OK. I have some test code that can collect Access query data and copy the data into an Excel spreadsheet.
I have one problem though, the Excel VBA code will not work if the Access database is open.
I get the following error message:
Run-Time Error '-2147467259 (80004005)'
Could not use "; file already in use."
What do I need to change in my code to allow me to collect my Access query data even if the database is open?
Code:
Sub GetAccessData_Test()
' Sample demonstrating how to return a recordset from an Access db
' late bound so does not require a reference to the Microsoft ActiveX Data Objects Library.
Dim wks As Worksheet
Dim cnn As Object, strQuery As String, rst As Object
Dim strPathToDB As String, strFormula As String, i As Long
' output to specific worksheet
Set wks = Sheets("Access2Excel_Test")
wks.Range("A5:M60000").ClearContents
' Path to database
strPathToDB = "c:\Data\Access2Excel_Test.mdb"
Set cnn = CreateObject("ADODB.Connection")
' open connection to database
With cnn
.ConnectionTimeout = 500
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=" & strPathToDB & ";"
.Open
.CommandTimeout = 500
End With
' SQL query string - change to suit
strQuery = "qry_Access2Excel_TestData"
' create new recordset
Set rst = CreateObject("ADODB.Recordset")
' open recordset using query string and connection
With rst
.Open strQuery, cnn
' check for records returned
If Not (.EOF And .BOF) Then
' Copy data starting from A5
wks.Cells(5, 1).CopyFromRecordset rst
End If
.Close
End With
' clean up
Set rst = Nothing
cnn.Close
Set cnn = Nothing
End Sub