Nearly all business applications need to store large volumes of data, organized in a format retrieval. This is’accomplished with a .da.tabasemanagement system (DBMS), a mechanism for manipulating tabular data with high-level commands.
The.database management system hides low-level details, such as how data are stored in a database, and frees the programmer to concentrate on managing rather than on the specifics of manipulating files or maintaining links among them.
Visual Basic provides a wealth of teals for creating and accessing databases on both individual machines and networks. The two major tools are:
• The Data control
• The Data Access object
The Data control gives you access to databases without any programming. You can set a few properties of the control and use regular controls such as textboxes to display the values of the fields in the database. This is the no-code approach to database programming, which is implemented quite nicely in Visual Basic. But as you can guess, this approach can’t take you far, Sooner or later, you will have to write code. .
The Datil Access object is a-structure of objects-for accessing databases through your code, All the functionality of the Data control is also available to your code, through the’ Data Access object.(DAO)
Just-what is a database? In its basic sense, a database is simply a grouping of related information organized for easy processing and retrieval. Figure 17.1 shows how this works. The actual data in a database is stored in tables, which are similar to random access files. Data in a table is made up of columns and TOWS. The rows contain identically structured pieces of information, which are equivalent to the records of random access files. A record is a collection of values (called fields).
RecordSets
RecordSets are objects that represent collections of records from one or more tables. In database programming, RecordSets are the equivalent of variables in regular programming. You can’t access the tables of a database directly. The only way to view or manipulate records is via RecordSet objects. A RecordSet is constructed of columns and rows and is’similar to a table, but it can contain data from multiple tables. The contents of the ‘grid shown in Figure 17.2 come from a single table, and they form a RecordSet. Such records are the result of queries, such as all sustomers and the total of their invoices in a given month.
One way to think of RecordSets is as object variables. They store the results of queries or an entire table of the database, just as numeric variables store numbers. The contents of a RecordSet, however, have a more complicated structure (they are made of rows and columns), and each cell on this grid can be of a different type. To access the contents of the RecordSet, you use its properties and methods.
A RecordSet, therefore, is a view of some of the data in the database, “Selected from the database according to user-spedfied criteria. The three types of Record- Sets are:
DynaSets, which are updatable views of data
SnapShots, which are static (read-only) views of data
Tables, which are direct views of tables
DynaSeb and are usually created with SQL (Structured Query language) statement will look at SQL statements later in this chapter, but all you need to know about SQL statements for now is that they are commands that you Use to specify criteria for recalling data from a database. (You can also use SQL commands to update a database and even to create a new database, but I’m not going to discuss these commands in this book.) DynaSeb are updated ev~ time users change the database, and changes they make to the corresponding RecordSet are reflected in the underlying tables. SnapShob are static views of the same data. A SnapShot contains the records requested the moment the SnapShot was generated (changes made to the underlying tables are not reflected “inSnapShots), and you can’t update SnapShob.
The DynaSet is the most flexible and powerful type of RecordSet, although a few operations (such as searches) may be faster with the Table The Table type, however, requires a lot of overhead. The least RecordSet type, the . . SnapShot, is the most efficient in terms of oyerhead.If you don’t to update the database and simply want to view records, choose the SnapShot type.
There’s also a vanation of the SnapShot type, the forward-on!y SnapShot, which is even more limited than the SnapShot type, but faster. Forward-only SnapShot you move forward only. You can use them in programming situations in which you want to scan a number of records and process them sequentially (use their values in a calculation, copy selected records to another table, and so on). By not providing any methods to go back in the records, this RecordSet type requires the least overhead of all.
The Table RecordSet is a reference to a table in the database. The Table is faster than the other types of RecordSets, always in sync with the table’s data, and can be Used to update the database. But the Table type is limited to a single table. In addition, when accessing a table through a Table Recordset, you can take advantage of the Table’s indices to perform very fast searches.
