Code to list Objects in DB lists deleted SQL

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


.

Relevant Pages

  • Re: Export Excel To Access Wizard HELP!!!
    ... permissions needed to run the queries needed by your workbook. ... >> write Excel code that uses the DAO library to open the secured database ... >> The sample procedure below opens a secured mdb and will work with minor ... >> Dim dbW 'As DAO.Workspace 'to change from late to ...
    (microsoft.public.access.externaldata)
  • Re: Trying to delete tables, forms, queries, etc. in an external datab
    ... > ' Delete all queries ... database and use that instance's DoCmd.DeleteObject method. ... Sub DeleteInExternalDBAs ... Dim cnt As DAO.Container ...
    (microsoft.public.access.modulesdaovba)
  • RE: Finding all queries which use a table
    ... Dim db As Database ... Dim qdf As QueryDef ... Dim numFound As Long ... Does anyone know of a tool that can scan all queries in a database and find ...
    (microsoft.public.access.queries)
  • Re: Code to list Objects in DB lists deleted SQL
    ... database, import everything into the blank one, and then run the Sub again. ... You mention that you're seeing queries including forms that no longer ... 'lists all objects within the db ... Dim MyQueries() As String ...
    (microsoft.public.access.formscoding)
  • Re: Code to list Objects in DB lists deleted SQL
    ... Tom (that they're temporary queries). ... lists the queries it lists some which look like they are the statements ... Dim MyQueries() As String ...
    (microsoft.public.access.formscoding)
Loading