Print a list of used tables and populated fields (1 Viewer)

[Pierre Beauchamp]

I have a database that content 90 tables I want to be able to list only the tables and fields use (populate) in a database.

I found this scrip written by RASKEW that do almost want I want, it list all the tables and fields in a database.

RASKEW Code:
 
Sub GetField2Description()
[COLOR=#339966]'**********************************************************[/COLOR]
[COLOR=#339966]'Purpose:   1) Deletes and recreates a table (tblFields)[/COLOR]
[COLOR=#339966]'           2) Queries table MSysObjects to return names of[/COLOR]
[COLOR=#339966]'              all tables in the database[/COLOR]
[COLOR=#339966]'           3) Populates tblFields[/COLOR]
[COLOR=#339966]'Coded by:  raskew[/COLOR]
[COLOR=#339966]'Inputs:    From debug window:[/COLOR]
[COLOR=#339966]'           Call GetField2Description[/COLOR]
[COLOR=#339966]'Output:    See tblFields[/COLOR]
[COLOR=#339966]'**********************************************************[/COLOR]
 
Dim db As Database, td As TableDef
Dim rs As Recordset, rs2 As Recordset
Dim Test As String, NameHold As String
Dim typehold As String, SizeHold As String
Dim fielddescription As String, tName As String
Dim n As Long, i As Long
Dim fld As Field, strSQL As String
n = 0
Set db = CurrentDb
[COLOR=#339966]' Trap for any errors.[/COLOR]
    On Error Resume Next
tName = "tblFields"
 
[COLOR=#339966]'Does table "tblFields" exist?  If true, delete it;[/COLOR]
DoCmd.SetWarnings False
   DoCmd.DeleteObject acTable, "tblFields"
DoCmd.SetWarnings True
[COLOR=#339966]'End If[/COLOR]
[COLOR=#339966]'Create new tblTable[/COLOR]
db.Execute "CREATE TABLE tblFields(Object TEXT (55), FieldName TEXT (55), FieldType TEXT (20), FieldSize Long, FieldAttributes Long, FldDescription TEXT (20));"
 
strSQL = "SELECT MSysObjects.Name, MSysObjects.Type From MsysObjects WHERE"
strSQL = strSQL + "((MSysObjects.Type)=1)"
strSQL = strSQL + "ORDER BY MSysObjects.Name;"
 
Set rs = db.OpenRecordset(strSQL)
If Not rs.BOF Then
[COLOR=#339966] ' Get number of records in recordset[/COLOR]
   rs.MoveLast
   n = rs.RecordCount
   rs.MoveFirst
End If
 
Set rs2 = db.OpenRecordset("tblFields")
 
For i = 0 To n - 1
  fielddescription = " "
  Set td = db.TableDefs(i)
[COLOR=#339966]  'Skip over any MSys objects[/COLOR]
    If Left(rs!Name, 4) <> "MSys" And Left(rs!Name, 1) <> "~" Then
       NameHold = rs!Name
       On Error Resume Next
       For Each fld In td.Fields
          fielddescription = fld.Name
          typehold = FieldType(fld.Type)
          SizeHold = fld.Size
          rs2.AddNew
          rs2!Object = NameHold
          rs2!FieldName = fielddescription
          rs2!FieldType = typehold
          rs2!FieldSize = SizeHold
          rs2!FieldAttributes = fld.Attributes
          rs2!FldDescription = fld.Properties("description")
          rs2.Update
       Next fld
 
       Resume Next
    End If
    rs.MoveNext
Next i
rs.Close
rs2.Close
db.Close
End Sub
 
Function FieldType(intType As Integer) As String
 
Select Case intType
    Case dbBoolean
        FieldType = "dbBoolean"   [COLOR=#339966]'1[/COLOR]
    Case dbByte
        FieldType = "dbByte"      [COLOR=#339966] '2[/COLOR]
    Case dbInteger
        FieldType = "dbInteger"   [COLOR=#339966]'3[/COLOR]
    Case dbLong
        FieldType = "dbLong"      [COLOR=#339966] '4[/COLOR]
    Case dbCurrency
        FieldType = "dbCurrency"  [COLOR=#339966] '5[/COLOR]
    Case dbSingle
        FieldType = "dbSingle"    [COLOR=#339966] '6[/COLOR]
    Case dbDouble
        FieldType = "dbDouble"    [COLOR=#339966] '7[/COLOR]
    Case dbDate
        FieldType = "dbDate"      [COLOR=#339966] '8[/COLOR]
    Case dbBinary
        FieldType = "dbBinary"    [COLOR=#339966] '9[/COLOR]
    Case dbText
        FieldType = "dbText"      [COLOR=#339966] '10[/COLOR]
    Case dbLongBinary
        FieldType = "dbLongBinary"[COLOR=#339966] '11[/COLOR]
    Case dbMemo
        FieldType = "dbMemo"      [COLOR=#339966] '12[/COLOR]
    Case dbGUID
        FieldType = "dbGUID"      [COLOR=#339966] '15[/COLOR]
End Select
 
End Function

Is there a way to modify this script to see only the tables used and only witch fields are populated in them?

In the attached Files you will find an example of what I want to achieve.

Thanks

 

[raskew]

Hi -

Any chance you can post example table(s). I'm lazy and don't want to build this myself, even though your example is quite clear.

Best wishes - Bob (SGM, USA Ret)

 

[Pierre Beauchamp]

As resquested I attach an example of my database I remove a lot of fields out of it.

Hope it will help you View attachment OEM_to_LSAR_ Migration_Tool_tables.mdb