Mendip Data Systems

Database applications for businesses and schools

LogoTransparent

Example Apps for Businesses, Schools & Personal Use

View Tables in External Databases

Click any image to view a larger version ...

Screenshots

Return to Example Databases Page

 

   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;

 

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

 

In addition, you can view and (optionally) edit the contents of almost all external tables from the utility without actually linking the tables

 

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)

 

The list of external tables and their properties are saved for future use and displayed in another form frmTableViewer

 

Double click a table name to view the contents of the external table. 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.

See the commented code in the Name_DblClick event procedure of the form frmTableViewer.

 

 

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

 

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 in the screenshot on the right

 

 

 

 

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

VBAReference ExtDBTablesMainForm TableTypes ExtDBTableViewer