Number of columns in a table

[gemma-the-husky]

Ken

this is a standard function I use to validate an imported table.
simply define the checkfield array with the field names you wish to check.

The code will check for existience of all the columns, and return true or false, depending on whether they are all there.

(i actually have it a bit more complex than this, having a case statement to select from multiple spreadsheet templates, and to set the checkfield array accordingly - so the function can be used to examine different imports.)

Function VALIDSOURCE(txtnewdata As String) As Boolean
Dim fieldmissing As Boolean
Dim checkfield(20) As String
Dim x As Long
Dim maxfield As Long
Dim badcols As String
Dim fld As Field
Dim tdf As TableDef
Dim dbs As Database

    fieldmissing = False
    badcols = ""
    
    maxfield = 3
    checkfield(1) = "Column1"
    checkfield(2) = "Column2"
    checkfield(3) = "Column3"
    
    Set dbs = CurrentDb
    Set tdf = dbs.TableDefs(txtnewdata)
    
    For x = 1 To maxfield
        On Error Resume Next
'        MsgBox ("checking table: " & txtNewData & vbCrLf & _
'            "Field: " & checkfield(x))
        If Len(tdf.Fields("[" & checkfield(x) & "]").Name) = 0 Then
            'MsgBox ("field " & checkfield(x) & " missing")
            fieldmissing = True
            badcols = badcols & checkfield(x) & "     "
        End If
    Next x
            
    If fieldmissing Then
        Call MsgBox("Sorry: Data Validation Failed. " & vbCrLf & vbCrLf & _
                    "Critical Data Fields are missing. The raw data file must contain certain critical fields names. These " & _
                    "names MUST be on row 1 of the spreadsheet. The other columns are not critical and the column order " & _
                    "is not significant. Please check the data file and try again. " & vbCrLf & vbCrLf & _
                    "The missing columns are: " & vbCrLf & _
                    vbTab & badcols, vbCritical, "Data Not Validated")
        
    End If
    
    VALIDSOURCE = fieldmissing = False
            
End Function