Share this Page URL

Chapter 5. Using Visual Basic to Automat... > Using an Object Model - Pg. 185

Using Visual Basic to Automate Your Database 185 Some of this code should be familiar if you've worked through the examples earlier in this chapter. The procedure declares variables for DAO Database and Recordset objects and two string variables to hold values from the Language and Headline fields in the MarketingMate- rials table. The variable db is set to the current database, and the variable rs is set to a re- cordset based on the MarketingMaterials table. The Recordset object's MoveFirst method goes to the first record in the recordset. The values of the Language and Headline fields, respectively, are then assigned to the variables strLanguage and strHeadline. The code in- cludes the Nz function, which returns a zero-length string if the record has no data in this field. The next two statements set the Value property for each of the text boxes on the form, using the values held in the variables. The procedure then includes code that closes the recordset and the database and sets the Database and Recordset objects equal to Nothing. You should include code such as this whenever you're working with recordsets in a database. Maintaining an object reference requires a lot of computer resources, so you want to release these re- sources as soon as you no longer need them. If you want, switch back to Access, and close the DAO Demonstration form. 5. Table 5-4 lists some of the other objects and collections in DAO that you'll work with. Use the Object Browser and the online help to get information about the properties and methods of the objects listed. Table 5-4. A Few Additional DAO Objects and Collections Collection/Object TableDefs collection, Table- Def object QueryDefs collection, Query- Def object Fields collection, Field object Description The TableDefs collection includes all the tables in a database, including linked tables. A Table- Def object refers to a particular item in the collection. We used the TableDef object in the Import procedure earlier in this chapter. QueryDefs includes all the queries in a database. A QueryDef object refers to a particular query. TableDef, QueryDef, and Recordset objects have a Fields collection. The Fields collection includes a Field object for each field in the table, query, or recordset. ADO Object Model ADO is designed to work with a number of different data sources, including Access, SQL Server, and others. If you need to work with a database other than one created in Access, you need to learn about ADO, but using ADO with Access is fine too, and some people find ADO easier to work with than DAO. Like DAO, ADO has a Recordset object that you use to get the records you want to work with. ADO also includes a Fields collection and a Field object. Creating a Connection ADO also uses a Connection object, which represents a connection to a database--either the da- tabase you're working with or a different one. We'll look at an example of opening a connection to a different database in Chapter 17. If you're using ADO to work with the current database, you can use code such as the following, which sets the ActiveConnection property of a Recordset object equal to the connection that's associated with the current database: Dim adoRst As ADODB.Recordset Set adoRst = New ADODB.Recordset adoRst.ActiveConnection = CurrentProject.Connection You could also use the Connection object in code such as the following: Dim adoCnn As ADODB.Connection Dim adoRst As ADODB.Recordset Set adoCnn = New ADODB.Connection Set adoRst = New ADODB.Recordset Set adoCnn = CurrentProject.Connection adoRst.ActiveConnection = adoCnn