DAO Example Importing Access Data Using

Open a new workbook and name it DataAccessSample03.xlsm. Be sure to use the .xlsm extension so your workbook is macro-enabled.

Open the VBE by choosing the Visual Basic command from the Developer ribbon or by pressing Alt+F11.

Before we can retrieve data using DAO, we must add a reference to the DAO library in our project.

1. Select Tools > References in the VBE.

2. Find the Microsoft DAO 3.6 Object library in the list, and select it, as shown in Figure 2-12.

4. Insert a new standard module by selecting Insert > Module.

5. Create a new subroutine called GetDA0AccessJet().

6. Add the following variable declarations:

Dim db As DAO.Database Dim rs As DA0.Recordset Dim xlSheet As Worksheet Dim i As Integer Dim arr_sPath(l) As String

References - VBAProject

Available References:

□ Microsoft Agent Server Extensions 2.0 ^

□ Microsoft AutoDiscovery Type Library

□ Microsoft Browser Helpers

□ Microsoft CDO for Windows 2000 Library

□ Microsoft Component Services Typelib ✓ Hicrosoft DAO 3.6 Ob]ect Library

□ Miaosoft Data Access Components Installed Version C

□ Microsoft Data Formatting Object Library 6.0 (SP4)

□ Miaosoft Data Source Interfaces

□ Microsoft DDS 80

□ Microsoft DDS Layout Manager

□ Miaosoft DDS Layout Manager 80

l~l Mirrnsnft Dpvplnnmpnt Fnvirnnmpnt R.O <

Priority

Help

Microsoft DAO 3.6 Object Library

Location: C: program Files''Common Files Microsoft Shared £>AO\dao36C Language: Standard

Figure 2-12. Adding a reference to the DAO library

We're declaring the db and rs variables to hold our database and recordset objects. The xlSheet variable will provide a simpler way to refer to the worksheet we'll be populating with data. We're going to store the path to two versions of the Northwind database—the new version with the .accdb extension and the Access 2000 version with the .mdb extension—to compare how DAO works with these.

Add the following code to set up the file paths and Excel worksheet (be sure to change the paths to the database files to reflect your location):

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

We're assigning the Sheet1 object from our workbook to the variable xlSheet to provide easier access to that sheet. This eliminates the need to type Sheets("Sheet1") whenever we need to reference the worksheet we're manipulating.

Next we'll instantiate our database and recordset objects:

Set db = Workspaces(0).OpenDatabase(arr_sPath(0), ReadOnly:=True) Set rs = db.OpenRecordset("Employees")

This code creates the default Jet workspace and fills a recordset with the information in the Employees table in the Northwind 2007 database.

Now we'll fill the first row in the worksheet with the field names from the recordset and add bold formatting to the column headings:

xlSheet.Cells(l, i + l).Value = rs.Fields(i).Name Next i xlSheet.Range(xlSheet.Cells(l, l), xlSheet.Cells(l, rs.Fields.Count)) ^ .Font.Bold = True

Rather than create a loop to walk through the recordset and populate the sheet row by row and column by column, we'll use Excel's CopyFromRecordset method to fill the sheet with data:

xlSheet.Range("A2").CopyFromRecordset rs

The last thing we'll do before inserting our cleanup code is adjust the column widths to show the full text values (using the AutoFit method):

xlSheet.Select Range( "Al").Select Selection.CurrentRegion.Select Selection.Columns.AutoFit Range( "Al").Select

The first call to Range("Al").Select puts the cursor within the region we want to work with (in case there's more than one area with data on your worksheet). The next line, Selection.CurrentRegion.Select, selects any contiguous area of cells based on the current cursor location. Next comes our AutoFit command, followed by the selection of a single cell (to remove the selection from the entire range).

The entire function should now look like Listing 2-3.

Listing 2-3. GetDAOAccessJet Method

Sub GetDA0AccessJet() Dim db As DAO.Database Dim rs As DAO.Recordset Dim xlSheet As Worksheet 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

Set db = Workspaces(0).OpenDatabase(arr_sPath(0), ReadOnly:=True) Set rs = db.OpenRecordset("Employees")

xlSheet.Cells(1, i + 1).Value = rs.Fields(i).Name Next i xlSheet.Range(xlSheet.Cells(1, 1), xlSheet.Cells(1, rs.Fields.Count)) ^ .Font.Bold = True xlSheet.Range("A2").CopyFromRecordset rs xlSheet.Select Range( "A1").Select Selection.CurrentRegion.Select Selection.Columns.AutoFit Range( "A1").Select rs.Close db.Close

Set xlSheet = Nothing Set rs = Nothing Set db = Nothing End Sub

Let's run our code and see the result on Sheetl.

1. On the Developer ribbon, choose the Macros command.

2. Select the GetDAOAccessJet macro from the list, and click the Run button. DAO generates an error, as shown in Figure 2-13.

Microsoft Visual Basic

Run-time error '3343: Unrecognized database format

'C: projects ^xcel ZOO TBook^iles^orthwind 2007.accdb'

End Debug Help

Figure 2-13. Unrecognized database format error

3. Click the Debug button, and notice that our attempt to instantiate our DAO.Database object is failing (see Figure 2-14).

Set dfc = Workspaces(Û).OpenDatafcase(arr_sFath(0), Readonly:=True) Set rs = dti. OpenRecordset ( "Employees")

Figure 2-14. OpenDatabase method fires error

DAO Jet, it seems, does not support the new Access database format. Does this mean we cannot use DAO with *.accdb files? No, it does not. In a short while, we'll take a look at how we can access data from Access 2007 using DAO with ODBC. For now, let's continue with Jet.

To make this code work, all we have to do is change the array index in our arr_sPath variable from 0 to 1.

Set db = Workspaces(0).OpenDatabase(arr_sPath(1), ReadOnly:=True)

Rerun the code, and your worksheet should look like Figure 2-15.

Example Access Worksheet
Figure 2-15. The Employees table from the Access 2000 version of the Northwind database

Note According to the help file, the Range.CopyFromRecordset method will fail if the DAO (or ADO) recordset contains an OLE object. This seems to be true only sometimes. In the preceding example, we filled a DAO recordset object with the entire contents of the Employees table from the Access 2000 version of the Northwind database. This table includes a field named Photo that does contain an OLE object and is included in the data returned to us.

Using the CopyFromRecordset method is much more efficient and more performant than looping through a recordset to retrieve the entire contents.

Note 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).

0 0

Responses

  • lobelia
    How to enter new subroutine called GetDAOAccessJet()?
    7 years ago

Post a comment