A look at the Northwind 2007 Customers table in Design view
(Figure 2-19) will show us the data types in use here.
Figure 2-19. Northwind Customers table Design view
Figure 2-19 shows us that most of these fields use the Text data type, but we see a few
that do not. You??™ll recall me mentioning that the Excel help file noted that OLE fields would
cause the CopyFromRecordset method to fail, yet there are no OLE fields present here. The
Memo, Hyperlink, and Attachment data types will all cause the CopyFromRecordset method to
fail. To check, you could change your SQL statement in the OpenRecordset call to any of these:
SELECT Address FROM Customers
or
SELECT [Web Page] FROM Customers
or
SELECT Attachments FROM Customers
A recordset that includes any of these filters will cause our subroutine to fail. So let??™s then
modify our SQL statement to include only those fields that are not of these data types.
Set rs = cnn.OpenRecordset("SELECT ID, Company, [Last Name]," ??
& " [First Name], [E-mail address], [Job title]," ??
& " [Business Phone], [Mobile Phone], [Fax Number]," ??
& " city, [state/province], [zip/postal code]," ??
& " [country/region] " ??
& "FROM Customers Order By Company", dbOpenDynamic)
Run the code, and your result should look like that in Figure 2-20.
CHAPTER 2 n DATA IN, DATA OUT 64
Figure 2-20. DAO ODBC result from Northwind 2007 Customers table
Can you access data in other versions of Access using DAO ODBC? Yes, you can. With a
simple edit to the GetDAOAccess2007ODBC subprocedure, you could use an ODBC call.
Pages:
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90