Working with Dynasets

A dynaset, being a selected subset of a database, lets you work with a smaller set of data than the entire database table. The nice thing about dynasets is that as soon as you make a change to a dynaset, the linked table updates in the database.

One problem is that the creation of dynaset variables often requires a full working knowledge of SQL (pronounced sequel), a database-access language that works among many database products. This and subsequent sections will show you some recordset and dynaset object creation and manipulation by example, but much more can be involved in the coding of DAO. As stated earlier, DAO can be much tougher to code than the Data controls, but DAO provides speed and flexibility that you don't get with the Data controls.

One common dynaset variable you can create references an entire table from the database. The following code defines a database and sets a dynaset variable named dsTitles to the Titles table inside the Biblio.mdb database:

Dim dbBooks As Database

Dim dsTitles As Recordset   ' A dynaset is a special recordset

' Create reference to database

Set dbBooks = OpenDatabase("Biblio.mdb")

' Create reference to dynaset (the entire table)

Set dsTitles = dbBooks.OpenRecordset("Titles", dbOpenDynaset)

When you supply a table name for OpenRecordset()'s first argument, as done here, the entire table is referenced from the dynaset variable named dsTitles. Therefore, subsequent code can access information from the dynaset that corresponds to the entire table. vbOpenDynaset makes the recordset a dynaset, as opposed to a snapshot that would result if you used the vbOpenSnapshot option. You can also pass OpenRecordset() the name of an existing query. Many database systems, such as Microsoft Access, let you specify queries that retrieve records and fields based on a criterion. You can save those queries and reference them in the OpenRecordset() function. The existing named query sets the dynaset variable to that subset of data generated by the query.

A big query advantage is that a query can access data from multiple tables within the database. The recordset acts as though the data all came from a single location.

Also, if you know SQL, you can place SQL language directly in the OpenRecordset() function, like this:

Set dsTitles = dbBooks.OpenRecordset("Select * FROM " _

  & "Titles WHERE Year Published = '1998'"), dbOpenDynaset)

The ampersand (the concatenate operator) lets you break the OpenRecordset() more easily into two lines.

After the OpenRecordset() finishes its task, the dynaset associates with its variable. To assign the dynaset's current record field value to a control such as a Label control, use this code:

If IsNull(dsTitles!Title) Then    ' Don't assign Null

  lblBookTitle.Caption = ""

Else

  lblBookTitle.Caption = dsTitles!Title

End If

You can shorten the code by using the IIf() function, like this:

lblBookTitle.Caption = __

  IIf(IsNull(dsTitles!Title), "", dsTitles!Title)

Consider the following when studying these If tests:

• You separate the field name from the dynaset with an exclamation mark.
• You must know the table's field names. You won't be able to manipulate the database through DAO if you're unfamiliar with the database table structure.
• A field might hold a Null value, but you should assign an empty string instead of the Null value. You can't assign Null to some controls.