Dump database object names into a table 1

[dweis]

Is it possible to write code to create a table with the database object names & description dumped into it? 2 fields - Name and Type - run the code to populate the table with "Table" & tablename, "Form" & formname etc. Any help would be appreciated.

 

[coco86]

To loop through the objects, use code similar to the following for each object type

Dim obj as object
For Each obj In CurrentProject.ObjectType
do what you want with obj.Name
Next

 

[illini]

dweis,

Here's a little sub to get you started. You can then append a table based upon the data obtained.

Hope this works for you.


Public FormID As Form
Public tbl As TableDef
Public qry As QueryDef

sub test()
For Each FormID In Access.Forms
MsgBox FormID.Name
Next FormID

For Each tbl In Access.CurrentDb.TableDefs
MsgBox tbl.Name
Next tbl

For Each qry in access.CurrentDb.QueryDefs
MsgBox qry.Name
Next qry

end sub -illini

 

[dweis]

Figured it out - code below for anyone interested. I put this in a forms onload event.

Dim accObject As Access.AccessObject
Dim InsertSQL As String
DoCmd.SetWarnings off
'Fill with Tables
For Each accObject In CurrentData.AllTables
InsertSQL = "INSERT INTO d1 (t, nm) VALUES ("
InsertSQL = InsertSQL & """ Table """ & ", "
InsertSQL = InsertSQL & "'" & accObject.Name & "'" & ""
DoCmd.RunSQL InsertSQL
Next

'If currently opened file is an Access database (mdb), then fill
'with queries.
'Otherwise, if it is an Access project (adp), fill with views,
'stored procedures, database diagrams, and functions.
If CurrentProject.ProjectType = acMDB Then
For Each accObject In CurrentData.AllQueries
InsertSQL = "INSERT INTO d1 (t, nm) VALUES ("
InsertSQL = InsertSQL & """ Query """ & ", "
InsertSQL = InsertSQL & "'" & accObject.Name & "'" & ""
DoCmd.RunSQL InsertSQL

Next
Else
For Each accObject In CurrentData.AllViews
InsertSQL = "INSERT INTO d1 (t, nm) VALUES ("
InsertSQL = InsertSQL & """ View """ & ", "
InsertSQL = InsertSQL & "'" & accObject.Name & "'" & ""
DoCmd.RunSQL InsertSQL

Next
For Each accObject In CurrentData.AllStoredProcedures
InsertSQL = "INSERT INTO d1 (t, nm) VALUES ("
InsertSQL = InsertSQL & """ Procedure """ & ", "
InsertSQL = InsertSQL & "'" & accObject.Name & "'" & ""
DoCmd.RunSQL InsertSQL

Next
For Each accObject In CurrentData.AllDatabaseDiagrams
InsertSQL = "INSERT INTO d1 (t, nm) VALUES ("
InsertSQL = InsertSQL & """ Diagram """ & ", "
InsertSQL = InsertSQL & "'" & accObject.Name & "'" & ""
DoCmd.RunSQL InsertSQL

Next
For Each accObject In CurrentData.AllFunctions
InsertSQL = "INSERT INTO d1 (t, nm) VALUES ("
InsertSQL = InsertSQL & """ Function """ & ", "
InsertSQL = InsertSQL & "'" & accObject.Name & "'" & ""
DoCmd.RunSQL InsertSQL

Next
End If

'Fill list with forms.
For Each accObject In CurrentProject.AllForms
InsertSQL = "INSERT INTO d1 (t, nm) VALUES ("
InsertSQL = InsertSQL & """ Form """ & ", "
InsertSQL = InsertSQL & "'" & accObject.Name & "'" & ""
DoCmd.RunSQL InsertSQL

Next
'Fill list with reports.
For Each accObject In CurrentProject.AllReports
InsertSQL = "INSERT INTO d1 (t, nm) VALUES ("
InsertSQL = InsertSQL & """ Report """ & ", "
InsertSQL = InsertSQL & "'" & accObject.Name & "'" & ""
DoCmd.RunSQL InsertSQL

Next
'Fill list with data access pages.
For Each accObject In CurrentProject.AllDataAccessPages
InsertSQL = "INSERT INTO d1 (t, nm) VALUES ("
InsertSQL = InsertSQL & """ Page """ & ", "
InsertSQL = InsertSQL & "'" & accObject.Name & "'" & ""
DoCmd.RunSQL InsertSQL

Next
'Fill list with macros.
For Each accObject In CurrentProject.AllMacros
InsertSQL = "INSERT INTO d1 (t, nm) VALUES ("
InsertSQL = InsertSQL & """ Macro """ & ", "
InsertSQL = InsertSQL & "'" & accObject.Name & "'" & ""
DoCmd.RunSQL InsertSQL

Next
'Fill list with modules.
For Each accObject In CurrentProject.AllModules
InsertSQL = "INSERT INTO d1 (t, nm) VALUES ("
InsertSQL = InsertSQL & """ Modules """ & ", "
InsertSQL = InsertSQL & "'" & accObject.Name & "'" & ""
DoCmd.RunSQL InsertSQL
Next
DoCmd.SetWarnings (Warningson)

Thanks!

 

[MichaelRed]

hmmmmmmmmmmmm,

with some minor amount of simple decoding, you could just generate a query aginst the (system) table MSysObjects, It include the names and other information re ALL objects in an Ms A. db. The "Type" field needs to be 'decoded', but that is relatively obvious from a brief inspection of a "raw" query.

for the inept, I have generated a "starter" query string which excludes many of the non-usert level objects:

SELECT MSysObjects.Name, MSysObjects.Type
FROM MSysObjects
WHERE (((MSysObjects.Name) Not Like &quot;MSys*&quot; And (MSysObjects.Name) Not Like &quot;~*&quot;) AND ((MSysObjects.Type)<>1 And (MSysObjects.Type)<>2 And (MSysObjects.Type)<>3 And (MSysObjects.Type)<>-32757))
WITH OWNERACCESS OPTION;

Just copy the above "string" into an "empty query SQL view object and "run it". Adjust as desired to see other / fewer object types (of even specific object groups). If you need to track this regularly, save the query as you like it.

For those who intend to make database programming their principal means of income, I would strongly suggest obtaining several or more third party reference / tutorial books re both Ms. Access, relational database design, SQL language and the host language (VB), many of these publications provide information re the above both specifically (i.e. Ms. A.) and generically (i.e. Schema), and can save a bit of time / energy / frustration and genneral feelings of hopelessness.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[coco86]

Wow! Now that's cool. I love it when I learn new things!