View All Tables in External Databases (1 Viewer)

Status
Not open for further replies.

isladogs

MVP / VIP
Local time
Today, 22:50
Joined
Jan 14, 2017
Messages
18,186
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

 

Attachments

  • ListTables_v1.6.zip
    69.5 KB · Views: 689
  • MainForm.PNG
    MainForm.PNG
    26.2 KB · Views: 2,792
  • VBAReference.PNG
    VBAReference.PNG
    12.3 KB · Views: 2,701
  • TableListForm.PNG
    TableListForm.PNG
    67.8 KB · Views: 3,140
Last edited:

isladogs

MVP / VIP
Local time
Today, 22:50
Joined
Jan 14, 2017
Messages
18,186
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
 

Attachments

  • TableTypes.PNG
    TableTypes.PNG
    37.6 KB · Views: 1,796
  • ListTables_v1.7.zip
    74.3 KB · Views: 371
Last edited:

isladogs

MVP / VIP
Local time
Today, 22:50
Joined
Jan 14, 2017
Messages
18,186
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
 

Attachments

  • ViewExternalTables_v2.3.zip
    362.4 KB · Views: 487
  • MainForm.jpg
    MainForm.jpg
    56.1 KB · Views: 286
  • TableViewer.jpg
    TableViewer.jpg
    87.6 KB · Views: 250
  • TableTypes.PNG
    TableTypes.PNG
    78.3 KB · Views: 238
Last edited:
Status
Not open for further replies.

Users who are viewing this thread

Top Bottom