• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 246
  • Last Modified:

Listing Tables Using ADO or DAO

Hello,

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.
0
Lethal_J
Asked:
Lethal_J
2 Solutions
 
vinnyd79Commented:
0
 
rettiseertCommented:
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
            Next
        End If
    Next
0
 
DoodleCommented:
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")
            RS.MoveNext
        Loop
    RS.Close
End Sub
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now