How can I get table names from an MS Access Database?

by

To build on Ilya’s answer try the following query:

SELECT MSysObjects.Name AS table_name
FROM MSysObjects
WHERE (((Left([Name],1))<>"~") 
        AND ((Left([Name],4))<>"MSys") 
        AND ((MSysObjects.Type) In (1,4,6)))
order by MSysObjects.Name

(this one works without modification with an MDB)

ACCDB users may need to do something like this

SELECT MSysObjects.Name AS table_name
FROM MSysObjects
WHERE (((Left([Name],1))<>"~") 
        AND ((Left([Name],4))<>"MSys") 
        AND ((MSysObjects.Type) In (1,4,6))
        AND ((MSysObjects.Flags)=0))
order by MSysObjects.Name

As there is an extra table is included that appears to be a system table of some sort.

More Related Contents:

  1. Combine values from related rows into a single concatenated string value
  2. What are the rules governing usage of parenthesis in VBA function calls?
  3. Autonumber value of last inserted row – MS Access / VBA
  4. How to get random record from MS Access database
  5. Operation must use an updatable query. (Error 3073) Microsoft Access
  6. Hand Install of 64-bit MS Access ODBC drivers when 32-bit Office is present
  7. Running Microsoft Access as a Scheduled Task
  8. How to diagnose MS access crashes
  9. “Operation must use an updateable query” error in MS Access
  10. How to restart counting from 1 after erasing table in MS Access?
  11. Equivalent cURL in VBA?
  12. VBScript & Access MDB – 800A0E7A – “Provider cannot be found. It may not be properly installed”
  13. Run-time error ‘3061’. Too few parameters. Expected 1. (Access 2007)
  14. MS Access trigger?
  15. Insert SQL command with Datetime in MS-Access
  16. Is MS Access (JET) suitable for multiuser access?
  17. VBA procedure to import csv file into access
  18. How to select top 10 in Access query?
  19. ConcatRelated() in Access, using two keys
  20. Concatenating multiple rows into single line in MS Access [duplicate]
  21. Transform multiple rows into columns with unique key
  22. connected combo boxes in continuous form – the second combo doesn’t show its value if dependent on first combo how could I fix that
  23. How can I modify a saved Microsoft Access 2007 or 2010 Import Specification?
  24. Null values for variables in VBA
  25. Custom row source for combo box in continuous form in Access
  26. MS Access query returning Chinese characters – possible table corruption?
  27. Using a centralized VBA module in multiple Access databases
  28. How to execute a query in ms-access in VBA code?
  29. How to add a menu item to the default right click context menu
  30. Force all users to disconnect from 2010 Access backend database

Leave a Comment