Code to list Objects in DB lists deleted SQL

[Evi]

I use Access 2000. I'm running some code to list all the items in my
Database for auditing (I got a bit carried away with my queries). When it
lists the queries it lists some which look like they are the statements
behind combo boxes etc eg
~sq_cFrmAdvancedChoose~sq_ccboFromMonth
A form called FrmAdvancedChoose does indeed have a combo called
cboFromMonth.
But I've notes that some of these '~sq_c's listed include the names of forms
which I've actually deleted ages ago. I've compacted since too. I noticed
that when I imported the whole database into a clean one, and re-ran the
code, these now disappeared. What's this about then? Does Access retain some
kind of record of deleted items?
Here's the code I'm using to list the db objects in a table:

Sub ListObjectsInDB()
'lists all objects within the db
'in a table and assigns an Object Type code to them
Dim MyCount As Integer
Dim MyQueries() As String
Dim MyTable As Recordset
Dim MyTableName As String
Dim TblName As String
Dim MyField As String
Dim MyTypeField As String
Dim a As Integer
Dim i As Integer
Dim QueryCode As Integer
Dim TableCode As Integer
Dim FormCode As Integer
Dim ReportCode As Integer
Dim SqlCode As Integer
Dim dbs As Database, ctr As Container, doc As Document
'Table and field names
MyTableName = "TblAppendDBObjects"
MyField = "DBAObject"
MyTypeField = "ObjTpID"

QueryCode = 4
TableCode = 3
FormCode = 9
ReportCode = 5
SqlCode = 10

Set MyTable = CurrentDb.OpenRecordset(MyTableName, dbOpenDynaset)
MyCount = CurrentDb.QueryDefs.Count
MyCount = MyCount - 1
ReDim MyQueries(MyCount)
'list queries in db
For a = 1 To MyCount
MyQueries(a) = CurrentDb.QueryDefs(a).Name
MyTable.AddNew
MyTable(MyField) = MyQueries(a)
If Left(MyQueries(a), 1) = "~" Then
'those weird sql queries get listed with a diff code
MyTable(MyTypeField) = SqlCode
Else
MyTable(MyTypeField) = QueryCode
End If
MyTable.Update
Next a

' Return reference to current database.
Set dbs = CurrentDb
Set MyTable = dbs.OpenRecordset(MyTableName, dbOpenDynaset)
' list forms in db
Set ctr = dbs.Containers!Forms

For Each doc In ctr.Documents
MyTable.AddNew
MyTable(MyField) = doc.Name
MyTable(MyTypeField) = FormCode
MyTable.Update
Next doc

Set ctr = dbs.Containers!Reports
' list reports in db
For Each doc In ctr.Documents
Set MyTable = dbs.OpenRecordset(MyTableName, dbOpenDynaset)
MyTable.AddNew
MyTable(MyField) = doc.Name
MyTable(MyTypeField) = ReportCode
MyTable.Update
MyTable.Close
Next doc

'list tables in db
Set dbs = CurrentDb
For i = 0 To dbs.TableDefs.Count - 1
Set MyTable = dbs.OpenRecordset(MyTableName, dbOpenDynaset)
'no system tables - msys
TblName = dbs.TableDefs(i).Name
If Left(TblName, 4) = "MSys" Then
i = i + 1
Else
MyTable.AddNew
MyTable(MyField) = TblName
MyTable(MyTypeField) = TableCode
MyTable.Update
MyTable.Close
End If
Next i
'tidy up
Set MyTable = Nothing
Set dbs

Evi

 

[Tom van Stiphout]

On Sat, 8 Nov 2008 15:36:25 -0000, "Evi"

The "~sq_" queries are temporary queries that Access creates when
needed, for example when you're doing query-by-form. You can ignore
them.

-Tom.
Microsoft Access MVP

 

[Douglas J. Steele]

Don't think that's true, Tom (that they're temporary queries).

As Evi points out, when you specify SQL as the RowSource for a list box or
combo box, or as the RecordSource for a form or report, the queries will
show up with ~sq names.

Evi: You mention that you're seeing queries including forms that no longer
exist. Do they go away when you compact your database?

 

[Evi]

Thanks Tom, you've cleared up the mystery

Evi

On Sat, 8 Nov 2008 15:36:25 -0000, "Evi"

The "~sq_" queries are temporary queries that Access creates when
needed, for example when you're doing query-by-form. You can ignore
them.

-Tom.
Microsoft Access MVP

 

[Evi]

No, Douglas, they don't go away after compacting. Only if I open a blank
database, import everything into the blank one, and then run the Sub again.
That's why Tom's explanation makes sense. And the Sub doesn't list Forms
that don't exist, only these ~sq entries which contain the names of the
long-deleted forms.
Evi