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;
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.
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
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
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
Source: Stackoverflow.com