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!
![]() |
Patch reliability is unclear. Unless you have an immediate, pressing need to install a specific patch, don't do it. |
SIGN IN | Not a member? | REGISTER | PLUS MEMBERSHIP |
-
Quick Question- How do you list all tables in … (Access)
Home » Forums » AskWoody support » Productivity software by function » MS Access and database help » Quick Question- How do you list all tables in … (Access)
- This topic has 24 replies, 10 voices, and was last updated 17 years, 3 months ago.
AuthorTopicWSCryptic80
AskWoody LoungerMarch 17, 2003 at 2:43 am #384831Viewing 1 reply threadAuthorReplies-
WSCryptic80
AskWoody Lounger -
WSpatt
AskWoody Lounger -
WSD Willett
AskWoody LoungerMarch 17, 2003 at 7:53 am #661600Try 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]; -
WSCryptic80
AskWoody Lounger -
WSHansV
AskWoody Lounger
-
-
-
WSIndyAries
AskWoody Lounger -
WSHansV
AskWoody LoungerMarch 19, 2003 at 9:20 pm #662380Hi 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 -
WSMarkD
AskWoody LoungerMarch 20, 2003 at 1:07 am #662436More 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.
-
WSHansV
AskWoody Lounger -
WSIndyAries
AskWoody LoungerMarch 20, 2003 at 11:23 am #662567 -
WSHansV
AskWoody LoungerMarch 20, 2003 at 11:26 am #662570I 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.
-
WSIndyAries
AskWoody Lounger -
WSMarkD
AskWoody LoungerMarch 20, 2003 at 1:18 pm #662600If 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 ExplicitEnum DbObject
Table = 1
Query = 2
Form = 3
Report = 4
Macro = 5
Module = 6
End EnumPublic 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 StringSet 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 Selectdb.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 SelectEnd 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
-
WSSupport4John
AskWoody LoungerDecember 16, 2007 at 2:16 pm #1088566 -
WSHansV
AskWoody Lounger -
WSSupport4John
AskWoody Lounger -
WSHansV
AskWoody Lounger -
WSSupport4John
AskWoody LoungerDecember 17, 2007 at 3:22 pm #1088692My 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
-
WSHansV
AskWoody Lounger -
WSSupport4John
AskWoody LoungerDecember 17, 2007 at 4:19 pm #1088710This 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;
-
WScharlotte
AskWoody Lounger
-
-
-
WSDrew
AskWoody LoungerDecember 21, 2007 at 11:55 pm #1089513A 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 FunctionErrorHandler:
Err.Clear
End FunctionThis 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 functionHowever, 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.
-
WSWebGenii
AskWoody Lounger -
WSDrew
AskWoody Lounger
-
Viewing 1 reply thread -

Plus Membership
Donations from Plus members keep this site going. You can identify the people who support AskWoody by the Plus badge on their avatars.
AskWoody Plus members not only get access to all of the contents of this site -- including Susan Bradley's frequently updated Patch Watch listing -- they also receive weekly AskWoody Plus Newsletters (formerly Windows Secrets Newsletter) and AskWoody Plus Alerts, emails when there are important breaking developments.
Get Plus!
Welcome to our unique respite from the madness.
It's easy to post questions about Windows 11, Windows 10, Win8.1, Win7, Surface, Office, or browse through our Forums. Post anonymously or register for greater privileges. Keep it civil, please: Decorous Lounge rules strictly enforced. Questions? Contact Customer Support.
Search Newsletters
Search Forums
View the Forum
Search for Topics
Recent Topics
-
File Naming Conventions (including Folders)
by
Magic66
2 hours, 32 minutes ago -
Windows 11 Insider Preview Build 26100.3613 (24H2) released to Release Preview
by
joep517
5 hours, 59 minutes ago -
Microsoft sends emails to Windows 10 users about EOS
by
Alex5723
1 hour, 41 minutes ago -
Outlook 2024 importing Calendar and Contacts – FAILURE
by
Kathy Stevens
25 minutes ago -
Adding Microsoft Account.
by
DaveBRenn
7 hours, 24 minutes ago -
Windows 11 Insider Preview build 26120.3576 released to DEV and BETA
by
joep517
1 day, 6 hours ago -
Windows 11 Insider Preview Build 22635.5090 (23H2) released to BETA
by
joep517
1 day, 7 hours ago -
Windows 11 won’t boot
by
goducks25
1 day, 21 hours ago -
Choosing virtual machine product for Windows on Mac
by
peterb
21 hours, 9 minutes ago -
Rest in Peace
by
Roy Lasris
2 days, 1 hour ago -
CISA : Install Windows March 2025 Updates until April 1 or shut down PC.
by
Alex5723
2 days ago -
Google proposes users with incompatible Win 11 PCs to migrate to ChromeOS Flex
by
Alex5723
2 days, 2 hours ago -
Drivers for Epson Perfection V600 Photo – scanner
by
Bookman
1 day, 6 hours ago -
Long Time Member
by
jackpet
2 days, 4 hours ago -
Woody Leonhard (1951–2025)
by
Will Fastie
14 minutes ago -
What I learned from Woody Leonhard
by
B. Livingston
1 day, 22 hours ago -
Windows Settings today
by
Simon Bisson
2 days, 12 hours ago -
Mail Merge magic in Microsoft Word
by
Peter Deegan
1 day, 20 hours ago -
Businesses in the crosshairs
by
Susan Bradley
1 day, 2 hours ago -
Double-row taskbar?
by
CWBillow
1 day, 15 hours ago -
Upgrading non-supported HW to Win 11
by
RetiredGeek
4 hours, 11 minutes ago -
Audio locks up after 15 minutes
by
WSArthurR
3 hours, 41 minutes ago -
Copilot app uninstalled
by
Susan Bradley
1 hour, 53 minutes ago -
Strongbox Password Manager Sold to Applause Group – Cost Escalation Imminent
by
Paul T
3 days, 22 hours ago -
SharePoint
by
CBFPD-Chief115
2 days, 20 hours ago -
Google replacing Google Assistant with Gemini AI assistant
by
Alex5723
4 days, 1 hour ago -
You can no longer stop Alexa from sending voice recordings to Amazon
by
Alex5723
4 days, 1 hour ago -
Meeting Woody in person
by
Susan Bradley
16 minutes ago -
Bear with me – getting some options regarding membership
by
Susan Bradley
4 days, 16 hours ago -
Difficulty installing Microsoft Office Home and Business 2024
by
Kathy Stevens
3 days, 21 hours ago
Recent blog posts
Key Links
Want to Advertise in the free newsletter? How about a gift subscription in honor of a birthday? Send an email to sb@askwoody.com to ask how.
Mastodon profile for DefConPatch
Mastodon profile for AskWoody
Home • About • FAQ • Posts & Privacy • Forums • My Account
Register • Free Newsletter • Plus Membership • Gift Certificates • MS-DEFCON Alerts
Copyright ©2004-2025 by AskWoody Tech LLC. All Rights Reserved.