Fields(i - 1).Name
Next i
xlSheet.Range(xlSheet.Cells(1, 1), _
xlSheet.Cells(1, rs.Fields.Count)).Font.Bold = True
Our first example used a zero-based counter to do this job:
For i = 0 To rs.Fields.Count - 1
xlSheet.Cells(1, i + 1).Value = rs.Fields(i).Name
Next i
CHAPTER 2 n DATA IN, DATA OUT 61
The only real difference in this code is that we??™ve assigned the rs.Fields.Count property
to a variable in the new version. This is a bit more efficient because it eliminates the need to
query the Recordset object for its Fields.Count with each pass through the loop. It does, however,
change the way we reference our index values. In the first example, our loop refers to
Fields.Count - 1; in the second, it simply refers to Fields.Count; and so on.
The remainder of the code is the same as the first example, with the addition of cleanup
code for our new Workspace and Connection objects. The entire new subroutine looks like
Listing 2-4.
Listing 2-4. Retrieving Access 2007 Code via ODBC
Sub GetDAOAccess2007ODBC()
Dim wrk As DAO.Workspace
Dim cnn As DAO.Connection
Dim rs As DAO.Recordset
Dim sConn As String
Dim xlSheet As Worksheet
Dim iFieldCount As Integer
Dim i As Integer
Dim arr_sPath(1) As String
'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.
Pages:
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88