Create Table of List of All Attached Tables, Fields, datatypes & size 1

[jwkolker]

This is what I have:

Mulitple ODBC connected SQL Server tables
Access 2000 front end

This is what I want to do:

Want to write a module that will do the following:

- Read the table and make a new table containing the following:

Field 1 TableName
Field 2 FieldName
Field 3 DataType
Field 4 Size
Field 5 Indexed?

I wish to make a data dictionary that lists all tables and all info about all fields in one single table - If I have to run it for each table and then append the results to one master table that is fine.

Does anyone know of a module, query or utility that will do this for me?

Thank you in advance.




John Kolker
Programmer
jwkolker@comcast.net

 

[rdodge]

In Access 2000 and later, you do have a DB documentation.

What you can do, go into the DB file that has the table structure and data (This should be your BE DB file).

Goto Tools>Analyzer>Documenter

Click on the Table Tab

Click on Select All

Click on Options

Select your options from there.

When Done, click on "OK"

A report will generate which may take a while to do.

If all of your tables are linked in the FE DB file, you can do this from the FE DB file.

If you wanting to do this via code, you can use, you can use the TableDefs collection as the starting point.

Dim tbl as Table, fld as Field
Dim tName as String, fName as String, fType as String, fSize as String
For each tbl in <Database>.TableDefs
tName = tbl.Name
For each fld in tbl.Fields
With fld
fName = .Name
fType = .Type
fSize = CStr(.Size)
End With
Next
Next

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
When the going gets tough, the tough gets going.

 

[jwkolker]

That's a great tool - thank you - but unfortunately I wanted to write the values to a table

TableName
FieldName
Datatype
Size

How do I tweek this code to do that?

Dim tbl as Table, fld as Field
Dim tName as String, fName as String, fType as String, fSize as String
For each tbl in <Database>.TableDefs
    tName = tbl.Name
    For each fld in tbl.Fields
        With fld
            fName = .Name
            fType = .Type
            fSize = CStr(.Size)
        End With
    Next
Next

Regards,



John Kolker
Programmer
jwkolker@comcast.net

 

[rdodge]

Not really sure how familiar you are to using DAO coding, but here's what I have done:

Set <RecordsetVariable> = <DAOWorkSpace>.<DAODatabase>.OpenRecordset(<NameOfTable>,dbOpenTable,dbSeeChanges,dbPessimistic)


If you want to have this to be conforment to data normalization form, you will need 2 tables

Table Table
Field Table

Table Table
-----------
TableID
TableName

Field Table
-----------
FieldID
TableID
FieldName
FieldType
FieldSize

Dim tbl as Table, fld as Field
Dim drsTable as DAO.Recordset, drsField as DAO.Recordset
Dim tName as String, fName as String, fType as String, fSize as String
'Use the above syntax to create both of your recordset at this point.

For each tbl in <Database>.TableDefs
drsTable.AddNew
drsTable.Fields(&quot;TableName&quot.Value = tbl.Name
drsTable.Update
drsTable.MoveLast
For each fld in tbl.Fields
With fld
drsField.AddNew
drsField.Fields(&quot;TableID&quot.Value = drsTable.Fields(&quot;TableID&quot.Value
drsField.Fields(&quot;FieldName&quot.Value = .Name
drsField.Fields(&quot;FieldType&quot.Value = .Type
drsField.Fields(&quot;FieldSize&quot.Value = CLng(.Size)
drsField.Update
End With
Next
Next

Please note that this is not complete code as it does assume you know certain DAO coding.

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
When the going gets tough, the tough gets going.

 

[jwkolker]

hi Rdodge - the DAO code is a no go for me -

the other issue is the Dim tbl as Table, fld as Field

Access does not like them as object types

Perhaps you could toss together a soup to nuts sub for me that I could load as a module in access where we use the table name &quot;JohnsTable&quot; and we create a table called tbldoc & JohnsTable with tname, fname, ftype and fsize as the fields.

I make Johnstable a variable and reply it with which ever table I want to analyze - at the end of the exercise I should end up with multiple tables called tbldoc* and I can append them to create my data dictionary - could you use this code to throw something together that would do that?

I am a little lost at this point since I have never tried this type of effort before within access.

Thank you.

Your original code follows

Dim tbl as Table, fld as Field
Dim tName as String, fName as String, fType as String, fSize as String
For each tbl in <Database>.TableDefs
    tName = tbl.Name
    For each fld in tbl.Fields
        With fld
            fName = .Name
            fType = .Type
            fSize = CStr(.Size)
        End With
    Next
Next

John Kolker
Programmer
jwkolker@comcast.net

 

[rdodge]

Be sure that you have Microsoft DAO 3.6 checkmarked in the list of references found by going to Tools>References.

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
When the going gets tough, the tough gets going.

 

[jwkolker]

Ok it is checkmarked (was checkmarked) Microsoft DAO 3.6

What do I put in the declarations

and what do I put in the sub to acheive what I referenced in my last message?



John Kolker
Programmer
jwkolker@comcast.net

 

[rdodge]

You may want to prequalify the objects with the DAO library name such as:

Dim tbl as DAO.Table, fld as DAO.Field

However, not all of the objects are related to DAO library such as the Long, String, and other Data types. The reason for prequalifying, sometimes, things can get confused like ADO and DAO both have Recordset object. This is not to say you can't use both, but for that very reason, that's where prequalifying your objects avoids these kinds of issues and still allow you to reference multiple libraries. If you want to learn more, such as what data types are in what library, you can use the Object Browser that's included in VBA Editor. The Object Browser will only show the various data types and objects of those libraries that's currently open as shown in the Reference List.

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
When the going gets tough, the tough gets going.