Access Schema

Ray Turner
Ray Turner used Ask the Experts™
on
Is there a way to retrieve a database schema using the AccessDataSource and Visual Basic?

I only want to retrieve the table names.

Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
you can use the mSysObjects table, which is a hidden system table.

SELECT [Name] as TableName , [Database], [Type] FROM mSysObjects
WHERE [Type] = 6
OR [Database] is not NULL

I think there is another type that is involved here besides 6, but don't remember what it is right off the top of my head.  6 will get you built-in tables, if you have linked tables, you might want to check what the type is associated with them, which is what the 2nd criteria above will do for you.
Owner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010
Commented:
Actually, it should be

SELECT [Name] as TableName , [Database], [Type] FROM mSysObjects
 WHERE [Type] IN (4, 6)
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
If you use ADO with the JET OLEDB provider, you can use the DBSCHEMA_TABLES rowset.  

There's also getoledbschematable:

https://support.microsoft.com/en-us/help/309488/how-to-retrieve-schema-information-by-using-getoledbschematable-and-visual-basic-.net

or you can set a reference (if you did mean VB and not  VB.Net)  to the DAO lib and use the tabledef's collection.

But if your already reading the DB, then you can use's Dale's back door method as well.

Jim.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

John TsioumprisSoftware & Systems Engineer
Commented:
This is for .NET but it includes queries....
Imports System.Data.OleDb

Module Module1
    Sub Main()
        Dim fileName As String
        fileName = "PathOfYourDatabase"
        Dim connString As String = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};User Id=Admin;Password=", fileName)

        ' Opening the Access connection
        Using conn As New OleDbConnection(connString)
            conn.Open()
            Dim dt As DataTable = conn.GetSchema("Tables")
            Dim tablesName As List(Of String) = dt.AsEnumerable().[Select](Function(dr) dr.Field(Of String)("TABLE_NAME")).Where(Function(dr) Not dr.StartsWith("MSys")).ToList()
        End Using
    End Sub
End Module

Open in new window

crystal (strive4peace) - Microsoft MVP, AccessRemote Training and Programming
Top Expert 2015
Commented:
As Dale mentioned, you can use MSysObjects.  Linked tables might be Type  4 or 6. Resident  tables are 1.  Here is an SQL statement you can use to make a query in the database* you want to document to show information from MSysObjects.
   SELECT GetObjectType([Type]) AS ObjectType, MSysObjects.Type, MSysObjects.Name, MSysObjects.[Type] AS Type_
   FROM MSysObjects
   WHERE (((Left([Name],1))<>"~") AND ((Left([Name],4))<>"MSys"))
   ORDER BY GetObjectType([Type]), MSysObjects.Name;

Open in new window

 
here is the Helper function:
Function GetObjectType(pType) As String
's4p 
   Select Case pType
   Case 1: GetObjectType = "Table"
   Case 3: GetObjectType = "Container"
   Case 4: GetObjectType = "Table odbc"
   Case 6: GetObjectType = "Table linked"
   Case 5: GetObjectType = "Query"
   Case 8: GetObjectType = "Relationship" '100306
   Case -32768: GetObjectType = "Form"
   Case -32764: GetObjectType = "Report"
   Case -32766: GetObjectType = "Macro"
   Case -32761: GetObjectType = "Module"
   Case Else: GetObjectType = pType
   End Select
End Function

Open in new window

You can, of course, filter the query for just tables. WHERE [Type] IN (1, 4, 6)

* if you are analyzing this externally, after the FROM clause, add: IN 'path\databasename.accdb' (and you may want to eliminate GetObjectType and just show Type). You also may want to filter for MSysObjects.Flags=0

If you decide you want a more in-depth analysis, here is a free tool that is invaluable to all who use Access:
https://analyzer.codeplex.com/
Ray TurnerSenior Consultant

Author

Commented:
Thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial