• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 589
  • Last Modified:

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.
2 Solutions
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
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 ?

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.


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 !


Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now