Hi
I am trying to extrapolate data from an MS Access 2007/2010 Database.
I have the following code in VBA but the connection string is incorrect. I have added the relevant REFERENCES libraries
Code:
Private Sub btnGetMsAccessData_Click()
Dim sConn As String
Dim oConn As ADODB.Connection
Dim oRs As ADODB.Recordset
Dim sSQL As String
sConn = "Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=\\MyNetworkPath\BP-MasterDashboard Source\BP_Planning_by_PT_dept_be.accdb;Mode=Read"
Set oConn = New ADODB.Connection ' Open a connection.
oConn.Open
sSQL = "SELECT * FROM Tbl_Start_Leaver" ' Make a query over the connection.
Set oRs = New ADODB.Recordset
oRs.Open sSQL, , adOpenStatic, adLockBatchOptimistic, adCmdText
MsgBox oRs.RecordCount
' Close the connection.
oConn.Close
Set oConn = Nothing
End Sub
It fails saying Unknown Application error on the oConn.Open line.
I have tried to link a Worksbook to one of the tables and this works fine.
I then looked at the "Connection" and copied it into my code but still no joy.
Any ideas would be appreciated.
Thanks in advance.