We??™ve added
variables to hold our Workspace and Connection objects, as previously noted. We??™ve also added
the sConn variable to hold our connection string. This is where we??™ll tell our Connection object
where to find the data we require. The last difference is that we??™ve added a variable, iFieldCount,
to hold the number of fields in our Recordset object.
Copy and paste the path string and worksheet setup code from the previous example:
'store path to Access 2007 and 2000 versions of Northwind db
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.CurrentRegion.Select
Selection.ClearContents
Range("A1").Select
Set the connection string:
sConn = "ODBC;Driver={Microsoft Access Driver (*.mdb, *.accdb)};" ??
& "DBQ=" & arr_sPath(0)
Instantiate the Workspace and Connection objects:
Set wrk = CreateWorkspace("", "", "", dbUseODBC)
Set cnn = wrk.OpenConnection("", , , sConn)
We use the Workspace object??™s OpenConnection method to create the Connection object.
Next we??™ll use the Connection object??™s OpenRecordset method to fill our recordset with
data from the Employees table:
Set rs = cnn.OpenRecordset("SELECT * FROM Customers", dbOpenDynamic)
Insert our column headings using the iFieldCount variable:
iFieldCount = rs.Fields.Count
For i = 1 To iFieldCount
xlSheet.Cells(1, i).Value = rs.
Pages:
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87