Writing Excel VBA to receive data from Access

Question

I am writing an excel application that draws from an Access database for work. When the user opens the Excel tool, a data table needs to populate one of the worksheets from the Access database that I created. I have been writing the VBA code in excel and I am receiving Run-Time Error: "429" ActiveX Component Can't Create Object.

The other questions are all written from Access but I believe I need this code written from the Excel workbook file. The code I have written is in the Workbook_Open() function so that the data is collected right as the user opens the file. Thanks very much for the help. BTW, I am using Access 2007 and Excel 2010.

Private Sub Workbook_Open()
    'Will fill the first listbox with data from the Access database
    Dim DBFullName As String
    Dim TableName As String
    Dim FieldName As String
    Dim TargetRande As String

    DBFullName = "D:\Tool_Database\Tool_Database.mdb"

    Dim db As DAO.Database, rs As Recordset
    Dim intColIndex As Integer

    Set TargetRange = Range("A1")
    Set db = OpenDatabase(DBFullName)
    Set rs = db.OpenRecordset("SELECT * FROM ToolNames WHERE Item = 'Tool'", dbReadOnly)

    ' Write the field names
    For intColIndex = 0 To rs.Fields.Count - 1
        TargetRange.Offset(1, intColIndex).Value = rs.Fields(intColIndex).Name
    Next

    ' Write recordset
    TargetRange.Offset(1, 0).CopyFromRecordset rs

    Set rs = Nothing
    db.Close
    Set db = Nothing
End Sub

Answer 1

Tyler, Could you please test this code for me? If you get any error you will get a Message Box. Simply post a snapshot of the Message Box.

'~~> Remove all references as the below code uses Late Binding with ADO.

Private Sub Workbook_Open()
          Dim cn As Object, rs As Object
          Dim intColIndex As Integer
          Dim DBFullName As String
          Dim TargetRange As Range

10        DBFullName = "D:\Tool_Database\Tool_Database.mdb"

20        On Error GoTo Whoa

30        Application.ScreenUpdating = False

40        Set TargetRange = Sheets("Sheet1").Range("A1")

50        Set cn = CreateObject("ADODB.Connection")
60        cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & DBFullName & ";"

70        Set rs = CreateObject("ADODB.Recordset")
80        rs.Open "SELECT * FROM ToolNames WHERE Item = 'Tool'", cn, , , adCmdText

          ' Write the field names
90        For intColIndex = 0 To rs.Fields.Count - 1
100           TargetRange.Offset(1, intColIndex).Value = rs.Fields(intColIndex).Name
110       Next

          ' Write recordset
120       TargetRange.Offset(1, 0).CopyFromRecordset rs

LetsContinue:
130       Application.ScreenUpdating = True
140       On Error Resume Next
150       rs.Close
160       Set rs = Nothing
170       cn.Close
180       Set cn = Nothing
190       On Error GoTo 0
200       Exit Sub
Whoa:
210       MsgBox "Error Description :" & Err.Description & vbCrLf & _
             "Error at line     :" & Erl & vbCrLf & _
             "Error Number      :" & Err.Number
220       Resume LetsContinue
End Sub

Answer 2

Both DAO and ADO include recordset object types. However they are not compatible. Your declaration for the rs object variable is ambiguous.

Dim db As DAO.Database, rs As Recordset

A potential problem is that if your project includes a reference to ADO, and the precedence of that reference is above your DAO reference, rs will wind up as an ADO recordset rather than a DAO recordset.

I'm not certain this is the cause of the error you're seeing. However setting rs to db.OpenRecordset(something) should fail if rs is an ADO recordset because OpenRecordset returns a DAO recordset.

I think you should change the declaration to this:

Dim db As DAO.Database, rs As DAO.Recordset

Even if that change doesn't resolve your problem, I encourage you to always qualify the type when declaring recordset object variables ... to avoid ambiguity.

And if this isn't the fix, please tell us which line of your code triggers the current error you're seeing.

Here is another red flag:

Dim TargetRande As String

Later you have:

Set TargetRange = Range("A1")

Add Option Explict to the Declarations section of your module. Then choose Debug->Compile from the VB editor's main menu. That effort will highlight misspelled variable names, and also alert you to syntax errors.

Answer 3

It works fine on my machine (except the field names get overwritten by the first row of data - for the field names you probably mean TargetRange.Offset(0, intColIndex)).

Do you have a Tools -> References... to the Microsoft DAO 3.6 Object library?

Are you perhaps using the 64-bit version of Excel 2010 (check under File->Help in the 'About Microsoft Excel' section)? If so the old version DAO libraries won't work, and you'll need to install the 64-bit ACE DAO library, which is available for 64-bit.