list of all tables and items in a database


       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.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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 ?

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.


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
bliincquiAuthor Commented:
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
bliincquiAuthor Commented:
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
Glad we could help, success !

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

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.