Change the connection string to reference the Access 2000 version file path by changing
the 0 to 1 in the arr_sPath array:
sConn = "ODBC;Driver={Microsoft Access Driver (*.mdb, *.accdb)};" ??
& "DBQ=" & arr_sPath(1)
Then use the original SQL statement in the call to OpenRecordset:
Set rs = cnn.OpenRecordset("SELECT * FROM Customers", dbOpenDynamic)
The Access 2000 version of the Northwind Customers table does not contain any of these
issue-bearing data types, so we are able to query using Select * syntax.
DAO Example 3: Importing SQL Data Using ODBC
The final example of using DAO to bring data into your Excel project will focus on getting data
from an SQL server (or other ODBC-compliant database). The process is identical to what we
just did in our previous example, with the exception of a new connection string:
sConn = "ODBC;DATABASE=msdb;DSN=mySQL"
CHAPTER 2 n DATA IN, DATA OUT 65
We??™re still using the ODBC reference in the string, but now we??™re passing in the database
name and the DSN name. Here??™s the complete code. (You must reference a valid database and
DSN for this to provide you with output.)
Sub GetDAOSQLODBC()
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
Set xlSheet = Sheets("Sheet1")
xlSheet.Activate
Range("A1").Activate
Selection.CurrentRegion.Select
Selection.ClearContents
Range("A1").Select
sConn = "ODBC;DATABASE=msdb;DSN=mySQL"
Set wrk = CreateWorkspace("", "", "", dbUseODBC)
Set cnn = wrk.
Pages:
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91