Using the CopyFromRecordset method is much more efficient and more performant than
looping through a recordset to retrieve the entire contents.
CHAPTER 2 n DATA IN, DATA OUT 59
nNote When using the Range.CopyFromRecordset method, copying begins at the current row of the
recordset object. After copying is completed, the EOF property of the recordset object is True. If you need to
reuse your recordset, you must call its MoveFirst method (if the type of recordset you??™ve created is not
using a forward-only cursor).
DAO Example 2: Importing Access Data Using ODBC
In the previous example, you saw that Jet 4 does not support the *.accdb format, and you
learned that it is no longer a part of the MDAC. How can you use DAO to access data in the
new Access database format? The answer is ODBC (Open Database Connectivity).
The DAO ODBC object model is shown in Figure 2-16.
Figure 2-16. DAO ODBC object model
The method for importing data using DAO ODBC is somewhat different than using Jet.
In Jet, we could use a database object to refer to our Access database. Using ODBC, we have
to create Workspace and Connection objects that we??™ll use to connect to the database and
retrieve a recordset of data.
In the VBE, on the same code module, add a subroutine called GetDAOAccess2007ODBC().
Add the following variable declarations:
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
CHAPTER 2 n DATA IN, DATA OUT 60
This looks very similar to our last example, but let??™s look at the differences.
Pages:
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86