Open the VBE by choosing the Visual Basic command from the Developer ribbon or by
pressing Alt+F11.
Before we can retrieve data using DAO, we must add a reference to the DAO library in our
project.
1. Select Tools ?¤ References in the VBE.
2. Find the Microsoft DAO 3.6 Object library in the list, and select it, as shown in
Figure 2-12.
3. Click OK.
4. Insert a new standard module by selecting Insert ?¤Module.
5. Create a new subroutine called GetDAOAccessJet().
6. Add the following variable declarations:
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim xlSheet As Worksheet
Dim i As Integer
Dim arr_sPath(1) As String
CHAPTER 2 n DATA IN, DATA OUT 55
Figure 2-12. Adding a reference to the DAO library
We??™re declaring the db and rs variables to hold our database and recordset objects. The
xlSheet variable will provide a simpler way to refer to the worksheet we??™ll be populating with
data. We??™re going to store the path to two versions of the Northwind database??”the new version
with the .accdb extension and the Access 2000 version with the .mdb extension??”to
compare how DAO works with these.
Add the following code to set up the file paths and Excel worksheet (be sure to change
the paths to the database files to reflect your location):
arr_sPath(0) = "C:\projects\Excel2007Book\Files\northwind 2007.accdb"
arr_sPath(1) = "C:\projects\Excel2007Book\Files\northwind.mdb"
Set xlSheet = Sheets("Sheet1")
xlSheet.Activate
Range("A1").Activate
Selection.
Pages:
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82