Dev Tools: Fill immediate window with Table columns

[jimatqsi]

To celebrate my 800th Bytes post I'm posting my first article. I plan to post a number of articles in a "Dev Tools" series; those being articles containing tools for developers to use as opposed to simply transferring knowledge about how to do something. The first tool is the subroutine "GetColumnNames."

Intellisense is nice but not always available and limited to one field at a time. I often find myself not quite sure about the spelling of a particular field name - is it OrderQty or QtyOrdered, CustPO or CustPONO, stuff like that used to drive me mad. Finally, some time ago, I wrote a routine to list a table's column names in the immediate window. Over time, I expanded it to return the information in various formats, depending on the need.

As of now, this routine will spit out to the immediate window one of these formats:
1. the field or column name only
2. TableName.FieldName.FieldType
3. rs1!FieldName=X
4. X=rs1!FieldName
In formats 3 and 4, X is literal; I paste the returned code into the module I'm working on and replace each X with the appropriate field name or value. Saves a lot of typing when I have to build, copy or update records in VBA. Likewise, the "rs1" may or may not have to be changed to whatever rs object name I'm using in the given instance.

Finally, the Table and Field name parameters are optional and represent search strings to match.
GetColumnNames(1,"PREmployee","W2") will return all column names beginning with "W2" from table "PREmployee".
GetColumnNames(2,"PR") will return every Table Name, the Field Name and the Field Type from every table beginning with "PR"
GetColumnNames(3,"PREmployee","YTD") might return something like this:
rs1!YTDWages=X
rs1!YTDFedWH=X
rs1!YTDNet=X ... and so on for all columns beginning YTD

I have used Enmerations so that the user doesn't have to remember that 1=gcnSimple,2=gcnComplex,3=gcnCopyTo and 4=gcnCopyFrom.

Enough babbling, on with the code.

Option Compare Database
Option Explicit
Public Enum gcnTypes
    gcnSimple = 1
    gcnComplex = 2
    gcnCopyTo = 3
    gcncopyfrom = 4
 
End Enum
 
'---------------------------------------------------------------------------------------
' Procedure : GetColumnNames
' Author    : Jim
' Date      : 4/28/2014
' Purpose   : Returns Table Field names in optional formats to the immediate window
'             Optional paramerters allow selective return of all tables and fields or wildcard matches
'             Return formats are
'               Field Name only (gcnSimple)
'               TableName.FieldName.Type (gcnComplex)
'               rs1!FieldName=x  (gcnCopyTo)
'               x=rs1!FieldName  (gcnCopyFrom)
'---------------------------------------------------------------------------------------
'
Public Sub GetColumnNames(ReplyType As gcnTypes, Optional TableName_str As String, Optional FieldPrefix_str As String)
' reply types are 1=simple (field names only)
'                 2=complex (table name, field name, field type)
'                 3=Move to
'                 4=Move from
'
' returns data in the immediate window
    Dim tbl As DAO.TableDef
    Dim fld As DAO.Field
    Dim fldTypes(23) As String
    Dim fldTyp As Integer
    Dim fldDesc As String
 
   On Error GoTo GetColumnNames_Error
 
    fldTypes(1) = "Boolean"
    fldTypes(2) = "Byte"
    fldTypes(3) = "Integer"
    fldTypes(4) = "Long"
    fldTypes(5) = "Currency"
    fldTypes(6) = "Single"
    fldTypes(7) = "Double"
    fldTypes(8) = "Date"
    fldTypes(9) = "Binary"
    fldTypes(10) = "Text"
    fldTypes(11) = "Long Binary"
    fldTypes(12) = "Memo"
    fldTypes(13) = "Attachment" '101
    fldTypes(14) = "Complex Byte" '102
    fldTypes(15) = "Complex Integer"
    fldTypes(16) = "Complex Long"
    fldTypes(17) = "Complex Single"
    fldTypes(18) = "Complex Double"
    fldTypes(19) = "Complex GUID"
    fldTypes(20) = "Complex Decimal"
    fldTypes(21) = "Complex Text"  ' 109
    fldTypes(22) = "Other"
 
    ' Print the header.
   On Error GoTo GetColumnNames_Error
 
    ' Loop through all the table definitions.
    For Each tbl In CurrentDb.TableDefs
        If Len(Nz(TableName_str)) = 0 Or (Left(tbl.Name, Len(TableName_str)) = TableName_str) Then
            For Each fld In tbl.Fields
                fldTyp = fld.Type
                If fldTyp > 100 And fldTyp <= 109 Then
                    fldTyp = fldTyp - 88 ' 101 becomes 13
                End If
                If fldTyp > 0 And fldTyp <= 22 Then
                    fldDesc = fldTypes(fldTyp)
                Else
                    fldDesc = fld.Type & " Other"
                End If
 
                        'only include fields matching the name requested
                If Len(Nz(FieldPrefix_str)) = 0 Or (Left(fld.Name, Len(FieldPrefix_str)) = FieldPrefix_str) Then
                     Select Case ReplyType
                        Case gcnSimple
                                    Debug.Print fld.Name
                        Case gcnComplex
                                 Debug.Print tbl.Name & "." & fld.Name & "." & fldDesc
                        Case gcnCopyTo
                                 Debug.Print "rs1!" & fld.Name & "= x"
                        Case gcncopyfrom
                                Debug.Print "x=rs1!" & fld.Name
                     End Select
                End If
                Next fld
            End If
 
    Next tbl
 
   On Error GoTo 0
   Exit Sub
 
GetColumnNames_Error:
 
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure GetColumnNames of Module PublicCode_vb"
    Resume Next
 
   On Error GoTo 0
   Exit Sub
 
End Sub
 

Paste the code into a public module and just call it from the immediate window when the need arises. Feel free to change the name to GCN or some other short name.

Jim

[Seth Schrock]

I can't wait to try this. Normally when I'm developing databases, I'm running a system with dual monitors plus I will have printed out (on paper) a copy of the relationships window so that I have the tables names, but I don't always have these features available to me. Plus the relationships window doesn't give data types. Does it just give the generic data type (like Number) or is it specific (integer, long integer, decimal, etc.)?

[jimatqsi]

Seth,
You can see the complete list of data types described in the array fldTypes. I'm wondering if I need to look at expanding the list for later versions of Access.

Jim

[Seth Schrock]

I added a bit of functionality to your code to make it a little easier to read so I thought that I would pass it on.

First I added another "view" to your code that just displayed the field name and the data type. I figure since I'm having to tell your sub which table I'm looking for, there isn't much point in having it display it again for each field. Also, with just the period separating the different columns it was a little hard to read in the immediate window. So I added a private function that would add spaces to the end of the field name so that the data types would then line up.

Private Function AddPadding(Text As String, PadTo As Integer)
 
Text = Text & Space(PadTo - Len(Text))
 
AddPadding = Text
 
End Function

I then use it like the following

Debug.Print AddPadding(fld.Name, 50) & AddPadding(fldDesc, 20)
 
Produces:
 AcctTypeID_pk                                    Long                
 AcctType                                         Text                
 TicketTypeID_fk                                  Long     

I'm also a little confused as to what gcnCopyTo and gcnCopyFrom are for. What did you have in mind when you created those?

[jimatqsi]

Eliminating the table name when there's only one table involved seems like a good idea. What I was trying to say in paragraph 3 is that the gcnCopyto/From are for automated code generation rather than reference/documentation. No matter how many fields are in a table I can get all the basic code generated for a few very common situations. So I don't have to type all that rs1!fieldname= again and again, this just prepares that code for me.

I would make a slight modification to your common length fld.Name display. I would use (longest fld.Name +1) rather than a fixed 50 length.

Thanks,

Jim

[Seth Schrock]

I had thought of that, but that would mean looping through the table fields once to find the longest one and then loop through again to do the Debug.Print and I don't think that I have ever had a field name over 30 characters, so I choose to use the fixed length.

I hadn't paid attention to the fact that the table name was optional. To account for this, I have now added the following code at the top of the table loop, inside the If-Then statement.

Debug.Print "---------------------------------------"
            Debug.Print "Table Name: " & tbl.Name
            Debug.Print "---------------------------------------"

This way the table name is only printed once.

I really like your tool. I can't wait to get another project started where I can use it.

[jimatqsi]

One little change that should be made because many of us deal with designs that are less than perfect. In the Debug statements for gcnCopyTo and gcnCopyFrom, change references to fld.Name to

"[" & fld.Name & "]" 

Because you'll run into some field names with spaces in them.

Jim