To establish a reference to the DAO object libraryYou can use DAO properties, objects, and methods the same way you reference and use Microsoft Excel properties, objects, and methods. After you establish a reference to the DAO object library, you're free to open or create databases, run queries to create result sets, and bring the result sets back to Microsoft Excel.
Before you can use DAO, you must establish a reference to the DAO object library using the Microsoft Excel References dialog box.
To establish a reference to the DAO object library
1. Switch to a Visual Basic module.
2. On the Tools menu, click References.
3. In the Available References box, click Microsoft DAO 3.0 Object Library.
If you don't see Microsoft DAO 3.0 Object Library in the Available References box, rerun Setup to install it.
To install the Microsoft DAO 3.0 Object Library
1. Run Setup in maintenance mode, and click Custom Setup.
2. Click Add/Remove.
3. Clear the Data Access check box, and then click Change Option.
4. Under Options, select the check boxes for the data access drivers you want to install, and then click OK.
5. Click Continue, and proceed with the installation.
To open an existing database using DAO, create a database variable. Then, in an open workspace, use the OpenDatabase method and specify the path to the existing database. The following example opens the Microsoft Access sample database Nwind.mdb.
Dim db As Database
Set db = OpenDatabase("e:\access\sampapps\nwind.mdb")
db.Close
End Sub
There are two ways to query a database using DAO: you can run a query that already exists, or you can create a new query. If you run a query that already exists, you simply refer to the name of the existing query in the OpenRecordset method. If you create a new query, you can choose to write it back to the database when you're finished, or you can choose to load it into memory.
To run any type of query, define a database variable and then set it to the database you want to work in.
Dim db As Database
Set db = OpenDatabase("e:\access\sampapps\nwind.mdb")
To open an existing query, use the OpenRecordset method to open the specified query and return the Recordset object. The first argument contains the name of the stored query you want to open, and the second argument describes the type of Recordset object that's returned.
Dim myset as Recordset
Set myset = db.OpenRecordset("Country", dbOpenDynaset)
If you omit the Recordset object type, DAO will try to return the fastest Recordset object type that allows you to modify the data. If you need only to read the data or append new data, see "OpenRecordset method" in Help for information about Option arguments that may make your application run faster.
To store a query in the database, use the CreateQueryDef method. The result of a CreateQueryDef method is compiled SQL code written back to the database as a query definition. You can store the SQL code in the database this way and then run it later using the OpenRecordset method to return a result set. The string you specify in the first argument is the query name that will be stored in the database, and the second argument is the SQL command string.
Dim qd As QueryDef
Set qd = db.CreateQueryDef("Country", "select distinctrow orders. _
[ship country],sum(orders.[freight])as SumofFreight from _
orders group by orders.[ship country] order by orders.[ship _
country];")
Note
If you're not familiar with writing SQL code, you can examine the examples that go with a specific object, property, or method in Help, or you can construct a query in Access and view the resulting SQL code. If necessary, you can copy and paste most SQL code from the SQL view in Microsoft Access into a CreateQueryDef method.
To run SQL code and return a Recordset object that exists entirely in memory, use the OpenRecordset method, and specify a SQL string (instead of a stored query definition) as the first argument.
Dim myset As Recordset Dim SQLString As String
After you set the database variable to the database you want to work in, load the SQL string into a string variable. Then use the OpenRecordset method, using as arguments the name of the SQL string and the Recordset object type.
SQLString = "select distinctrow orders.[ship
country],sum(orders.[freight])as SumofFreight from orders _
group by orders.[ship country] order by orders.[ship country];"
Set myset = db.OpenRecordset(SQLString, dbOpenSnapshot)
For information about optimizing your SQL statement, see "SQL" in Help. For information about optimizing your application, see "Optimizing DAO Code" later in this chapter. If you want to run multiple queries that differ only in the records selected, you may want to use a parameter query and provide new parameters each time you open the query. For more information about parameter queries, see "Parameter object" in Help.
You can import the contents of a result set into a Microsoft Excel worksheet either by placing the records into rows one by one or by importing the entire set at once using the CopyFromRecordset method. After you copy the result set to the worksheet, the record values become part of the worksheet.
If you copy the records one by one, you'll need to keep track of index variables because these dictate where the record will be placed on the worksheet. Although keeping track of index variables can be confusing if you have many fields, copying records this way gives you complete control over where your information is placed.
You can copy an entire result set to a range on a worksheet at once by using the CopyFromRecordset method. This method begins to copy at the current row of the result set; when the transfer is completed, the Recordset object pointer is positioned just past the last row, or at EOF. Other than moving the Recordset object pointer, this method doesn't alter the Recordset object.
Dim db As Database
Dim rs As Recordset
Set db = OpenDatabase("e:\access\sampapps\nwind.mdb")
Set rs = db.OpenRecordset("Orders")
Worksheets("Sheet1").Range("A2").CopyFromRecordset rs
rs.Close
db.Close
Note
In some circumstances, if you're copying either a result set containing OLE objects or pictures (such as the Categories table in Nwind.mdb), the copy operation will fail, and an exception error will be returned.
For more information about the CopyFromRecordset method, see "Optimizing DAO Code" later in this chapter.
There are two ways to fill a list box with a result set. You can specify a range of cells on a worksheet that contains a list of items you want to appear in the list box, or you can use the Additem method to directly fill the list box.
To fill a list box with a range of cells, use the ListFillRange property. The following example assumes that there's at least one dialog sheet in the active workbook, that there's at least one ListBox object on the first dialog sheet, and that there are values in cells A1:A10 on the worksheet named "Sheet1." The following example fills the list box with the values in cells A1:A10.
ActiveWorkbook.DialogSheets(1).ListBoxes(1).ListFillRange _
= "Sheet1!A1:A10"
Note
A cell can contain up to 255 characters; a list box can contain more than 255 characters. Therefore, if you use the ListFillRange property to fill a list box, be careful that the text you insert from a cell into a list box isn't truncated.
To fill a list box with a single item or to add selected items to a list box, use the AddItem method. The following example adds the second field value in the result set to the contents of the list box. The example assumes that there's at least one dialog sheet in the active workbook and that there's at least one ListBox object on the first dialog sheet.
Dim db As Database
Dim rs As Recordset
Dim x as Integer, I as Integer
Set db = OpenDatabase("d:\access\sampapps\nwind.mdb")
Set rs = db.OpenRecordset("shippers")
rs.MoveLast
x = rs.RecordCount
rs.MoveFirst
For i = 0 To x - 1
DialogSheets(1).ListBoxes(1).AddItem Text:=rs.Fields(1).Value
rs.MoveNext
Next I
rs.Close
db.Close
End Sub
Note
Using the AddItem method will delete all list box entries made with the ListFillRange method.