The following section of code comes from an Access function.
In brief, it checks if a query is present (using another function) and - if so - exports it to a new worksheet in a previously created Excel file. This code works perfectly some of the time.
On other occasions, the text in red fails with the following message:
The message specifies the query name not the name of the Excel file and the debug message is saying it exists, just before the process fails because it supposedly doesn't??? I don't understand how it's even reaching the problematic line of code if the query doesn't exist in the first place?
The code runs perfectly some of the time. Why doesn't it always fail? I'm wondering if there could be some kind of timing problem? For example, the function QueryExists finds it but could something be affecting how long it takes for the database itself to 'know' it exists?
If it makes any difference, I'm running Access 2010 on Windows 7.
Hoping somebody with more comprehensive knowledge than me can offer some suggestions.
In brief, it checks if a query is present (using another function) and - if so - exports it to a new worksheet in a previously created Excel file. This code works perfectly some of the time.
Code:
If QueryExists(strSheetNameNew) Then
Debug.Print strSheetNameNew & " exists"
[COLOR="Red"]DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, strSheetNameNew, strNewBook, True[/COLOR]
DoCmd.DeleteObject acQuery, strSheetNameNew
RstExport.MoveNext
Else
Debug.Print strSheetNameNew & " does Not Exist"
End If
Code:
Public Function QueryExists(ByVal strQueryName As String) As Boolean
Dim qdf As DAO.QueryDef
QueryExists = False
For Each qdf In CurrentDb.QueryDefs
If qdf.Name = strQueryName Then
QueryExists = True
Exit For
End If
Next
End Function
On other occasions, the text in red fails with the following message:
Code:
Run-time error '3011':
The Microsoft Access database engine could not find the object {whichever query it is}. Make sure the object exists and that you spell its name and path name correctly. If {whichever query it is} is not a local object, check your network connection or contact the server administrator'
The message specifies the query name not the name of the Excel file and the debug message is saying it exists, just before the process fails because it supposedly doesn't??? I don't understand how it's even reaching the problematic line of code if the query doesn't exist in the first place?
The code runs perfectly some of the time. Why doesn't it always fail? I'm wondering if there could be some kind of timing problem? For example, the function QueryExists finds it but could something be affecting how long it takes for the database itself to 'know' it exists?
If it makes any difference, I'm running Access 2010 on Windows 7.
Hoping somebody with more comprehensive knowledge than me can offer some suggestions.