isladogs
MVP / VIP
- Local time
- Today, 08:44
- Joined
- Jan 14, 2017
- Messages
- 18,717
I wrote the attached utility in response to a question in another forum: How can I get a list of tables from a designated database path?
This is easy enough to do by querying the MSysObjects system table of the selected database.
For example, this will list all local tables (except for hidden / system tables) in an external password protected database:
However, I decided to make this more versatile by allowing users the option of selecting any/all of the following types of table: hidden/system/linked
The table list and properties are saved for future use and displayed in another form
The utility will work successfully:
a) with ACCDB / ACCDE / MDB / MDE files
b) with password protected files where this information is entered on the main form
c) on the current database
d) in 32-bit or 64-bit Access (there are no API declarations)
NOTE:
1. I have deliberately excluded what I call deep hidden tables from this version of the utility.
These are tables that cannot be viewed in the navigation pane and that end users cannot easily view by other methods (nor have any reason to do so)
2. If you import this utility into your own application, you will need to add the VBA reference Microsoft Office XX.0 Object Library where XX is the Office version e.g. 14 for Access 2010.
This isn't included in the references list for all versions of Access so you may need to browse for the file MSO.DLL e.g. in the location shown below
This is easy enough to do by querying the MSysObjects system table of the selected database.
For example, this will list all local tables (except for hidden / system tables) in an external password protected database:
Code:
SELECT '" & FileName.accdb & "' AS DBName, MSysObjects.Name AS TableName
FROM MSysObjects IN '' [MS Access;PWD=xyz;DATABASE=C:\FilePath\FileName.accdb]
WHERE (((MSysObjects.Flags)=0) AND ((MSysObjects.Type)=1) AND ((Left([Name],1))<>'~'))
ORDER BY MSysObjects.Name;However, I decided to make this more versatile by allowing users the option of selecting any/all of the following types of table: hidden/system/linked
The table list and properties are saved for future use and displayed in another form
The utility will work successfully:
a) with ACCDB / ACCDE / MDB / MDE files
b) with password protected files where this information is entered on the main form
c) on the current database
d) in 32-bit or 64-bit Access (there are no API declarations)
NOTE:
1. I have deliberately excluded what I call deep hidden tables from this version of the utility.
These are tables that cannot be viewed in the navigation pane and that end users cannot easily view by other methods (nor have any reason to do so)
2. If you import this utility into your own application, you will need to add the VBA reference Microsoft Office XX.0 Object Library where XX is the Office version e.g. 14 for Access 2010.
This isn't included in the references list for all versions of Access so you may need to browse for the file MSO.DLL e.g. in the location shown below
Attachments
Last edited:
