Quick Question- How do you list all tables in … (Access)

Topic

New Reply

How do you list all tables in a database? Was going to make a query to do this then it dawned on me i dont know how!

Reply | Quote

Replies

nevermind found out how, for future reference…
hidden table named mysysobjects
pulled the name from it
then did a like tbl* for critrea

Reply | Quote

You have got the right table, but your criteria is not entirely correct. You might name them tbl* but not everyone does this.
What you should do is to put a criteria for Type=1 (local table) or 6 (linked table) then name holds the tablename.
Pat

Reply | Quote

Try this.

It will return all tables, whether networked or not, and count all entries also.

SELECT [MsysObjects].[Name], DCount(“*”,[Name]) AS TableCount
FROM MsysObjects
WHERE (((Left$([Name],1))”~”) And ((Left$([Name],4))”Msys”) And (([MsysObjects].[Type])=1 Or ([MsysObjects].[Type])=6))
ORDER BY [MsysObjects].[Name];

Reply | Quote

Thanks guys. I was on a one track mind there, lol. Now gota make me somthing for it to refresh it everytime i run my report.

Reply | Quote

I assume that you created a selection query and used that as record source of a report. The query will be executed every time you open the report, so you’ll always get an up-to-date list.

Reply | Quote

Hi Dave,

How could I use this method to display queries, forms, reports, macros, and modules?

Also, (sounds dumb), but how can I view these hidden objects?

Reply | Quote

Hi Bob,

You can view hidden and/or system objects (this is not the same) by selecting Tools/Options…, view tab. There are separate check boxes for hidden objects and system objects.

Here is a list of types to use in queries that list objects:

Object	Type
Table	1
Linked table	6
Query	5
Form	-32768
Report	-32764
Macro	-32766
Module	-32761
Data Access Page	-32756

Reply | Quote

More trivial nitpicking: Type 6 = ISAM Linked tables, while Type 4 = ODBC Linked Tables. Type 5, Queries, includes SQL statements used as RecordSource or RowSource properties (included in MSysObjects for optimization purposes). These can be identified by Flags field = 3 when querying MSysObjects table for Type = 5, and thus be excluded by specifying that Flags 3 in query criteria. Since System tables are officially undocumented, the usual caveats apply, tho AFAIK the “Type” numbers used to identify database objects work same in A97, A2K, and AXP.

Reply | Quote

Thanks, Mark. The extra condition for queries is especially useful.

(Final nit in my own table: the last entry, Data Access Pages, is not valid for Access 97, only for Access 2000 and 2002/XP, since Access 97 didn’t have those)

Reply | Quote

Thank you, Hans. Is there another function that displays the DESCRIPTION for the tables, queries, etc? I include a description with all of my objects, and think it would be nice to have the ability to print a TOC of my objects and their descriptions.

Bob in Indy

Reply | Quote

I don’t think you can get the descriptions from the MSysObjects table. You will have to loop through all objects in the collection of tables (or queries, …)

You can do this with DAO, or with the AllTables etc. collections available in Access 2000 and higher. If you want to get an idea of how to do this, take a look at the Documenter database from Access moderator MarkLiquorman. You will find a link to his web site in his profile.

But it is going to be a lot of work. Can you use Tools/Analyze/Documentation instead? Study the various options to see if it will work for you.

Reply | Quote

Hans,

Thanks for the reply. I will check out the link, and also the Documentor. My apologies to anyone whom I may have offended. I’ve removed that sentence.

Regards,

Bob in Indy

Reply | Quote

If interested this function use DAO methods to get Description property (if any) for specified database object. This function can be used in query based on MSysObjects table to list Description for each object type specified in query. Example:

Option Compare Database
Option Explicit

Enum DbObject
Table = 1
Query = 2
Form = 3
Report = 4
Macro = 5
Module = 6
End Enum

Public Function daoGetObjDescription(strObjName As String, intObjType As DbObject) As String
On Error GoTo Err_Handler

‘ intObjType: see DbObject Enum for valid arg values

Dim db As DAO.Database
Dim doc As DAO.Document
Dim strContainer As String
Dim strMsg As String

Set db = CurrentDb

Select Case intObjType
Case 1, 2
strContainer = “Tables”
Case 3
strContainer = “Forms”
Case 4
strContainer = “Reports”
Case 5
strContainer = “Scripts”
Case 6
strContainer = “Modules”
End Select

db.Containers(strContainer).Documents.Refresh
Set doc = db.Containers(strContainer).Documents(strObjName)
daoGetObjDescription = doc.Properties(“Description”)

Exit_Sub:
Set db = Nothing
Set doc = Nothing
Exit Function
Err_Handler:
Select Case Err.Number
Case 3265 ‘ Item not found in collection
strMsg = “Invalid object name – object not found in specified database container.”
MsgBox strMsg, vbExclamation, “OBJECT NOT FOUND”
Resume Exit_Sub
Case 3270 ‘ Property not found:
daoGetObjDescription = “”
Resume Exit_Sub
Case Else
strMsg = “Error No ” & Err.Number & “: ” & Err.Description
Beep
MsgBox strMsg, vbExclamation, “GET OBJECT DESCRIPTION ERROR”
Resume Exit_Sub
End Select

End Function

Example of use in query (list all forms in current db):

SELECT MSysObjects.Name AS [Object Name], daoGetObjDescription([Name],3) AS Description, MSysObjects.Type
FROM MSysObjects
WHERE (((MSysObjects.Type)=-32768))
ORDER BY MSysObjects.Name;

NOTE: This will not exactly be lightning-quick in query, as function runs once for each row returned by query. If this is issue you can always create your own table to store object info, but you’d need a reliable method to keep table updated. If no description has been entered for object, function returns a zero-length string. Also note use of user-defined Enum in Declarations section of sample code, this helps avoid errors by providing “Intellisense” list of valid arguments when using function in VBA – see attd screen shot of use in Immediate window. If using this code, ensure reference to DAO library is set.

HTH

Reply | Quote

Hi Mark

How would I change the function to return the description for all object types?

Thanks, John

SELECT MSysObjects.Name AS [Object Name], daoGetObjDescription([Name]) AS Description, MSysObjects.Type
FROM MSysObjects
ORDER BY MSysObjects.Name;

Reply | Quote

Do you want to change the function (which already works for all object types) or the query (which you have already modified)?

Reply | Quote

At this point I think I would want to clone the existing function and make any necessary query adjustments or is there an easier way?

Thanks, John

Reply | Quote

The existing function should work with the modified query you posted. Doesn’t it work the way you want?

Reply | Quote

My query contains all Object Types, tables, queries, forms, reports, ect

When I use

SELECT MSysObjects.Type, MSysObjects.Name, daoGetObjDescription([Name]) AS Description
FROM MSysObjects
ORDER BY MSysObjects.Type, MSysObjects.Name;

I Get Wrong number of arguments used with function in query expression daoGetObjDescription([Name])

daoGetObjDescription([Name]) is looking for the Object Type to return the description

John

Reply | Quote

Try

SELECT MSysObjects.Type, MSysObjects.Name, daoGetObjDescription(MSysObjects.Name, MSysObjects.Type) AS Description
FROM …

Reply | Quote

This works, thanks for your help

John

SELECT MSysObjects.Type, MSysObjects.Name, daoGetObjDescription([Name],
IIf([type]=-32768,3,
IIf([type]=-32766,5,
IIf([type]=-32764,4,
IIf([type]=-32761,6,
IIf([type]=1,1,
IIf([type]=6,1,
IIf([type]=5,2,2)))))))) AS Description
FROM MSysObjects
WHERE (((MSysObjects.Type)=-32768)) 
OR (((MSysObjects.Type)=-32766)) 
OR (((MSysObjects.Type)=-32764)) 
OR (((MSysObjects.Type)=-32761)) 
OR (((MSysObjects.Type)=1)) 
OR (((MSysObjects.Type)=6)) 
OR (((MSysObjects.Type)=5))
ORDER BY MSysObjects.Type, MSysObjects.Name;

Reply | Quote

In short, you examine the properties of the objects. Description is not one of the properties in intellisense, so you address it like .Properties(“description”), where represents an object variable like a tabledef.

Reply | Quote

A lot of methods posted. Personally, I prefer the ADO method (which will work for tables and queries):

Function GetDBTables()
On Error GoTo ErrorHandler
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
If DBConnect(cnn) Then
Set rs = cnn.OpenSchema(adSchemaTables)
Me.lstTables.Clear
rs.MoveFirst
Do Until rs.EOF = True
If Me.optTables Then
If rs.Fields(“TABLE_TYPE”) = “TABLE” Then Me.lstTables.AddItem rs.Fields(“TABLE_NAME”)
Else
If rs.Fields(“TABLE_TYPE”) = “VIEW” Then Me.lstTables.AddItem rs.Fields(“TABLE_NAME”)
End If
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
cnn.Close
Set cnn = Nothing
End If
Exit Function

ErrorHandler:
Err.Clear
End Function

This is right out of an application I wrote that builds classes in ASP from tables/queries. It’s displaying them in a listbox. And there are option buttons to display tables, or to display queries. The function DBConnect is essentially:

Function DBConnect(ByRef cnn as ADODB.Connection)
set cnn=new ADODB.connection
cnn.Provider=”Microsoft.Jet.OLEDB.4.0″
cnn.Open “C:SomePathSomeDatabase.mdb”
Exit function

However, you can connect that ‘cnn’, the connection object, to any database you want (Access, SQL Server, Oracle, etc), and it will list the tables for you.

Reply | Quote

Hey Drew-good to see you posting!

Reply | Quote

Thanks! I’ll try to be more active, just got too busy with work.

Reply | Quote