[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Extract the structure of an Access 2000 database

Posted on 2007-10-01
Medium Priority
Last Modified: 2008-01-09
Hello expert,

I need to get the structure of an access database in for example an excel file.
Need to extract the table name with all the column name and the type of the data.

Really need this because doing it by hand will take me ages !
It'es for Access 2000.
Big big thanks.

Question by:Madeso
LVL 14

Assisted Solution

ragoran earned 1000 total points
ID: 19993876
this procedure will loop thru all tables and fields.  Change the "debug.print" statement to whatever you want to do.

Public Sub ExtratDBModel()

   Dim db As DAO.Database
   Dim tb As DAO.TableDef
   Dim fd As DAO.Field
   Set db = CurrentDb
   For Each tb In db.TableDefs
      Debug.Print "Table: " & tb.Name
      For Each fd In tb.Fields
         Debug.Print "   Field: "; fd.Name & "  ";
         Select Case fd.Type
            Case dbBigInt:
               Debug.Print "Big Integer"
            Case dbBinary:
               Debug.Print "Binary"
            Case dbBoolean:
               Debug.Print "Boolean (Y/N)"
            Case dbByte:
               Debug.Print "Byte"
            Case dbChar:
               Debug.Print "Char"
            Case dbCurrency:
               Debug.Print "Currency"
            Case dbDate:
               Debug.Print "Date/Time"
            Case dbDouble
               Debug.Print "Double"
            Case dbSingle
               Debug.Print "Single Float"
            Case dbLong
               Debug.Print "Long"
            Case dbInteger
               Debug.Print "Integer"
            Case dbNumeric
               Debug.Print "Numeric"
            Case dbGUID
               Debug.Print "GUID"
            Case dbLongBinary
               Debug.Print "Long Binary (OLE)"
            Case dbMemo
               Debug.Print "Memo"
            Case 10
               Debug.Print "AutoNumber (long)"
            Case Else
               Debug.Print fd.Type & "?"
         End Select
End Sub
LVL 27

Accepted Solution

MikeToole earned 1000 total points
ID: 19993980
You can use the built-in support from the menu option Tools=>Analyze=>Documenter, or there are commercial tools out there, e.g. FMS Total Access Analyxer.
If you want to roll your own the following code will get you started:

Public Sub DocumentIt()
    Dim db As DAO.Database
    Dim fld as DAO.Field
    Set db = CurrentDb
    Dim tdf As TableDef
    For Each tdf In db.TableDefs
        If Not IsSystemObject(tdf) Then
            Debug.Print tdf.Name
            for each fld in tdf.Fields
                Debug.Print fld.Name, fld.Type
            next fld
        End If
    Next tdf

Author Comment

ID: 20117718
Perfect, thanks experts !

Featured Post


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

872 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