Writing Excel VBA to receive data from Access

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 Range10        DBFullName = "D:\Tool_Database\Tool_Database.mdb"20        On Error GoTo Whoa30        Application.ScreenUpdating = False40        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 names90        For intColIndex = 0 To rs.Fields.Count - 1100           TargetRange.Offset(1, intColIndex).Value = rs.Fields(intColIndex).Name110       Next          ' Write recordset120       TargetRange.Offset(1, 0).CopyFromRecordset rsLetsContinue:130       Application.ScreenUpdating = True140       On Error Resume Next150       rs.Close160       Set rs = Nothing170       cn.Close180       Set cn = Nothing190       On Error GoTo 0200       Exit SubWhoa:210       MsgBox "Error Description :" & Err.Description & vbCrLf & _             "Error at line     :" & Erl & vbCrLf & _             "Error Number      :" & Err.Number220       Resume LetsContinueEnd Sub

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.

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.