Append Query missing from Query tab, but it's there!!

[misterpon]

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

 

[antomack]

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.

 

[misterpon]

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?

 

[antomack]

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

 

[misterpon]

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.