I have a group of queries in a custom category in Access, and a bit of VBA code that gets the query names and runs them, and then copies the results for each query to a separate tab on an Excel spreadsheet. Here's the code:
Private Sub Command0_Click()
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
Dim timestamp As String
Dim path As String
'Create the Excel spreadsheet
timestamp = Format(Now(), "yyyyMMddhhmmss")
path = "C:\Users\username\Desktop\ValidationResults" & timestamp & ".xlsx"
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Add
Set oSheet = oBook.Worksheets("Sheet1")
oBook.SaveAs path
oExcel.Quit
'This gets a list of query names and puts it in a recordset
Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset("01 - GetValidationQueries")
'this runs each query and copies the results to a unique worksheet
Do While Not rs.EOF
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, rs("Name"), path, True, Left(rs("Name"), 2)
rs.MoveNext
Loop
End Sub
The "01 - GetValidationQueries" query looks in the MSysNavPaneGroupToObjects and MSysNavPaneObjectIDs tables to get the names of the queries in a custom category.
The problem is that when I click the form button to run this code, I get the following error on the DoCmd line:
Run-time error '3275':
Unexpected error from external database driver (1309).
If I step thru the code line by line (hitting F8 each time) it runs fine, so I'm guessing the TransferSpreadsheet process is too slow. Other than putting a pause after that line, is there a better way of making this work?
EDIT: using the following code (for now, at least)
Public Function fnWait(intNrOfSeconds As Integer)
Dim varStart As Variant
varStart = Timer
Do While Timer < varStart + intNrOfSeconds
Loop
End Function
Then before and after the DoCmd line, I have...
fnWait (2)
...to create a 2 second delay. It seems to be working so far.
