Refresh Jet

[Millon]

Access is giving me errors when I try to export a newly created query.

Run-time error '3011':
The Microsoft Jet database engine could not find the object 'example_query'. Make sure the object exists and that you spell its name and the path name correctly.

However, if I go into debug mode, and continue (F5), then it exports without any errors. Sometimes, if the query is large, it will give the error a second or third time, but eventually Access realizes that the query actually does exist, and will proceed to export it.

This means that there must be some refreshing delays in access. I tried to get around this problem by calling a WaitForQuery sub before exporting, but I still get errors.

Here is a simplified example of my current approach:

Option Compare Database
Option Explicit

'sleep function
Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

'looks though the list of all queries in the database and returns true if it finds one with a matching name
Public Function QueryExists(queryName As String, db As Database) As Boolean
    Dim qdf As QueryDef
    QueryExists = False
    For Each qdf In db.QueryDefs
        If qdf.Name = queryName Then
            QueryExists = True
            Exit Function
        End If
    Next qdf
End Function

'depending on what preExisting is set to, this sub either waits for a query to show up or be deleted
Public Sub waitForQuery(q As String, preExisting As Boolean)
    Dim c As Integer
    c = 0
    
    ' if preExisting is set to true, then this section loops until the query is no longer in the database
    ' if preExisting is set to false, then this section loops until the query is detected in the database
    Do While (QueryExists(q, CurrentDb) = preExisting)
        Sleep 200
        c = c + 1
    Loop

    If preExisting = True Then
        Debug.Print ("Waited " & c & " times for " & q & " to be deleted")
    Else
        Debug.Print ("Waited " & c & " times for " & q & " to show up")
    End If
End Sub


Sub exportExample()
    Dim exampleQueryDef As QueryDef
    
    'deletes the query if it is already in the database
    If QueryExists("example_query", CurrentDb) = True Then
        DoCmd.DeleteObject acQuery, "example_query"
        'waits until Access realizes that the query has been deleted
        waitForQuery "example_query", True
    End If
    
    'make the query
    Set exampleQueryDef = CurrentDb.CreateQueryDef("example_query")
    'give SQL statement to query
    exampleQueryDef.SQL = "SELECT * FROM SomeTable"
    
    'wait until Access detects the query
    waitForQuery exampleQueryDef.Name, False
    
    'additional confirmation that the query exists
    If QueryExists(exampleQueryDef.Name, CurrentDb) = True Then
        Debug.Print "For a second time " & exampleQueryDef.Name & " exists"
    End If
    
    'export Query
    DoCmd.TransferText acExportDelim, "ExampleSpec", exampleQueryDef.Name, "C:\Documents and Settings\Nathan\Desktop\Output\" & exampleQueryDef.Name & ".txt", True
    Set exampleQueryDef = Nothing
End Sub

Even when the immediate window says that the query exists before exporting, there are still errors. So it seems that even if the query detection method reports that the query exists, Jet still doesn't know about it.
What makes this even more frustrating is that sometimes it will export without any errors, so it may seem like the problem is solved, when in reality, it was just lucky refresh timing.

Is there a way to see what queries jet thinks are part of the database, or to force jet to refresh its query list?

 

[Banana]

I'm not completely sure as I don't know which line of code it errors on.

That said, I did had similar problem with deleting tabledefs, sometime it wasn't getting deleted. This was fixed when I used CurrentDb() which guarantees that all objects will be up to date.

So add into CurrentDb when doing actions such as deletequery and make it explicit rather than using implicit Docmd.

HTH.

 

[Millon]

The error is on the export line

DoCmd.TransferText acExportDelim...

So are you suggesting that I change it to:

CurrentDB(DoCmd.TransferText acExportDelim...)

I'll give it a try and report back with my results.

Edit: I don't think I'm doing what you had in mind. Could you please post the modified line?

 

[Banana]

No. CurrentDb() should be used when you're referencing TableDef or QueryDef:

Set MyQuery = CurrentDb.QueryDefs("TheNameOfQuery")

Another thing I missed earlier- why do you want to delete the query then recreate the query? You can just requery to get updated recordset? You use OpenRecordset to return the most current recordset based on the query's sql, then you can transfertext with that recordset...

 

[Millon]

I always use CurrentDB when referencing queries.

The only place I don't is when deleting them, but I don't know how to do it without using DoCmd.
Now it may not seem necessary to delete the queries before updating the SQL, but I'm afraid that Access's refresh problems will export the old version of the same query with a different SQL. I admit that this may be too paranoid, but given all the weird stuff I've seen access do, I don't trust it.
So I delete them first, and use waitforquery to pause operation until the query is completely deleted, then remake the query and pause again until it detects that the query is there.

You suggest using recordsets to get the updated query results, but I think transfertext only works with tables or queries. I could convert the recordset into a table, but that's a pain to do, since you have to do an insert into statement for each field.

What I am really looking for is a way to ask jet if it sees the query, or force it to refresh. An ultra pragmatic approach is to just insert a wait for 5 seconds before each export, but I would rather not wait longer than I have to, and there is no guarantee that it will work for larger queries.