Reference a field by its position

[annsolomon]

Is there a way to grab the name of a field by referencing its position in the record? Kinda like an Excel cell reference.

This question is related to another post (thread705-478283) that I'm still grappling with where the field names of imported data frequently change. Ann

 

[MichaelRed]

Not sure if this is suitable for your use, however -properly implemented- it will populate a table with a FEW of the attributes of a table's fields. Some parts are not necessary for use on a single table, and you would need to set up a table in your db to hold the results, but the REQUIRED changes are minimal:

[TAB]Remove (or comment out) the first two IF Blocks,
[TAB](re tblName) and blnFirstPass

[TAB]Set up the table:
[TAB][TAB]Name = "tblFields"
[TAB][TAB]Field = "tblName", String 25 (or your best guess?)
[TAB][TAB]Field = "fldType", String 25 (or ... )
[TAB][TAB]Field = "fldType", String 25 (or ... )
[TAB][TAB]Field = "fldLen", Integer

If you are not comfortable with this approach, do not attempt it. It would probably cause no harm, however it can (actually is intended to) manipulate the data in the db and should not be used w/o at least a reasonable understanding of the process.

Public Function basFieldList(tblName As String)

    'Michael Red    Documentation Tools     2/10/2003

    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim cmd As ADODB.Command

    Dim fld As Field
    Dim Idx As Integer
    Dim tdf As TableDef
    Dim strSQL As String
    Dim MyFldTyp As String
    Dim Quo As String
    Dim MyType As String

    Quo = Chr(34)

    If (Left(tblName, 3) = "XXX") Then
        GoTo SkipTbl
    End If

    Set cnn = CurrentProject.Connection

    If (blnFirstPass = True) Then
        strSQL = "Delete tblFields.* from tblFields"
        cnn.Execute strSQL
        blnFirstPass = False
    End If

    Set rst = New ADODB.Recordset
    rst.Open tblName, cnn, adOpenDynamic, , adCmdTable
    'rstEmployees.Open "employee", cnn1, , , , adCmdTable

    While Idx < rst.Fields.Count        'Not rst.EOF

        MyType = basFldTyp(rst.Fields(Idx).Type)

        strSQL = &quot;Insert Into tblFields (&quot;
        strSQL = strSQL & &quot;tblName, &quot;
        strSQL = strSQL & &quot;fldName, &quot;
        strSQL = strSQL & &quot;fldType &quot;
        
        If (rst.Fields(Idx).Type = adVarWChar) Then
            strSQL = strSQL & &quot;, &quot;
            strSQL = strSQL & &quot;fldLen &quot;
        End If
        
        strSQL = strSQL & &quot;) &quot;

        strSQL = strSQL & &quot;Values (&quot;
        strSQL = strSQL & Quo & tblName & Quo & &quot;, &quot;
        strSQL = strSQL & Quo & rst.Fields(Idx).Name & Quo & &quot;, &quot;
        strSQL = strSQL & Quo & MyType & Quo
        If (rst.Fields(Idx).Type = adVarWChar) Then
            strSQL = strSQL & &quot;, &quot;
            strSQL = strSQL & rst.Fields(Idx).DefinedSize
        End If
        strSQL = strSQL & &quot;)&quot;

        cnn.Execute strSQL

SkipFld:
        Idx = Idx + 1
    Wend

SkipTbl:

End Function

Also note that there is no error handling here. In context, the error handling is generally accomodated in hte calling process.


MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[annsolomon]

Michael, thanks for the response. I'm not sure my VBA skill level is up to this just yet but I've printed out this thread in the hope that I'll be up to the task before too much longer. Ann

 

[Rick39]

You may be able to modify this to get you started:

Sub PrintFields(tdf As TableDef)
' This procedure prints the names of all fields in the Fields collection
' of a TableDef object.

Dim fld As Field

Debug.Print "Fields in " & tdf.NAME & " table: "
For Each fld In tdf.Fields
Debug.Print fld.NAME
Next fld
Debug.Print
End Sub


Hope this helps

Rick

 

[annsolomon]

Hi Rick. I probably didn't ask the right question. I was trying to figure out a way to manipulate fields programatically without knowing the field names. (I don't ask for much, do I?)

Anyway, this particular project isn't urgent so I've decided to set it aside for a while. Ya know, sometimes when you think too hard on something the brain gets totally muddled.

Thanks to both of you. Ann