Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 07-08-2019, 04:27 AM   #1
Alc
Newly Registered User
 
Join Date: Mar 2007
Location: Ontario
Posts: 2,308
Thanks: 48
Thanked 28 Times in 27 Posts
Alc will become famous soon enough Alc will become famous soon enough
Access acting as if query is not present, just after telling me it is

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"
                
       DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, strSheetNameNew, strNewBook, True
       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.

Alc is offline   Reply With Quote
Old 07-08-2019, 05:10 AM   #2
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,333
Thanks: 40
Thanked 3,670 Times in 3,538 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: Access acting as if query is not present, just after telling me it is

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
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
The Following User Says Thank You to CJ_London For This Useful Post:
Alc (07-08-2019)
Old 07-08-2019, 05:54 AM   #3
Alc
Newly Registered User
 
Join Date: Mar 2007
Location: Ontario
Posts: 2,308
Thanks: 48
Thanked 28 Times in 27 Posts
Alc will become famous soon enough Alc will become famous soon enough
Re: Access acting as if query is not present, just after telling me it is

Quote:
Originally Posted by CJ_London View Post
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.

Quote:
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.
Quote:
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 is offline   Reply With Quote
Old 07-08-2019, 06:35 AM   #4
Alc
Newly Registered User
 
Join Date: Mar 2007
Location: Ontario
Posts: 2,308
Thanks: 48
Thanked 28 Times in 27 Posts
Alc will become famous soon enough Alc will become famous soon enough
Re: Access acting as if query is not present, just after telling me it is

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.
Alc is offline   Reply With Quote
Old 07-08-2019, 06:45 AM   #5
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,333
Thanks: 40
Thanked 3,670 Times in 3,538 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: Access acting as if query is not present, just after telling me it is

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.
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 07-08-2019, 07:11 AM   #6
Alc
Newly Registered User
 
Join Date: Mar 2007
Location: Ontario
Posts: 2,308
Thanks: 48
Thanked 28 Times in 27 Posts
Alc will become famous soon enough Alc will become famous soon enough
Re: Access acting as if query is not present, just after telling me it is

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
Alc is offline   Reply With Quote
Old 07-08-2019, 08:29 AM   #7
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,569
Thanks: 68
Thanked 2,745 Times in 2,630 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Access acting as if query is not present, just after telling me it is

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

__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
The Following User Says Thank You to arnelgp For This Useful Post:
Alc (07-09-2019)
Old 07-08-2019, 08:57 AM   #8
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,333
Thanks: 40
Thanked 3,670 Times in 3,538 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: Access acting as if query is not present, just after telling me it is

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?
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 07-08-2019, 08:57 AM   #9
Alc
Newly Registered User
 
Join Date: Mar 2007
Location: Ontario
Posts: 2,308
Thanks: 48
Thanked 28 Times in 27 Posts
Alc will become famous soon enough Alc will become famous soon enough
Re: Access acting as if query is not present, just after telling me it is

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).
Alc is offline   Reply With Quote
Old 07-08-2019, 09:08 AM   #10
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,569
Thanks: 68
Thanked 2,745 Times in 2,630 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Access acting as if query is not present, just after telling me it is

maybe add an extra line:
Code:
            Set QdfNew = Db.CreateQueryDef(strSheetNameNew, strNewSQL)
            Db.QueryDefs.Refresh
 
            Application.RefreshDatabaseWindow
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 07-08-2019, 09:16 AM   #11
Alc
Newly Registered User
 
Join Date: Mar 2007
Location: Ontario
Posts: 2,308
Thanks: 48
Thanked 28 Times in 27 Posts
Alc will become famous soon enough Alc will become famous soon enough
Re: Access acting as if query is not present, just after telling me it is

Quote:
Originally Posted by arnelgp View Post
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.
Alc is offline   Reply With Quote
Old 07-08-2019, 09:20 AM   #12
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,333
Thanks: 40
Thanked 3,670 Times in 3,538 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: Access acting as if query is not present, just after telling me it is

Quote:
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
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 07-08-2019, 09:35 AM   #13
Alc
Newly Registered User
 
Join Date: Mar 2007
Location: Ontario
Posts: 2,308
Thanks: 48
Thanked 28 Times in 27 Posts
Alc will become famous soon enough Alc will become famous soon enough
Re: Access acting as if query is not present, just after telling me it is

Quote:
Originally Posted by CJ_London View Post
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.
Alc is offline   Reply With Quote
Old 07-08-2019, 09:40 AM   #14
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,333
Thanks: 40
Thanked 3,670 Times in 3,538 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: Access acting as if query is not present, just after telling me it is

sounds more and more like there is a problem with the sql
Quote:
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
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 07-08-2019, 09:43 AM   #15
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,333
Thanks: 40
Thanked 3,670 Times in 3,538 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: Access acting as if query is not present, just after telling me it is

and just to clarify

Quote:
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

__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
check if the values present in column A of excelsheet are also present in Access tabl aman Modules & VBA 1 09-03-2014 09:11 AM
Don't need a heading telling me it's a sum JordanR Queries 2 09-17-2008 07:07 AM
Find records that are present in one table, but are not present in other tables. mattcdse Queries 6 06-01-2007 04:11 AM
Access acting weird DomZ General 7 08-06-2003 06:31 PM
Telling a query to return last 3 rows durdle Queries 4 07-18-2003 12:34 PM




All times are GMT -8. The time now is 11:10 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World