How to query a MS-Access Table from MS-Excel (2010) using VBA

Question

I am trying to write some vba code in Excel to query a table in Access. I have tried multiple code samples for this such as the added links and they all seem to fail at the "Open connection" part. I have tried using different references but I'm not sure which I should be using, what the differences are between some of the different versions (ie. Microsoft ActiveX Data Objects 2.0,2.1,...,6.0) or what the provider information should be. For the provider information I've usually been seeing something along the lines of

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="

But I'm not sure if that is what I need to use or why/what conditions anything in the provider string shown above should change. Can someone please educate me on how to properly do this sort of thing?

Note: If at all possible I would like a solution that would work without having to download any other application and would work for both 2007 and 2010 versions of Access and Excel since this will need to run on different computers with possibly different versions of office.

Links to similar questions: Excel VBA query to access is failing http://www.mrexcel.com/forum/showthread.php?t=527490

Code:

Sub asdf()

strFile = "C:\Users\bwall\Desktop\Excel Query Access Testing"

Dim cn As Object
Dim rs As Object
Dim strSql As String
Dim strConnection As String
Dim AppPath As String
Set cn = CreateObject("ADODB.Connection")
AppPath = Application.ActiveWorkbook.Path


strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & AppPath & "\Masterlist_Current_copy.accdb;"
Debug.Print strConnection
strSql = "SELECT [Neptune Number],[Description],[Manufacturer],[Manufacturer P/N] FROM [All Components];"
cn.Open strConnection
Set rs = cn.Execute(strSql)
MsgBox rs.Fields(0) & " rows in MyTable"
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing

End Sub  

strConnection value =

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\bwall\Desktop\Excel Query Access Testing\Masterlist_Current_copy.accdb;

Hoary

Microsoft.Jet.OLEDB.4.0 is not supported for the 64 bit OS. – Cobbie

Answer 1

The Provider piece must be Provider=Microsoft.ACE.OLEDB.12.0 if your target database is ACCDB format. Provider=Microsoft.Jet.OLEDB.4.0 only works for the older MDB format.

You shouldn't even need Access installed if you're running 32 bit Windows. Jet 4 is included as part of the operating system. If you're using 64 bit Windows, Jet 4 is not included, but you still wouldn't need Access itself installed. You can install the Microsoft Access Database Engine 2010 Redistributable. Make sure to download the matching version (AccessDatabaseEngine.exe for 32 bit Windows, or AccessDatabaseEngine_x64.exe for 64 bit).

You can avoid the issue about which ADO version reference by using late binding, which doesn't require any reference.

Dim conn As Object
Set conn = CreateObject("ADODB.Connection")

Then assign your ConnectionString property to the conn object. Here is a quick example which runs from a code module in Excel 2003 and displays a message box with the row count for MyTable. It uses late binding for the ADO connection and recordset objects, so doesn't require setting a reference.

Public Sub foo()
    Dim cn As Object
    Dim rs As Object
    Dim strSql As String
    Dim strConnection As String
    Set cn = CreateObject("ADODB.Connection")
    strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=C:\Access\webforums\whiteboard2003.mdb"
    strSql = "SELECT Count(*) FROM MyTable;"
    cn.Open strConnection
    Set rs = cn.Execute(strSql)
    MsgBox rs.fields(0) & " rows in MyTable"
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
End Sub

If this answer doesn't resolve the problem, edit your question to show us the full connection string you're trying to use and the exact error message you get in response for that connection string.

Huddleston

Doesn't work for me either. Error I'm getting is: "Run-time error'-2147467259 (80004005)': Automation error, Unspecified error – Hoary

Should there be a semi-colon after the filename in the connection string? That is: Data Source=C:\Access\webforums\whiteboard2003.mdb; – Speaks

Would you be willing to show us the code for a bare bones procedure which produces the error? If you're building your connection string from variables, include a Debug.Print statement for the final string and copy it from the Immediate Window so we can see the string, too. – Huddleston

Sorry, I was on vacation. I am adding my code now. The only major descrepenscy I see is that my database is a .accdb not a mdb file type. – Hoary

Provider=Microsoft.Jet.OLEDB.4.0 does not work for ACCDB. You need the ACE provider as I described in the first paragraph of my answer. – Huddleston

Ah, that worked like a charm, sorry I must have missed that part. Thank you for the help. – Hoary

My environment is Office 2016, it works with Provider=Microsoft.ACE.OLEDB.12.0 and an accdb. Life saver! – Thoreau

Answer 2

Sub Button1_Click()
Dim cn As Object
    Dim rs As Object
    Dim strSql As String
    Dim strConnection As String
    Set cn = CreateObject("ADODB.Connection")
    strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
        "Data Source=C:\Documents and Settings\XXXXXX\My Documents\my_access_table.accdb"
    strSql = "SELECT Count(*) FROM mytable;"
    cn.Open strConnection
    Set rs = cn.Execute(strSql)
    MsgBox rs.Fields(0) & " rows in MyTable"

    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing

End Sub

Mize

Answer 3

All you need is a ADODB.Connection

Dim cnn As ADODB.Connection ' Requieres reference to the
Dim rs As ADODB.Recordset   ' Microsoft ActiveX Data Objects Library

Set cnn = CreateObject("adodb.Connection")
cnn.Open "DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\Access\webforums\whiteboard2003.mdb;"

Set rs = cnn.Execute(SQLQuery) ' Retrieve the data

Cobbie

Answer 4

Option Explicit

Const ConnectionStrngAccessPW As String = _"Provider=Microsoft.ACE.OLEDB.12.0;
Data Source=C:\Users\BARON\Desktop\Test_DB-PW.accdb;
Jet OLEDB:Database Password=123pass;"

Const ConnectionStrngAccess As String = _"Provider=Microsoft.ACE.OLEDB.12.0;
Data Source=C:\Users\BARON\Desktop\Test_DB.accdb;
Persist Security Info=False;"

'C:\Users\BARON\Desktop\Test.accdb

Sub ModifyingExistingDataOnAccessDB()

    Dim TableConn As ADODB.Connection
    Dim TableData As ADODB.Recordset


    Set TableConn = New ADODB.Connection
    Set TableData = New ADODB.Recordset

    TableConn.ConnectionString = ConnectionStrngAccess

    TableConn.Open

On Error GoTo CloseConnection

    With TableData
        .ActiveConnection = TableConn
        '.Source = "SELECT Emp_Age FROM Roster WHERE Emp_Age > 40;"
        .Source = "Roster"
        .LockType = adLockOptimistic
        .CursorType = adOpenForwardOnly
        .Open
        On Error GoTo CloseRecordset

            Do Until .EOF
                If .Fields("Emp_Age").Value > 40 Then
                    .Fields("Emp_Age").Value = 40
                    .Update
                End If
                .MoveNext
            Loop
            .MoveFirst

        MsgBox "Update Complete"
    End With


CloseRecordset:
    TableData.CancelUpdate
    TableData.Close

CloseConnection:
    TableConn.Close

    Set TableConn = Nothing
    Set TableData = Nothing

End Sub

Sub AddingDataToAccessDB()

    Dim TableConn As ADODB.Connection
    Dim TableData As ADODB.Recordset
    Dim r As Range

    Set TableConn = New ADODB.Connection
    Set TableData = New ADODB.Recordset

    TableConn.ConnectionString = ConnectionStrngAccess

    TableConn.Open

On Error GoTo CloseConnection

    With TableData
        .ActiveConnection = TableConn
        .Source = "Roster"
        .LockType = adLockOptimistic
        .CursorType = adOpenForwardOnly
        .Open
        On Error GoTo CloseRecordset

        Sheet3.Activate
        For Each r In Range("B3", Range("B3").End(xlDown))

            MsgBox "Adding " & r.Offset(0, 1)
            .AddNew
            .Fields("Emp_ID").Value = r.Offset(0, 0).Value
            .Fields("Emp_Name").Value = r.Offset(0, 1).Value
            .Fields("Emp_DOB").Value = r.Offset(0, 2).Value
            .Fields("Emp_SOD").Value = r.Offset(0, 3).Value
            .Fields("Emp_EOD").Value = r.Offset(0, 4).Value
            .Fields("Emp_Age").Value = r.Offset(0, 5).Value
            .Fields("Emp_Gender").Value = r.Offset(0, 6).Value
            .Update

        Next r

        MsgBox "Update Complete"
    End With


CloseRecordset:
    TableData.Close

CloseConnection:
    TableConn.Close

    Set TableConn = Nothing
    Set TableData = Nothing

End Sub

Wryneck