list of all tables and items in a database

Posted on 2004-11-07
Last Modified: 2009-07-29

       I just started working in a new company. They have tons of databases but no structure or standard. I would like to build a tool that will go threw each database and get the list of tables, the items in it and the type. After that I could see all the discreptencies amd fix them. So my question would be if there is functions in vba  that will alow me to get all the information on the structure for a given database.

Thank you for any input on the subject.
Question by:bliincqui
    LVL 44

    Expert Comment

    Click on Tools, Analyzer, Documenter and follow the Wizard.
    LVL 2

    Accepted Solution


    In VBA you could use the TableDefs object to cycle through all the tables, then all the fields and get all the properties, etc, a bit of an example is shown further below.  However, have you considered using the "Documenter" function in Access?  You can use this by clicking on "Tools", "Analyze", then select "Documenter".  Select all the tables you want to analyze then press OK.  It will esentially give you most of the information you after - that may be a way to go before trying to develop something in VBA, which could take some time.

    Hope that helps - if you would like more info on the code below please let me know.



        Dim i As Integer
        Dim j As Integer
        For i = 0 To CurrentDb.TableDefs.Count - 1
            Debug.Print "Table: " & CurrentDb.TableDefs(i).Name
            For j = 0 To CurrentDb.TableDefs(i).Fields.Count - 1
                Debug.Print "Field Name: " & CurrentDb.TableDefs(i).Fields(j).Name
                Debug.Print "Field Type: " & CurrentDb.TableDefs(i).Fields(j).Type

            Next j
        Next i
    LVL 54

    Assisted Solution

    I've created my own function to extract the table data and drop it into a table. This will allow you to perform queries on the tables and fields and you could add the database name to the table to get all into one "master table".

    To let the function work you need four steps:
    1) Create a "dummy" table named "tblTablenico5038", fields don't care as it will be reconstructed from code

    2) Copy/paste the following function in a module

    Function fncCreateTableField()
    'Function to create a "table/field" table
    Dim db As DAO.Database
    Dim td As DAO.TableDefs
    Dim tdx As DAO.TableDef
    Dim rs As DAO.Recordset
    Dim rsC As DAO.Recordset
    Dim fld As DAO.Field

    Dim intI As Integer
    Dim strTable As String
    Dim prp As Property

    On Error GoTo err_CreateTableField

    Set db = CurrentDb
    Set td = db.TableDefs

    ' Create table. First drop existing table
    db.Execute "DROP TABLE tblTableNico5038;"

    db.Execute "CREATE TABLE tblTableNico5038 (TableName TEXT, FieldName TEXT, FieldType TEXT, FieldLength NUMBER, FieldDescription TEXT, DiffValues NUMBER, Values10 Text);"

    Set rs = db.OpenRecordset("tblTableNico5038")

    For Each tdx In td
        strTable = tdx.Name
        If Left(strTable, 4) = "MSys" Or strTable = "tblDbConstantes" Or strTable = "tblTableNico5038" Then
            'system table, no action
            For intI = 0 To tdx.Fields.Count - 1
                rs!TableName = strTable
                rs!FieldName = tdx.Fields(intI).Name
                rs!FieldType = DLookup("[TypeDescription]", "tblDbConstantes", "[TypeValue]=" & tdx.Fields(intI).Type)
                rs!FieldLength = tdx.Fields(intI).Size
                rs!FieldDescription = tdx.Fields(intI).Properties("Description")
                If Left(rs!TableName, 2) = "S_" Then
                   Set rsC = CurrentDb.OpenRecordset("select [" & rs!FieldName & "] from " & rs!TableName & " group by [" & rs!FieldName & "]")
                   If rsC.BOF And rsC.EOF Then
                      rs!DiffValues = 0
                      If IsNull(rsC.Fields(0)) Then
                         rs!DiffValues = rsC.RecordCount - 1
                         rs!DiffValues = rsC.RecordCount
                      End If
                      If rsC.RecordCount < 10 Then
                         rs!Values10 = ""
                         While Not rsC.EOF
                            rs!Values10 = rs!Values10 & rsC.Fields(0) & "; "
                      End If
                   End If
                End If
            Next intI
        End If
    Exit Function

    Select Case Err
    '3270 - "Property not found" error is raised for fields without definition
    Case 3270
        Resume Next
    '3371 - table not found for DROP
    Case 3371
        Resume Next
    Case Else
        MsgBox Err.Number & " " & Err.Description
    End Select
    End Function

    3) Create the table "tblDbConstantes" with the following fields and values:

    TypeValue      TypeConstant      TypeDescription
    1      dbBoolean      Boolean
    2      dbByte      Byte
    3      dbInteger      Integer
    4      dbLong      Long
    5      dbCurrency      Currency
    6      dbSingle      Single
    7      dbDouble      Double
    8      dbDate      Date/Time
    9      dbBinary      Binary
    10      dbText      Text
    11      dbLongBinary      Long Binary (OLE Object)
    12      dbMemo      Memo
    15      dbGUID      GUID
    16      dbBigInt      Big Integer
    17      dbVarBinary      VarBinary
    18      dbChar      Char
    19      dbNumeric      Numeric
    20      dbDecimal      Decimal
    21      dbFloat      Float
    22      dbTime      Time
    23      dbTimeStamp      Time Stamp

    (Best to copy into a text file and to import with the NewTable > Import function and select the text file)

    4) Run the function by positioning the cursor in it and press Run button or F5

    Clear ?

    LVL 1

    Expert Comment


    U can get the field name using the following

    Dim Rec as new ADODB.Recordset


    For i=0 rec.Fields.count-1
       msgbox rec.Field(i).name

    Author Comment

    thanks for the quick responses, I will try your solutions today and will you give back some input hopefully tomorrow.

    Thanks again for the suggestions

    Author Comment

    Hi to all of you,
      the suggested answers worked great special thanks to Soluch and nico5038.                               I am very greatfull for your help . I increase the point value of the question and splitted it between both of you to thank you for your input.

    thanks gaain
    LVL 54

    Expert Comment

    Glad we could help, success !


    Featured Post

    Highfive Gives IT Their Time Back

    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

    Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
    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…
    Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
    What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

    728 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

    16 Experts available now in Live!

    Get 1:1 Help Now