Solved Curious about obtaining table structures (1 Viewer)

Alc

Registered User.
Local time
Today, 00:46
Joined
Mar 23, 2007
Messages
2,371
I'm testing using the following to print field names and data types of the Access tables in my db.
Code:
    For Each Tdf In RemoteDb.TableDefs
        intNumberofFields = Tdf.Fields.Count
        For intCounter = 0 To intNumberofFields - 1
            strFieldName = CurrentDb.TableDefs(Tdf.Name).Fields(intCounter).Name
            intFieldType = CurrentDb.TableDefs(Tdf.Name).Fields(intCounter).Type
         
            Select Case intFieldType
              Case 2    'Byte
                strTypeName = "Byte"
              Case 3    'Integer
                strTypeName = "Integer"
              Case 4    'Long
                strTypeName = "Long"
              Case 6    'Single
                strTypeName = "Single"
              Case 7    'Double
                strTypeName = "Double"
              Case Else 'Not a Number
                strTypeName = "N/A"
            End Select

debug.print tdf.name & " - " & strFieldName & " - " & strTypeName
   Next intcounter
It works fine for Access tables, local and linked.

I'm curious if it's possible to do something similar with the DB2 tables we connect to, via ODBC?
There are a lot of them and some have a large number of fields.

The above code in red doesn't work but, if I could find something along the same lines, I'd use an 'If' statement, based on the name of the remote db being in the connection string.

Edit:
Every time I save, it strips off the colour. The lines that should be in red are

strFieldName = CurrentDb.TableDefs(Tdf.Name).Fields(intCounter).Name
intFieldType = CurrentDb.TableDefs(Tdf.Name).Fields(intCounter).Type
 
Last edited:

Alc

Registered User.
Local time
Today, 00:46
Joined
Mar 23, 2007
Messages
2,371
Care to provide a little more detail here?
Sorry the colour won't save, so I added the lines underneath.

strFieldName = CurrentDb.TableDefs(Tdf.Name).Fields(intCounter).Name
intFieldType = CurrentDb.TableDefs(Tdf.Name).Fields(intCounter).Type
 

Isaac

Lifelong Learner
Local time
Yesterday, 21:46
Joined
Mar 14, 2017
Messages
5,558
Sorry the colour won't save, so I added the lines underneath.

strFieldName = CurrentDb.TableDefs(Tdf.Name).Fields(intCounter).Name
intFieldType = CurrentDb.TableDefs(Tdf.Name).Fields(intCounter).Type
What does "doesn't work" mean? What is the error? Is it a Compile or Runtime error?
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 21:46
Joined
Oct 29, 2018
Messages
16,216
Sorry the colour won't save, so I added the lines underneath.

strFieldName = CurrentDb.TableDefs(Tdf.Name).Fields(intCounter).Name
intFieldType = CurrentDb.TableDefs(Tdf.Name).Fields(intCounter).Type
What error message were you getting?

Edit: Oops, too slow...
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 00:46
Joined
Feb 19, 2002
Messages
32,908
Here's some code I use to document databases. It is taken out of context so you will have to modify it a lit.e. Create a table based on the AddNew code. At the beginning of the procedure, it gets the name of the database file to document. I do this because the documentation code runs in db1 and documents whatever db it is pointed to. Change the code to reference the current db if you want to embed the code.
Sub Create_tblTableFields()

''''Reference Field object definition https://msdn.microsoft.com/en-us/library/office/ff193203.aspx

Dim db As dao.Database
Dim tblLoop As dao.TableDef
Dim fldLoop As dao.Field
Dim TD1 As dao.TableDef
Dim QD1 As dao.QueryDef
Dim TempSet1 As dao.Recordset
Dim strDatabase As String
Dim ThisDB As dao.Database
Dim CountTables As Integer

''''On Error GoTo Create_tblTableFields_Error

On Error GoTo Err_Create_tblTableFields
'strDatabase = "C:\hartman\LinkDB2.mdb"
strDatabase = Forms!frmPrintDoc!txtDBName

CountTables = 0
Set ThisDB = CurrentDb()
If strDatabase = "" Then
Set db = CurrentDb()
Else
Set db = DBEngine.Workspaces(0).OpenDatabase(strDatabase)
End If

db.Containers.Refresh

''' Set QD1 = ThisDB.QueryDefs!QdeltblTableFields
''' QD1.Execute
Set TD1 = ThisDB.TableDefs!tblTableFields
Set TempSet1 = TD1.OpenRecordset

' Loop through TableDefs collection.
For Each tblLoop In db.TableDefs
' Enumerate Fields collection of each
' TableDef object.
CountTables = CountTables + 1
Forms!frmPrintDoc!txtTableCount = CountTables
Forms!frmPrintDoc!txtTableName = tblLoop.Name
Forms!frmPrintDoc.Repaint

If Left(tblLoop.Name, 4) = "MSys" Or Left(tblLoop.Name, 2) = "xx" Or Left(tblLoop.Name, 2) = "zz" Or Left(tblLoop.Name, 1) = "~" Then
Else
For Each fldLoop In tblLoop.Fields
TempSet1.AddNew
TempSet1!TableName = tblLoop.Name
TempSet1!FieldName = fldLoop.Name
TempSet1!OrdinalPosition = fldLoop.OrdinalPosition
TempSet1!AllowZeroLength = fldLoop.AllowZeroLength
TempSet1!DefaultValue = fldLoop.DefaultValue
TempSet1!Size = fldLoop.Size
TempSet1!Required = fldLoop.Required
TempSet1!Type = fldLoop.Type
TempSet1!ValidationRule = fldLoop.ValidationRule
TempSet1!Attributes = fldLoop.Attributes
On Error Resume Next ' the following property is only available when it is not null
TempSet1!Description = fldLoop.Properties("Description")
TempSet1!FieldType = GetType(fldLoop.Type)
TempSet1!Caption = fldLoop.Properties("Caption")
If fldLoop.Attributes And dbAutoIncrField Then 'performs bitwise operation
TempSet1!AutoNum = True
TempSet1!Required = True
Else
TempSet1!AutoNum = False
End If
TempSet1.Update
Next fldLoop
End If
Next tblLoop

Exit_Create_tblTableFields:
db.Close
Exit Sub

Err_Create_tblTableFields:
Select Case Err.Number
Case 3043, 3055
MsgBox "Please select a valid database. Error #" & Err.Number, vbOKOnly
Case 91 ' db was not opened so it cannot be closed.
Exit Sub
Case Else
MsgBox Err.Number & " (" & Err.Description & ") in procedure Create_tblTableFields of Module DocumentCollections"
End Select
Resume Exit_Create_tblTableFields

On Error GoTo 0
Exit Sub

''''Create_tblTableFields_Error:
''''
'''' MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Create_tblTableFields of Module DocumentCollections"
End Sub

Here are all the field type codes I have documented. There may be others.
Code:
TypeCode    TypeDesc    AltTypeDesc    AccessTypeDesc    SQLTypeDesc
1    Boolean    bit    Yes/No    Bit
2    Byte    bit    Byte    TinyInt
3    Integer    numeric(9,0)    Integer    SmallInt
4    Long    numeric(9,0)    Long    Int
5    Currency    money    Currency    Money
6    Single    money    Single    Real
7    Double        Double    Float
8    Date/Time    datetime    Date    DateTime
9    Binary        Binary   
10    Text    varchar    Short Text    varchar()
11    LongBinary        OLE Object    Image
12    Memo        Long Text    varchar(max)
15    GUID        GUID    UNIQUEIDENTIFIER
16    BigInt        BigInt   
17    VarBinary        VarBinary   
18    Char        Char   
19    Numeric        Numeric   
20    Decimal        Decimal    Decimal()
21    Float        Float   
22    Time        Time   
23    TimeStamp        TimeStamp
 

Alc

Registered User.
Local time
Today, 00:46
Joined
Mar 23, 2007
Messages
2,371
What does "doesn't work" mean? What is the error? Is it a Compile or Runtime error?
Error 3265: Item not found in this collection

If I hover the cursor over the variables, I can see that intCounter is at 0, so it's the first field, and Tdf.Name is the name of a table I'm linked to, via ODBC, in a remote db.
 

cheekybuddha

AWF VIP
Local time
Today, 05:46
Joined
Jul 21, 2014
Messages
711
You don't show how you set your RemoteDb object variable.

Can you share?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 23:46
Joined
Feb 28, 2001
Messages
20,674
Have you looked at the Documenter? I believe you could ask it to document any linked table. Here's the point of my question: IF the Documenter can do what you want with a DB2 table linked via ODBC, then there should be a way (with the proper object libraries referenced) for you to do it. If the documenter can't touch it, then I don't know what to tell you. But it's a simple experiment.
 

Alc

Registered User.
Local time
Today, 00:46
Joined
Mar 23, 2007
Messages
2,371
Here's some code I use to document databases. It is taken out of context so you will have to modify it a lit.e. Create a table based on the AddNew code. At the beginning of the procedure, it gets the name of the database file to document. I do this because the documentation code runs in db1 and documents whatever db it is pointed to. Change the code to reference the current db if you want to embed the code.


Here are all the field type codes I have documented. There may be others.
Code:
TypeCode    TypeDesc    AltTypeDesc    AccessTypeDesc    SQLTypeDesc
1    Boolean    bit    Yes/No    Bit
2    Byte    bit    Byte    TinyInt
3    Integer    numeric(9,0)    Integer    SmallInt
4    Long    numeric(9,0)    Long    Int
5    Currency    money    Currency    Money
6    Single    money    Single    Real
7    Double        Double    Float
8    Date/Time    datetime    Date    DateTime
9    Binary        Binary 
10    Text    varchar    Short Text    varchar()
11    LongBinary        OLE Object    Image
12    Memo        Long Text    varchar(max)
15    GUID        GUID    UNIQUEIDENTIFIER
16    BigInt        BigInt 
17    VarBinary        VarBinary 
18    Char        Char 
19    Numeric        Numeric 
20    Decimal        Decimal    Decimal()
21    Float        Float 
22    Time        Time 
23    TimeStamp        TimeStamp
That looks very useful.
Thanks a lot, I'll give it a go.
 

Alc

Registered User.
Local time
Today, 00:46
Joined
Mar 23, 2007
Messages
2,371
Okay, as is often the case, asking the question (and clarifying the problem) got me thinking differently.
That, coupled with Pat's kind code snippet, got me a working version.

The user runs the function, it prompts them to select an Access db, it then populates a table with each Db name (or 'Access', if it's not an ODBC table), the table name, the field name and the data type.

In case it helps anyone else, here it is.
The password part in the error handling takes care of the fact that some of our dbs require a password and some don't.
Code:
Public Function List_Table_Fields()
    Dim Rst As Recordset
    Dim Db As Database
    Dim RemoteDb As Database
    Dim strDB As String
    Dim strMsg As String
    Dim Tdf As TableDef
    Dim oFd As Object
    Dim strFileName As String
    Dim strFilter As String
    Dim strDefaultLocation As String
    Dim strInsertString As String
    Dim intNumberofFields As Integer
    Dim intFieldType As Integer
    strDataType As String
    Dim fld As Field
    Dim intCounter As Integer
    Dim strFieldName As String
     
    DoCmd.SetWarnings False
    
    On Error GoTo Err_Point
    
    strDefaultLocation = "Q:\"

    strFilter = ahtAddFilterItem(strFilter, "ACCDB Files (*.ACCDB)", "*.ACCDB")
    
    strFileName = ahtCommonFileOpenSave(InitialDir:=strDefaultLocation, _
                Filter:=strFilter, OpenFile:=True, _
                DialogTitle:="Select File")
    If strFileName = "" Then
        Exit Function
    End If
    
    Set Db = CurrentDb
    If TableExists("tblTableFieldList") Then
        DoCmd.DeleteObject acTable, "tblTableFieldList"
    End If
    Db.Execute "CREATE TABLE tblTableFieldList " & _
               "(RemoteDbname CHAR, TableName CHAR, FieldName CHAR, DataType CHAR);"

    Db.TableDefs.Refresh

    Set RemoteDb = OpenDatabase(strFileName)
        
ResumePoint:

    For Each Tdf In RemoteDb.TableDefs
        intNumberofFields = Tdf.Fields.Count
        For intCounter = 0 To intNumberofFields - 1
            strFieldName = RemoteDb.TableDefs(Tdf.Name).Fields(intCounter).Name
            intFieldType = RemoteDb.TableDefs(Tdf.Name).Fields(intCounter).Type
                        
            Select Case intFieldType
                Case 1
                strTypeName = "Yes/No"
                
                Case 2
                strTypeName = "Byte"
                
                Case 3
                strTypeName = "Integer"
                
                Case 4
                strTypeName = "Long"
                
                Case 5
                strTypeName = "Currency"
                
                Case 6
                strTypeName = "Single"
                
                Case 7
                strTypeName = "Double"
                
                Case 8
                strTypeName = "Date/Time"
                
                Case 9
                strTypeName = "Binary"
                
                Case 10
                strTypeName = "Text"
                
                Case 11
                strTypeName = "LongBinary"
                
                Case 12
                strTypeName = "Memo"
                
                Case 15
                strTypeName = "GUID"
                
                Case 16
                strTypeName = "BigInt"
                
                Case 17
                strTypeName = "VarBinary"
                
                Case 18
                strTypeName = "Char"
                
                Case 19
                strTypeName = "Numeric"
                
                Case 20
                strTypeName = "Decimal"
                
                Case 21
                strTypeName = "Float"
                
                Case 22
                strTypeName = "Time"
                
                Case 23
                strTypeName = "TimeStamp"

              Case Else
                strTypeName = "N/A"
            End Select
            
            If Tdf.Connect Like "*TRGTPROD*" Then
                strInsertString = "INSERT INTO tblTableFieldList (RemoteDbname, TableName, FieldName, DataType) " & _
                                  "VALUES ('TRGTPROD', '" & Replace(Tdf.Name, "IWH_", "IWH.") & "','" & strFieldName & "','" & strTypeName & "');"
            ElseIf Tdf.Connect Like "*ECORPRD*" Then
                strInsertString = "INSERT INTO tblTableFieldList (RemoteDbname, TableName, FieldName, DataType) " & _
                                  "VALUES ('ECORPRD', '" & Replace(Tdf.Name, "IWH_", "IWH.") & "','" & strFieldName & "','" & strTypeName & "');"
            ElseIf Tdf.Connect Like "*TRGTPRD2*" Then
                strInsertString = "INSERT INTO tblTableFieldList (RemoteDbname, TableName, FieldName, DataType) " & _
                                  "VALUES ('TRGTPRD2', '" & Replace(Tdf.Name, "IWH_", "IWH.") & "','" & strFieldName & "','" & strTypeName & "');"
            ElseIf Tdf.Connect Like "*STG_PROD*" Then
                strInsertString = "INSERT INTO tblTableFieldList (RemoteDbname, TableName, FieldName, DataType) " & _
                                  "VALUES ('STG_PROD', '" & Replace(Tdf.Name, "IWH_", "IWH.") & "','" & strFieldName & "','" & strTypeName & "');"
            Else
                strInsertString = "INSERT INTO tblTableFieldList (RemoteDbname, TableName, FieldName, DataType) " & _
                                  "VALUES ('Access', '" & Tdf.Name & "','" & strFieldName & "','" & strTypeName & "');"
            End If

            Db.Execute strInsertString
        Next intCounter

    Next
    Set RemoteDb = Nothing
    Set Db = Nothing
   
    DoCmd.SetWarnings True
    
    MsgBox "Process Complete"
    
    Exit Function
    
Err_Point:

    If Err.Number = 3031 Then ' Permission denied
        Set RemoteDb = OpenDatabase(strDB, False, False, ";pwd=" & {password removed} & "")
        GoTo ResumePoint
    End If
    Resume Next

End Function
 

cheekybuddha

AWF VIP
Local time
Today, 05:46
Joined
Jul 21, 2014
Messages
711
I posted an unfinished db here that extracts DDL from a chosen database. It has code to extract indexes as well.

It spits out Access DDL, but you can probably tweak it easily enough for DB2. Give it a try to see if you can use some ideas from it.
 

Users who are viewing this thread

Top Bottom