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

 
Closed Thread
 
Thread Tools Rate Thread Display Modes
Old 11-05-2018, 09:21 AM   #1
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,308
Thanks: 115
Thanked 3,095 Times in 2,813 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
View 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, 968 views)
File Type: png VBAReference.PNG (12.3 KB, 927 views)
File Type: png TableListForm.PNG (67.8 KB, 1023 views)
Attached Files
File Type: zip ListTables_v1.6.zip (69.5 KB, 273 views)

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

Colin (Mendip Data Systems)
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.


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.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Common sense and a sense of humour are the same thing, moving at different speeds. (Clive James - RIP)

Last edited by isladogs; 11-27-2019 at 05:36 PM.
isladogs is offline  
The Following User Says Thank You to isladogs For This Useful Post:
eizik_g (11-09-2018)
Old 11-22-2019, 10:42 AM   #2
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,308
Thanks: 115
Thanked 3,095 Times in 2,813 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: View Tables in External Databases

Attached is an update to version 1.7
As before, the app allows you to view a list of all local/linked/hidden & system tables in any external Access app.
Once again I have deliberately excluded deep hidden system tables from that list

I was contacted by UA member payfast8898 yesterday who informed me thattables with attachment fields were being omitted from the list
Further investigation revealed that was also true for tables containing MVFs and column history in memo fields.
These tables are unusual in that each is linked to a deep hidden system table 'behind the scenes' and all have Flags value 262144 in MSysObjects (or 262152 if hidden in the nav pane)

I've now updated this utility to include those types of table as well
This should work in all versions from A2007 onwards

I have tested this version on linked Access/SQL/Excel/csv/text/XML tables
The full list of Flags/Type values covered in this app is currently:



I would be grateful to any feedback on its use together with information about any other 'missing' Flags values you may have with any of your linked tables.
In particular please supply any additional Flags / Type values needed if you have access to any linked Sharepoint / Data Services / HTML / Outlook / DBase / Azure tables

I also intend to release an updated version of this utility which will also allow you to view the contents of any external tables

NOTE:
1. I also strongly recommend an excellent external tables list/view utility created using .net by Albert Kallal and available here

2. As this thread is closed, please provide feedback by PM or you can email me
Attached Images
File Type: png TableTypes.PNG (37.6 KB, 143 views)
Attached Files
File Type: zip ListTables_v1.7.zip (74.3 KB, 22 views)
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Colin (Mendip Data Systems)
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.


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.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Common sense and a sense of humour are the same thing, moving at different speeds. (Clive James - RIP)

Last edited by isladogs; 11-27-2019 at 05:36 PM.
isladogs is offline  
Old 11-27-2019, 05:17 PM   #3
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,308
Thanks: 115
Thanked 3,095 Times in 2,813 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: View Tables in External Databases

A further update to version 2.3

This includes the following changes:
a) data related to complex local tables e.g. those used with attachment fields, multi value fields & memo fields with column history
b) data for linked tables from Outlook, HTML and DBase
c) a new feature allowing you to view the contents of external tables (except for complex tables) within the utility.
This is done without the need to link any of the external tables
Instead it uses a local query definition that for safety reasons has been made read only.
However it is very easy to modify this to allow the external tables to be directly edited from the utility if you wish!
See the comments in the code for frmTableViewer. TAKE CARE if you do so

Once again, I have deliberately excluded deep hidden tables from this utility

Feedback on this utility would be much appreciated. As the thread is closed, please respond by PM or email

NOTE:
I still need info for linked tables from Sharepoint and Data Services. If any of you use either of these types of linked table, I would be very grateful for details of their Flags & Type values in the system table MSysObjects
Attached Images
File Type: jpg MainForm.jpg (56.1 KB, 6 views)
File Type: jpg TableViewer.jpg (87.6 KB, 7 views)
File Type: png TableTypes.PNG (78.3 KB, 6 views)
Attached Files
File Type: zip ViewExternalTables_v2.3.zip (362.4 KB, 15 views)

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

Colin (Mendip Data Systems)
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.


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.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Common sense and a sense of humour are the same thing, moving at different speeds. (Clive James - RIP)

Last edited by isladogs; 11-28-2019 at 12:18 AM. Reason: Spelling
isladogs is offline  
Closed Thread

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
[SOLVED] 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:44 PM.


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

Featured Forum post


Sponsored Links


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