Listing Tables Using ADO or DAO

Posted on 2005-04-19
Last Modified: 2010-05-02

Ive been contracted to write a program to update the data structures and tables of an Access database when the client deploys an updated version of their software.  This will run initially and upgrade the database with the latest fields and tables as required.

However, im a little stumped as to how to read the table names from the MDB file.  I cannot use constants for the table names in this case as the client will be using this program on several other projects as well.

Is there a way to list the tables of an Access database using either ADO or DAO, or is there perhaps another method which I am overlooking?

Thanks for your help.
Question by:Lethal_J
    LVL 28

    Assisted Solution

    LVL 13

    Accepted Solution

    This is another way with DAO:

        Dim db As DataBase
        Dim tbldef As TableDef
        Dim fld As Field
        Set db = OpenDatabase("C:\path\your_db.mdb")
        For Each tbldef In db.TableDefs
            If Left(tbldef.Name, 4) <> "MSys" Then
                Debug.Print tbldef.Name
                For Each fld In tbldef.Fields
                    Debug.Print vbTab + fld.Name
            End If
    LVL 1

    Expert Comment

    The following code will list all the object name from the msysobjects table all you will need to do is create an connection to the db.

    Public Sub listtablenames()
        Dim RS As New ADODB.Recordset
        RS.Open "Select * from msysobjects", CN
            Do Until RS.EOF
                Debug.Print RS("Name")
    End Sub

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Suggested Solutions

    Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
    When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now