How can I export a list of database objects?

[Guest]

I want to export a list of tables, a list of queries and a list of reports so
I can audit my database.

 

[Brendan Reynolds]

You didn't mention the format in which you wish to export. The following
example exports to a CSV (Comma-Separated Values) file, which can be
imported into many applications, for example you could import it into
another Access database or you can open it in Excel.

 

[Brendan Reynolds]

It would probably help if I posted the example!

Public Sub ExportCSV()

Dim strFile As String
Dim intFile As Integer
Dim aob As AccessObject

strFile = CurrentProject.Path & "\ObjectList.csv"
intFile = FreeFile

'This will overwrite any file with same
'name in same location - use with care.
Open strFile For Output As intFile
Write #intFile, "ObjectType", "ObjectName"
For Each aob In CurrentData.AllTables
If Left$(aob.Name, 4) <> "MSys" Then
Write #intFile, "Table", aob.Name
End If
Next aob
For Each aob In CurrentData.AllQueries
Write #intFile, "Query", aob.Name
Next aob
For Each aob In CurrentProject.AllReports
Write #intFile, "Report", aob.Name
Next aob
Close #intFile

End Sub

 

[Guest]

CSV is fine - where's the example?

Thanks

You didn't mention the format in which you wish to export. The following
example exports to a CSV (Comma-Separated Values) file, which can be
imported into many applications, for example you could import it into
another Access database or you can open it in Excel.

 

[Guest]

SELECT
IIf(MSysObjects.Type=1,'TABLE',IIf(MSysObjects.Type=5,'QUERY',IIf(MSysObjects.Type=5,'QUERY',IIf(MSysObjects.Type=-32768,'FORM',IIf(MSysObjects.Type=-32764,'REPORT',IIf(MSysObjects.Type=-32766,'MACRO',IIf(MSysObjects.Type=-32761,'MODULE',IIf(MSysObjects.Type=-32756,'PAGE',IIf(MSysObjects.Type=8,'RELATIONSHIPS',IIf(MSysObjects.Type=6,'TABLE
LINKED','Unknown')))))))))) AS ObjectName, MSysObjects.Name,
MSysObjects.Type, MSysObjects.Name
FROM MSysObjects
WHERE (((MSysObjects.Name) Not Like "~*" And (MSysObjects.Name) Not Like
"Msys*") AND ((MSysObjects.Type) Not In (2,3)))
ORDER BY
IIf(MSysObjects.Type=1,'TABLE',IIf(MSysObjects.Type=5,'QUERY',IIf(MSysObjects.Type=5,'QUERY',IIf(MSysObjects.Type=-32768,'FORM',IIf(MSysObjects.Type=-32764,'REPORT',IIf(MSysObjects.Type=-32766,'MACRO',IIf(MSysObjects.Type=-32761,'MODULE',IIf(MSysObjects.Type=-32756,'PAGE',IIf(MSysObjects.Type=8,'RELATIONSHIPS',IIf(MSysObjects.Type=6,'TABLE LINKED','Unknown')))))))))), MSysObjects.Name;

 

[Brendan Reynolds]

I posted it later, Sam - do you see it now?

 

[Albert D.Kallal]

you can use the built in documenter.

Try tools->analyze->documenter.

The above is designed to printout everything you have in your database. It
will even printout code if you want...