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

by

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.

More Related Contents:

  1. Non-web SQL Injection
  2. Trying to use variables as values vba to sql
  3. How to insert an embedded picture?
  4. Error: “Could Not Find Installable ISAM”
  5. Rounding in MS Access
  6. Get length of array?
  7. Comparison of Dictionary, Collections and Arrays
  8. What is the different between IsNull, IsEmpty, =Empty, and an empty string ie “” and why might I use variants
  9. VBA comma separated cells, needt last entry only
  10. Wait for shell command to complete [duplicate]
  11. How to return the number of dimensions of a (Variant) variable passed to it in VBA [duplicate]
  12. How to add a named sheet at the end of all Excel sheets?
  13. VBA general way for pulling data out of SAP [closed]
  14. Delete Row based on Search Key VBA
  15. How to add a button programmatically in VBA next to some sheet cell data?
  16. How can I kill task manager processes through VBA code?
  17. How to add parameters to an external data query in Excel which can’t be displayed graphically?
  18. Pivotfields multiple filter
  19. How to send mail when the .Send does not work?
  20. How do I click a link on a web page using Excel VBA?
  21. Ping IP Address with VBA code and return results in Excel
  22. VBA copy cells value and format
  23. Paste Excel Chart into Powerpoint using VBA
  24. running excel macro from another workbook
  25. Multiply Entire Range By Value?
  26. EXCEL VBA UserInterfaceOnly:= True not working
  27. VBA Excel – IFERROR & VLOOKUP error
  28. Finding a workbook in one of multiple Excel instances
  29. SAP Extract into EXCEL via VBA out of memory
  30. reading entire text file using vba

Leave a Comment