Go Back   Access World Forums > Microsoft Access Reference > Sample Databases

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 11-05-2018, 09:21 AM   #1
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 6,903
Thanks: 92
Thanked 1,697 Times in 1,575 Posts
isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice
List All Tables in External Databases

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:

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

Attached Images
File Type: png MainForm.PNG (26.2 KB, 87 views)
File Type: png VBAReference.PNG (12.3 KB, 89 views)
File Type: png TableListForm.PNG (67.8 KB, 88 views)
Attached Files
File Type: zip ListTables_v1.6.zip (69.5 KB, 27 views)

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Previously known as ridders : Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
The Following User Says Thank You to isladogs For This Useful Post:
eizik_g (11-09-2018)
Reply

Tags
external database , list tables

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
list all databases and their tables vba Kuhn Modules & VBA 5 08-31-2018 05:21 PM
Copy data from multiple access databases with 4 tables into 1 database with 4 tables rmk911 Modules & VBA 2 06-27-2018 02:32 PM
The request from the external databases alexeinahai General 1 03-02-2017 04:52 AM
Reading External Word Documents Into Access List Box robertb Modules & VBA 1 11-06-2002 01:48 PM
combo or list box values from an external file jatfill Modules & VBA 1 11-28-2001 11:00 AM




All times are GMT -8. The time now is 06:56 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World