Access Schema

Is there a way to retrieve a database schema using the AccessDataSource and Visual Basic?

I only want to retrieve the table names.

Ray TurnerSenior ConsultantAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeOwner, Dev-Soln LLCCommented:
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.
Dale FyeOwner, Dev-Soln LLCCommented:
Actually, it should be

SELECT [Name] as TableName , [Database], [Type] FROM mSysObjects
 WHERE [Type] IN (4, 6)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
If you use ADO with the JET OLEDB provider, you can use the DBSCHEMA_TABLES rowset.  

There's also getoledbschematable:

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.

Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

John TsioumprisSoftware & Systems EngineerCommented:
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)
            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 ProgrammingCommented:
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
   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:
Ray TurnerSenior ConsultantAuthor Commented:
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.