Access acting as if query is not present, just after telling me it is (1 Viewer)

Alc

Registered User.
Local time
Today, 17:00
Joined
Mar 23, 2007
Messages
2,407
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.

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.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 21:00
Joined
Feb 19, 2013
Messages
16,553
probably due to trying to delete it before transferspreadsheet has finished populating the workbook - i.e a small query not a problem, but a problem for larger queries

try putting

doevents

between the two lines of code - that will stop the code moving on until all other actions have been completed.

Also, the code will only movenext if the query exists - if not, looks like you are in a permanent loop but can't really comment without seeing the full code - particularly relevant around when and how your query is created.

Not sure why you need to delete the query (assuming it is being repeatedly created temporarily) as it will cause front end bloat. Once created, use code to change the sql property. Then delete once the process has been completed
 

Alc

Registered User.
Local time
Today, 17:00
Joined
Mar 23, 2007
Messages
2,407
probably due to trying to delete it before transferspreadsheet has finished populating the workbook - i.e a small query not a problem, but a problem for larger queries

try putting

doevents

between the two lines of code - that will stop the code moving on until all other actions have been completed.
This is the sort of thing I suspected I might need. I couldn't work out what phrase to search for. Will make the change and retest.

Also, the code will only movenext if the query exists - if not, looks like you are in a permanent loop but can't really comment without seeing the full code - particularly relevant around when and how your query is created.
I understand the confusion there, I was trying to keep the code to a minimum when posting. The loop problem doesn't happen - the code either works or it crashes.
Not sure why you need to delete the query (assuming it is being repeatedly created temporarily) as it will cause front end bloat. Once created, use code to change the sql property. Then delete once the process has been completed
The query names are based on a combination of Part Number, Engineer and Days Outstanding and are generated on the fly. Since the same query name will rarely, if ever, be used twice, I was using the combination of those three items to create a unique name which, in turn, was used as the name of the Excel worksheet, before being deleted. If it's going to cause bloat I may need to rethink that.
 

Alc

Registered User.
Local time
Today, 17:00
Joined
Mar 23, 2007
Messages
2,407
Sadly, this results in the same error at the same point as before
Code:
If QueryExists(strSheetNameNew) Then
   Debug.Print strSheetNameNew & " exists"
                
   DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, strSheetNameNew, strNewBook, True
   DoEvents
   DoCmd.DeleteObject acQuery, strSheetNameNew
                            
   RstExport.MoveNext
Else
   Debug.Print strSheetNameNew & " does Not Exist"
                
   GoTo Replace_Query
End If
The QueryExists function says the query is present and I can see it in the Navigation panel immediately after the fail, but the Transfer function thinks it's not there.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 21:00
Joined
Feb 19, 2013
Messages
16,553
in that case the implication is a problem with the query - it may not be a valid select query or (perhaps, don't know) the query is returning a null recordset. Or maybe the name itself is invalid e.g. has spaces, starts with a number, has illegal chars (which may or may not be visible), etc

As mentioned before - you have only provided a sample of the code - it may be that is OK or it may be you have left out something which is actually causing the error.
 

Alc

Registered User.
Local time
Today, 17:00
Joined
Mar 23, 2007
Messages
2,407
Thanks again.
This is the whole function. As I mentioned, frustratingly, it will run one minute but if tried a little while later will fail. Since the queries run will only change day-by-day, it's failing when running SQL that has previously worked. I can confirm this by looking at the debug prints of what was being exported when it failed.

Code:
Public Function Export_Data(strNewBook As String, strSheetName As String, Db As Database, strSQLToRun As String)
    Dim QdfNew As QueryDef
    Dim xlSheetToFormat As Object
    Dim RstExport As Recordset
    Dim strNewSQL As String
    Dim strSheetNameBase As String
    Dim strSheetNameNew As String
    Dim strPrefix As String
    Dim strPartNo As String
    Dim strYear As String
    
    On Error GoTo Err_Point

    strSheetNameBase = strSheetName
    
    Set RstExport = Db.OpenRecordset(strSQLToRun)
    If RstExport.RecordCount <> 0 Then
        RstExport.MoveFirst
        Do While Not RstExport.EOF
            strNewSQL = Db.QueryDefs("qryexceedancemgmt(MV)_MultiExport2").SQL
            
            strPrefix = Left(RstExport![DataTable], Len(RstExport![DataTable]) - 1)
            strYear = Right(RstExport![DataTable], 1)
            strPartNo = RstExport![Part#]
            
            strNewSQL = Replace(strNewSQL, "AAAAA", strPrefix)
            strNewSQL = Replace(strNewSQL, "BBBBB", strYear)
            strNewSQL = Replace(strNewSQL, "CCCCC", strPartNo)
            
            strSheetNameNew = strSheetNameBase & "_" & strPartNo & "_" & strPrefix & strYear

Replace_Query:

            Delete_Query (strSheetNameNew)
            Set QdfNew = Db.CreateQueryDef(strSheetNameNew, strNewSQL)
            Db.QueryDefs.Refresh
            
            If QueryExists(strSheetNameNew) Then
                'Debug.Print strSheetNameNew & " exists"
                
                DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, strSheetNameNew, strNewBook, True
                DoEvents
                DoCmd.DeleteObject acQuery, strSheetNameNew
                
                Set QdfNew = Nothing
            
                RstExport.MoveNext
            Else
                Debug.Print strSheetNameNew & " does Not Exist"
                
                GoTo Replace_Query
            End If

        Loop
    End If
    
    Exit Function
    
Err_Point:
    
    strResponse = MsgBox(Err.Number & Chr(13) & Err.DESCRIPTION, vbCritical, "Error")
    
End Function
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 05:00
Joined
May 7, 2009
Messages
19,169
why not add those queries into collection and later delete them
Code:
Public Function Export_Data(strNewBook As String, strSheetName As String, Db As Database, strSQLToRun As String)
    Dim QdfNew As QueryDef
    Dim xlSheetToFormat As Object
    Dim RstExport As Recordset
    Dim strNewSQL As String
    Dim strSheetNameBase As String
    Dim strSheetNameNew As String
    Dim strPrefix As String
    Dim strPartNo As String
    Dim strYear As String
    
    ' this is the collection
    Dim Coll As New Collection
    
    On Error GoTo Err_Point

    strSheetNameBase = strSheetName
    
    Set RstExport = Db.OpenRecordset(strSQLToRun)
    If RstExport.RecordCount <> 0 Then
        RstExport.MoveFirst
        Do While Not RstExport.EOF
            strNewSQL = Db.QueryDefs("qryexceedancemgmt(MV)_MultiExport2").SQL
            
            strPrefix = Left(RstExport![DataTable], Len(RstExport![DataTable]) - 1)
            strYear = Right(RstExport![DataTable], 1)
            strPartNo = RstExport![Part#]
            
            strNewSQL = Replace(strNewSQL, "AAAAA", strPrefix)
            strNewSQL = Replace(strNewSQL, "BBBBB", strYear)
            strNewSQL = Replace(strNewSQL, "CCCCC", strPartNo)
            
            strSheetNameNew = strSheetNameBase & "_" & strPartNo & "_" & strPrefix & strYear

Replace_Query:

            Delete_Query (strSheetNameNew)
            Set QdfNew = Db.CreateQueryDef(strSheetNameNew, strNewSQL)
            Db.QueryDefs.Refresh
            
            If QueryExists(strSheetNameNew) Then
                'Debug.Print strSheetNameNew & " exists"
                
                ' add the query name to collection object
                Coll.Add strSheetNewName
                
                DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, strSheetNameNew, strNewBook, True
                
                ' remove this and move to the end of function
                'DoEvents
                'DoCmd.DeleteObject acQuery, strSheetNameNew
                
                Set QdfNew = Nothing
             
                RstExport.MoveNext
            Else
                Debug.Print strSheetNameNew & " does Not Exist"
                
                GoTo Replace_Query
            End If

        Loop
    End If
    
Exit_Point:
    ' delete the queries made
    Dim i As Integer
    For i = 1 To Coll.Count
        CurrentDb.QueryDefs.Delete Coll.Item(i)
    Next
    Set Coll = Nothing
    
    Exit Function
    
Err_Point:
    
    strResponse = MsgBox(Err.Number & Chr(13) & Err.Description, vbCritical, "Error")
    Resume Exit_Point
End Function
 

CJ_London

Super Moderator
Staff member
Local time
Today, 21:00
Joined
Feb 19, 2013
Messages
16,553
you still have a loop issue

Code:
Do While Not RstExport.EOF
           ...

Replace_Query:

            Delete_Query (strSheetNameNew)
            Set QdfNew = Db.CreateQueryDef(strSheetNameNew, strNewSQL)
            Db.QueryDefs.Refresh

            If QueryExists(strSheetNameNew) Then
               ....
            Else
                ....
                
                GoTo Replace_Query
            End If

        Loop

if query does not exist it returns to the replace_query place holder to delete it with the 'delete_query' function - which might be generating your error if it tries to delete a query that does not exist. If there is no error handling in that function, the error will pass back up to the error handler in the calling function.

How confident are you that it is the transferspreadsheet line is the line generating the error? Have you tried stepping through the code?

Have you debug printed strNewSQL for the errored version and confirmed it runs without error?
 

Alc

Registered User.
Local time
Today, 17:00
Joined
Mar 23, 2007
Messages
2,407
Thanks for the suggestion.

Unfortunately, it didn't work. I made the change and ran the process twice successfully, but on the third attempt I got the same error message as originally (albeit for a different query name).
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 05:00
Joined
May 7, 2009
Messages
19,169
maybe add an extra line:
Code:
            Set QdfNew = Db.CreateQueryDef(strSheetNameNew, strNewSQL)
            Db.QueryDefs.Refresh
 
            Application.RefreshDatabaseWindow
 

Alc

Registered User.
Local time
Today, 17:00
Joined
Mar 23, 2007
Messages
2,407
maybe add an extra line:
Code:
            Set QdfNew = Db.CreateQueryDef(strSheetNameNew, strNewSQL)
            Db.QueryDefs.Refresh
 
            Application.RefreshDatabaseWindow
Alas, same result.

If the data weren't confidential, I'd try copying a zipped version of the db itself.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 21:00
Joined
Feb 19, 2013
Messages
16,553
Unfortunately, it didn't work.
you haven't answered my questions so for the final time

1. have you stepped through the code to confirm the error is where you say it is
2. have you debug.printed strNewSQL for the errored version and confirmed it runs without error?

and another obvious one
3. have you stopped the code before calling the queryexists function to confirm the query actually does exist - you may need to add Arnel's code to refresh the database window
 

Alc

Registered User.
Local time
Today, 17:00
Joined
Mar 23, 2007
Messages
2,407
you haven't answered my questions so for the final time

1. have you stepped through the code to confirm the error is where you say it is
2. have you debug.printed strNewSQL for the errored version and confirmed it runs without error?

and another obvious one
3. have you stopped the code before calling the queryexists function to confirm the query actually does exist - you may need to add Arnel's code to refresh the database window
Sorry, I missed those.
In order:
1. Yes. The error is definitely at that line.
2. Yes, the debug.print prints what I would expect to see. No spaces, special characters, missing characters, etc.
3. Yes. This was the case even before refreshing the window. I also commented out the problematic line and added another call to QueryExists to confirm that the query is present both before and after the problematic line.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 21:00
Joined
Feb 19, 2013
Messages
16,553
sounds more and more like there is a problem with the sql
2. Yes, the debug.print prints what I would expect to see. No spaces, special characters, missing characters, etc.
and does it execute ok if you copy and paste it into a query?
 

CJ_London

Super Moderator
Staff member
Local time
Today, 21:00
Joined
Feb 19, 2013
Messages
16,553
and just to clarify

I made the change and ran the process twice successfully, but on the third attempt I got the same error message as originally (albeit for a different query name).
what if you run the process two or three times for the same query? Or are you saying this is happening randomly for all queries
 

Alc

Registered User.
Local time
Today, 17:00
Joined
Mar 23, 2007
Messages
2,407
sounds more and more like there is a problem with the sqland does it execute ok if you copy and paste it into a query?
Yes the SQL will run fine if I copy and paste it.

what if you run the process two or three times for the same query? Or are you saying this is happening randomly for all queries
Yep, I can literally run the process multiple times in a row (the queries will be the same until the next day). The first few will work, then the last fails. Or it fails first time, then runs the next. There are between 15 and 40 queries for a given engineer and the failure can occur on any query.

I still think it's connected to something not finishing before the next thing starts but I can't pin it down further.
 

Micron

AWF VIP
Local time
Today, 17:00
Joined
Oct 20, 2018
Messages
3,476
Difficult to be sure when lengthy and nested code can't be tested, but here's what doesn't look right to ,me.
If this is false: If QueryExists(strSheetNameNew) Then
go here: GoTo Replace_Query
which says do this: Delete_Query (strSheetNameNew)

If it doesn't exist, how can you delete it?

Also, I question your placement of RstExport.MoveNext - you will only move if above is TRUE. If not, you are looping while on the same record.

RstExport.MoveNext
Else
Debug.Print strSheetNameNew & " does Not Exist"
GoTo Replace_Query
End If
<<<no move if false
Loop
You probably need to either move or add .Move just before the loop?
Also, it is usually considered best practice best to avoid GoTo to control code direction.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 21:00
Joined
Feb 19, 2013
Messages
16,553
@micron: I made the same point in post #8 but the OP is 100% confident the issue is with the transferspreadsheet line.

Since introducing doevents to slow things down did not resolve the issue I believe that due to the randomness of the error the issue is either with the data or the looping. But unable to get a clear confirmation that this has been tested thoroughly or the code has been stepped through to check variable values at the time of failure.

@Alc - suggest you research and apply debug.assert to stop the code when about to fail (i.e. before the Debug.Print strSheetNameNew & " does Not Exist" line and thoroughly check all the variables and step through the code at that point.

just in case it is actually the speed of operation causing the issue I would add further doevents between each line of code and see if that resolves the issue

then repeat same query, same data enough times to be sure the problem is solved.
 

JHB

Have been here a while
Local time
Today, 22:00
Joined
Jun 17, 2012
Messages
7,732
Try in the error handler to resume to the same code line, remember to put a breakpoint in that code line where the Resume is, then if it is a time issue it will execute.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 05:00
Joined
May 7, 2009
Messages
19,169
as suggested, add a pause period on the code:
Code:
Replace_Query:

            Delete_Query (strSheetNameNew)
            Set QdfNew = Db.CreateQueryDef(strSheetNameNew, strNewSQL)
            Db.QueryDefs.Refresh
            WaitSeconds 10
            
            If QueryExists(strSheetNameNew) Then
                'Debug.Print strSheetNameNew & " exists"
                
                DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, strSheetNameNew, strNewBook, True
                WaitSeconds 10
                DoCmd.DeleteObject acQuery, strSheetNameNew
on a Module:
Code:
Option Compare Database
Option Explicit

#If VBA7 Then
Private Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal lngMilliSeconds As Long)
#Else
Private Declare Sub Sleep Lib "kernel32" (ByVal lngMilliSeconds As Long)
#End If

Public Function WaitSeconds(intSeconds As Integer)
  ' Comments: Waits for a specified number of seconds
  ' Params  : intSeconds      Number of seconds to wait
  ' Source  : Total Visual SourceBook

  On Error GoTo PROC_ERR

  Dim datTime As Date

  datTime = DateAdd("s", intSeconds, Now)

  Do
   ' Yield to other programs (better than using DoEvents which eats up all the CPU cycles)
    Sleep 100
    DoEvents
  Loop Until Now >= datTime

PROC_EXIT:
  Exit Function

PROC_ERR:
  MsgBox "Error: " & Err.Number & ". " & Err.Description, , "modDateTime.WaitSeconds"
  Resume PROC_EXIT
End Function
try adjusting if the waiting time is too long/short.
 

Users who are viewing this thread

Top Bottom