ow to list all tables and queries in access 1

[wtina]

Hi,

I would like to list all tables, queries and other I have in .mdb format. There are many access databases we use and I need to know all titles of tables and queries that are in them. Is this possible?

Thanks for your help!

 

[chickey]

I use the following function to populate a listbox on an
export form which allows the user to see tables, queries
or reports in a listbox according to their selection
(sent in as pObjectType).

Reply if you still have problems.

Regards...

==

Public Function ListObjects(pObjectType As String)
'For the relevant type of object (query/table/report)
'list them in the listbox on the form
On Error GoTo ListObjects_Err

Dim Db As Database
Dim vList As String
Dim vDocuments As Document
Dim i As Integer

Set Db = DBEngine.Workspaces(0).Databases(0)

Select Case pObjectType
Case "Queries"
Db.QueryDefs.Refresh
If Db.QueryDefs.count = 0 Then
MsgBox "No " & pObjectType & " exist in the database"
Else
For i = 0 To Db.QueryDefs.count - 1
vList = vList & Db.QueryDefs(i).Name & ";"
End If
Next i
End If
Case "Tables"
If Db.TableDefs.count = 0 Then
MsgBox "No " & pObjectType & " exist in the database"
Else
For i = 0 To Db.TableDefs.count - 1
If Not Left(Db.TableDefs(i).Name, 4) = "MSys" Then
vList = vList & Db.TableDefs(i).Name & ";"
End If
Next i
End If
Case "Reports"
If Db.Containers(pObjectType).Documents.count = 0 Then
MsgBox "No " & pObjectType & " exist in the database"
Else
With Db.Containers(pObjectType)
For Each vDocuments In .Documents
vList = vList & vDocuments.Name & ";"
End If
Next vDocuments
End With
End If
End Select

'Set the relevant form fields requirements

Forms![frm_ExportForm]![lst_Originator].ColumnWidths = "2 in"
Forms![frm_ExportForm]![lst_Originator].ColumnCount = 1
Forms![frm_ExportForm]![lst_Originator].RowSourceType = "Value List"
Forms![frm_ExportForm]![lst_Originator].RowSource = vList

Forms![frm_ExportForm]![fld_Option] = pObjectType
Forms![frm_ExportForm]![fld_OriginatorLabel].Caption = " "
Forms![frm_ExportForm]![fld_OriginatorLabel].Caption = "Select from " & pObjectType

ListObjects_Exit:
On Error Resume Next
Exit Function

ListObjects_Err:
Call ut_Bug(Err.Description, Err.Number, Erl(), Application.CurrentObjectName, "ListObjects&quot
Resume ListObjects_Exit

End Function

 

[wtina]

Thank you,(-:

I'll try and probably come back with questions.

 

[MichaelRed]

See also, MSysObjects. A wee bit of 'investigation' will easily show how to obtain a recordset listing any subset of objects in an Ms. A. Database. (Hint, look at the Type column and relate it to your specific object type [table | Query | Form | Report | Module |even (UGH!) Macro] ... but you DO need to be careful to exclude some items (the system objects themselves?)


MSys* items are normally 'hidden' objects, so you can 'un-hide' them by setting the attribute in the Menu [Options -> Views] Show hidden objects, or you can see the resulkts by writting a SIMPLE query in the SQL View of a Query.

e.g. no code required for this.







MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[wtina]

Thank you, I'll give it a shoot (-:

 

[wtina]

Michael or chickey, hi again,

I have one new question,
is it possible in any way to get a list of macros and what exactlly do they do. So I need to list all tables, queries, reports, macros and I would be the happiest if I knew why are macros there...

Thanks again for your help

 

[chickey]

Modify the code to include the following as an additional case in the select case statement:

Case "Macros"
  If Application.CurrentProject.AllMacros.count = 0 Then
     MsgBox "No " & pObjectType & " exist in the database"
  Else
     For i = 1 To Application.CurrentProject.AllMacros.count
         vList = vList & Application.CurrentProject.AllMacros(i - 1).Name & ";"
     Next i
  End If

Regards...

 

[MichaelRed]

MSysObjects does not appear to save explicit references to Macros.

You can do an 'auto-convert' of macros to code, and each procedure will then be listed in a seperate module. In any 'professional system', this would be recommended, as macros are not able to have error trapping, and thus are not considered 'safe' for professional / production applications.

As for getting the " ... what exactlly do they do ...", I do not know of any way to approach this programmatically. it is a process of reqding through each to understand the commands and arguments as well as their implementation. Of course the same can be said about the individual procedures in the modules so, other than the possablity that the programmer(s) commented the procedures with the information in a format which suits your purpose, the only difference is your familiarity with the language 'style', and the capability to programatically to at least retrieve each 'declaration' line of the modules so you can at least start with the number of individual calls (procedures) you have to review. Another aspect of of the Mouules and Macros which bear some attention is that both may include dead (un-used) sections which are simply left from some earlier version. Depending on your exact needs / motivation these may not be germane to the exercise.





MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[wtina]

Thank you both, again.

It looks like the project will wait for a month or two due to bosses, so I won't need this now, but it looks very helpful(-:

Thanks again!

 

[wtina]

Hi, me again!

I need to see macro properties somehow, is there any other way next to openning it and check it in design view? I need to see whether it contains some kind of trigger that gets external data (from tables).

Thanks!

 

[MichaelRed]

Since I (at least) am firmly in the "Just Say No -to MACROS- camp), the best I can offer is to 'do the right thing', which is to convert the pesky little buggfers to code and review them through that mechanisim. It will still be quite a chore, as there are obviously MANY approaches to the use of external informaion, but the modules and procedures at least provide SOME manner of progromatic analysis.

Then, since you do not lay claim to programatic skills, this may be more of a task than you want to undertaks. The conversion of Macro to code is easily accomplished, as there is a menu item somewhere to do this. Programatically searching the entire set of modules for any of the numerous possabilities for referencing 'external data' is -perhaps- not lightly approached even with nodest programming skills. While I heartliy recommend the conversion of macors to code, the search (for what ever hte basic issue is) may be more readily undertaken through other venues, maot / many of which might be facillitated by the conversion - but that would also depend on some (perhaps even a lot) of additional detail / description of the basic problem you are confronting.





MichaelRed
m.red@att.net

Searching for employment in all the wrong places