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    amp         Data Source    amp  AppPath  amp    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   amp    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

User · Answer

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  gt  40            Source    Roster           LockType   adLockOptimistic          CursorType   adOpenForwardOnly          Open         On Error GoTo CloseRecordset              Do Until  EOF                 If  Fields  Emp Age   Value  gt  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    amp  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

User · Answer

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    amp             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   amp    rows in MyTable       rs Close     Set rs   Nothing     cn Close     Set cn   Nothing  End Sub

User · Answer

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    amp             Data Source C  Access webforums whiteboard2003 mdb      strSql    SELECT Count    FROM MyTable       cn Open strConnection     Set rs   cn Execute strSql      MsgBox rs fields 0   amp    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

User · Answer

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

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

Examples related to excel

Examples related to ms-access

Examples related to vba