Hi there,

I've got an Append Query in an Access 2000 database that is not visible on the Query tab. I know it exists because when I try to create a new query and call it the same name it responds with "The name entered already exists for another object of the same type in this database"

I've tried ticking the System and Hidden objects but to no avail. Somebody please tell me where this query is hiding??

Thanks in advance.


Paul

Have you tried to compact and repair.

OR
If you are able to rebuild the query easily then create a new query and go to SQL view, type in
DROP TABLE YourQueryName
run the query, and this should remove the query fully from the database. You can then recreate the query.

Yes I tried compact and repair but to no avail. Unfortunately I don't have the SQL for the query so I can't recreate it. Here's the weird thing, if I create a blank database and import the queries it still isn't visible. However, when I try to create another query with the same name on this new database it lets me??

Any further clues?

To try and recover the SQL in the query copy the following code into a module and then go to the Immediate Window and type in
Call GetQrySQL("YourQueryName")
and press return to run

If the query is found it should return the SQL from the query to the immediate window. When you have the SQL you can then drop the query and rebuild it.


Sub GetQrySQL(strQryName As String)
On Err GoTo Err_GetQrySQL
Dim dbs As Database, qdf As QueryDef
Dim strSQL As String

' Return reference to current database.
Set dbs = CurrentDb
' Refresh QueryDefs collection.
dbs.QueryDefs.Refresh
' If RecentHires query exists, delete it.
For Each qdf In dbs.QueryDefs
If qdf.Name = strQryName Then
Debug.Print qdf.SQL
Exit Sub
End If
Next qdf

Set qdf = Nothing
Set dbs = Nothing

Exit_GetQrySQL:
Exit Sub

Err_GetQrySQL:
MsgBox Err.Description & Err
Resume Exit_GetQrySQL

End Sub

Woo!!! That's sorted it, SQL appeared, copy and pasted into new query, saved query and voila!! It's now appeared back in the query tab.

Thanks a million.