Version 2.3           Last Updated 28 Nov 2019           Approx 0.85 MB

I originally wrote the attached utility in response to a question from Utter Access member payfast8898 in this thread:
      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):

CODE:

SELECT '" & FileName.accdb & "' AS DBName, MSysObjects.Name AS TableName
FROM MSysObjects IN '' [MS Access;PWD=xyz;DATABASE=C:\temp\FileName.accdb]
WHERE (((MSysObjects.Flags)=0) AND ((MSysObjects.Type)=1) AND ((Left([Name],1))<>'~'))
ORDER BY MSysObjects.Name;

The utility will work successfully:
a)   with external 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)

I decided to make this more versatile by allowing users the option of selecting any or all of the following types of table: local/hidden/system/linked


Click the View Table List button to view details of each of the selected table types in another form frmTableViewer


NOTE: The table type information is taken from a reference table tblTableTypes


Double click a table name to view (and optionally edit) the contents of almost all external tables.

This is done (without needing to physically open the external database) by creating a temporary link to the external table(s)


NOTE:
By default, for safety reasons, the table contents are viewed read only using a local query definition.

However, this can easily be altered to allow external tables to be edited if you wish.
To do so, comment out the final line of the Name_DblClick event procedure of the form frmTableViewer.

'open the query def read-only
'disable the acReadOnly argument to allow editing
DoCmd.OpenQuery strQueryDef, , acReadOnly

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. These have been excluded for security reasons

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
      You may need to browse for the file MSO.DLL e.g. in the location shown in the screenshot on the right.



Download

Click to download:             View External Tables v2.3    (zipped)



Feedback

Please use the contact form below to let me know whether you found this article interesting/useful or if you have any questions/comments.

Do let me know if you find any bugs in the application.

Please also consider making a donation towards the costs of maintaining this website. Thank you



Colin Riddington                       Mendip Data Systems                       Last Updated 28 Nov 2019

Return to Example Databases Page

Return to Top