Listing Access Table Columns Through VBA?

Hello EE!

I recently got some help with listing tables.

Sub ListTbls()
   
   Dim cnn As ADODB.Connection

   Set cnn = New ADODB.Connection
   
   With cnn
      .CursorLocation = adUseServer
      .ConnectionTimeout = 500
      .Provider = "Microsoft.ACE.OLEDB.12.0"
      .ConnectionString = "Data Source=C:\Database.accdb"
      .Open
      .CommandTimeout = 500
   End With
   
Dim rsSchema As New ADODB.Recordset
Set rsSchema = cnn.OpenSchema(adSchemaTables)

rsSchema.MoveFirst
With rsSchema
While Not .EOF
If .Fields("TABLE_TYPE") = "TABLE" Then
MsgBox  .Fields("TABLE_NAME")
End If
.MoveNext
Wend
End With

   Set cnn = Nothing

End Sub

Open in new window


But how do I go about listing all columns in a table? For example table "Data" and is there any manual or such where i can learn more about using this sort of code?

Thanks in advance!

ThomasFoegeAsked:
Who is Participating?
 
Rory ArchibaldConnect With a Mentor Commented:
I'd still recommend DAO for Access:
    Dim DAOws As DAO.Workspace
    Dim DAOdb As DAO.Database
    Dim tdef As DAO.TableDef
    Dim fld As DAO.Field
    Dim strDBFile As String
   
      strDBFile = "H:\MyDatabase.accdb"
    Set DAOws = DBEngine.Workspaces(0)
    Set DAOdb = DAOws.OpenDatabase(strDBFile, False, True)
    For Each tdef In DAOdb.TableDefs
      If left$(LCase$(tdef.Name), 4) <> "msys" Then
         MsgBox tdef.Name
         For Each fld In tdef.Fields
            MsgBox fld.Name
         Next fld
      End If
   Next tdef
   DAOdb.Close
   DAOws.Close

Open in new window

0
 
ThomasFoegeAuthor Commented:
Cool!

Whats the difference between ADODB and DAO?
0
 
Rory ArchibaldCommented:
Different technologies doing much the same thing. DAO is what Access uses natively, I believe, and seems to me to be easier to work with for Access, as a rule. (especially for parameter queries, for some reason).
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
ThomasFoegeAuthor Commented:
Sub ListTbls()

    Dim DAOws As DAO.Workspace
    Dim DAOdb As DAO.Database
    Dim tdef As DAO.TableDef
    Dim fld As DAO.Field
    Dim strDBFile As String
   
    strDBFile = "C:\Database.accdb" 'ActiveSheet.Range("D4").Value
    Set DAOws = DBEngine.Workspaces(0)
    Set DAOdb = DAOws.OpenDatabase(strDBFile, False, True)
    For Each tdef In DAOdb.TableDefs
      If Left$(LCase$(tdef.Name), 4) <> "msys" Then
         MsgBox tdef.Name
         For Each fld In tdef.Fields
            MsgBox fld.Name
         Next fld
      End If
   Next tdef
   DAOdb.Close
   DAOws.Close
   
End Sub

Open in new window



I tried using your code, i get an error either in line
   
Set DAOdb = DAOws.OpenDatabase(strDBFile, False, True)
    For Each tdef In DAOdb.TableDefs

Open in new window


Runetime error 3343, application-defined or object-defined error

Did i miss something?

Thanks again!
0
 
Rory ArchibaldCommented:
What references do you have set?
0
 
ThomasFoegeAuthor Commented:
Visual Basic for applications
microsoft excel 14 object lib
ole automation
microsoft office 14 object lib
microsoft DAO 3.6 object lib
0
 
Rey Obrero (Capricorn1)Commented:

try this simple version


Dim dbs As DAO.Database
 
Dim tdf As DAO.TableDef, fld As DAO.Field
 
Set dbs = DAO.OpenDatabase("C:\Database.accdb")
    For Each tdf In dbs.TableDefs
        If Left(tdf.Name, 4) <> "msys" Then
            For Each fld In tdf.Fields
            Debug.Print tdf.Name, fld.Name
            Next
 
        End If
    Next

dbs.Close

Open in new window

0
 
Rory ArchibaldCommented:
You need the 'Microsoft Office 12.0 Access database engine objects' reference rather than DAO3.6
0
 
ThomasFoegeAuthor Commented:
Perfect!

Is there any site that has a "beginners guide" to DAO?
0
 
Rory ArchibaldCommented:
I couldn't tell you to be honest. There's a guide to the model here: http://msdn.microsoft.com/en-us/library/ee291983(v=office.12).aspx
and you might find this page useful (though it's VB6 rather than VBA): http://www.vb6.us/tutorials/using-dao-data-access-objects-code-tutorial
0
 
ThomasFoegeAuthor Commented:
Perfect as always!
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.

All Courses

From novice to tech pro — start learning today.