Extract the structure of an Access 2000 database

Posted on 2007-10-01
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

    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

    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

    Perfect, thanks experts !

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
    I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
    Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
    With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

    746 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

    15 Experts available now in Live!

    Get 1:1 Help Now