L Fields Field

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

This looks very similar to our last example, but let's look at the differences. 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.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:

xlSheet.Cells(1, i + 1).Value = rs.Fields(i).Name Next i

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 - l; 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(l) 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(l) = "C:\projects\Excel2007Book\Files\northwind.mdb"

Set xlSheet = Sheets("Sheetl")

xlSheet.Activate

Range("Al").Activate

Selection.CurrentRegion.Select

Selection.ClearContents

Range( "Al").Select sConn = "ODBC;Driver={Microsoft Access Driver (*.mdb, *.accdb)};" & "DB0=" & arr_sPath(0)

Set wrk = CreateWorkspace("", "", "", dbUseODBC) Set cnn = wrk.OpenConnection("", , , sConn)

Set rs = cnn.OpenRecordset("SELECT * FROM Customers", dbOpenDynamic)

iFieldCount = rs.Fields.Count For i = l To iFieldCount xlSheet.Cells(l, i).Value = rs.Fields(i - l).Name Next i xlSheet.Range(xlSheet.Cells(l, 1), xlSheet.Cells(l, rs.Fields.Count)).Font.Bold = True xlSheet.Cells(2, l).CopyFromRecordset rs xlSheet.Select Range( "Al").Select Selection.CurrentRegion.Select Selection.Columns.AutoFit Range( "Al").Select

'close workspace wrk.Close

'release objects Set xlSheet = Nothing Set rs = Nothing Set wrk = Nothing Set cnn = Nothing

End Sub

Let's run this code from Sheet1 and see what it does.

1. Choose the Macros command from the Developer ribbon.

2. Select the DAOAccess2007ODBC macro from the list, and click Run. This should generate an error, as shown in Figure 2-17.

Figure 2-17. DAO ODBC runtime error 3. Click the Debug button, and let's see where the code is stopping (see Figure 2-18).

Figure 2-18. CopyFromRecordset stops the code.

Figure 2-18. CopyFromRecordset stops the code.

Now we run into the error that I mentioned in the previous example. Excel's CopyFromRecordset method doesn't like the data type of a field or fields that we're returning in the recordset being passed to it. 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.

1 ||D jcompany

2 1 Company A

3 27 Company AA

4 2 Company B

5 2S Company BB

6 3 Company C

7 29 Company CC S 4 Company D

9 I 5 Company E

10 5 Company F

11 7 Company G

12 8 Company H

13 9 Company I

14 10 CompanyJ

15 11 Company K

16 12 Company L

17 13 Company M IS 14 Company N

19 15 Company O

20 IS Company P

21 17 Company Q

22 IS Company R

23 19 Companys

24 20 Company T

25 21 Company U

Last Name

Bedecs Toh

Grata cos Solsona

Raghav

Axen

O'Donnell

Perez-Olaeta

Andersen

Mortensen

Wacker

Krschne

Edwards

Ludick

Grilo

Kupkova

Goldschmidt

Bagel

Autier Miconi

Eggerer

Tham

First Name

Anna

Karen

Antonio

Amritansh

Thomas

Soo Jung

Christina

Martin

Francisco

Ming-Yang

Elizabeth

Sven

Roland

Peler

John

Carlos

Helena

Daniel

Jean Philippe

Catherine

Alexander

George

Bernard

E-mail address Job title Business Phone

Owner (123)555-0100

Purchasing Manager (123)555-0100

Owner (123)555-0100

Purchasing Manager (123)555-0100

PurchasingRepresentative (123)555-0100

Purchasing Manager (123)555-0100

Purchasing Manager (123)555-0100

Owner (123)555-0100

Purchasing Manager (123)555-0100

Owner (123)555-0100

PurchasingRepresentative (123)555-0100

Purchasing Manager (123)555-0100

Purchasing Manager (123)555-0100

Purchasing Manager (123)555-0100

Purchasing Manager (123)555-0100

PurchasingRepresentative (123)555-0100

PurchasingRepresentative (123)555-0100

Purchasing Manager (123)555-0100

PurchasingRepresentative (123)555-0100

Owner (123)555-0100

PurchasingRepresentative (123)555-0100

AccountingAssistant (123)555-0100

Purchasing Manager (123)555-0100

AccountingManager (123)555-0100

Figure 2-2D. 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.

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)};"

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.

0 0

Post a comment