How to check if an Access column exists

I need to find out how to check if an Access column exists.

If an Access column does not exist my VB app. must add it.
jflAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
cerikConnect With a Mentor Commented:
try this, just stick it in a sub or function

Dim ws As Workspace
Dim db As Database
Dim FieldX As Field
Dim Searchstr As String
Dim index As Integer
Dim tdef As TableDef
'connect up to database
Set ws = Workspaces(0)
Set db = ws.OpenDatabase("D:/mydb") 'or whereever yours is
'get a tabledef to work with
Set tdef = db.TableDefs!myTable
'set column search name
Searchstr = "unknown"
index = -1
'scan thru column(fields) for match
For Each FieldX In tdef.Fields
    If FieldX.Name = Searchstr Then index = FieldX.OrdinalPosition
Next
'if no ordinal position found then add
If index = -1 Then
    'check that you can update
    If tdef.Updatable Then
        tdef.Fields.Append tdef.CreateField(Searchstr, dbText, 50)
    'you can add as different fields and field size but both are optional
    End If
End If


0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.