Add columns if not exists

[ysrini]

Hi for a given access table, i want to add few columns if they don't exist.
psedo code:
If col1 not exists then create col1 ...
If col2 not exists then create col2 ...
If col3 not exists then create col3 ...

And i want to run all the above if statements in one query
Thanks
-srinivas

[carlmdobbs]

Hi for a given access table, i want to add few columns if they don't exist.
psedo code:
If col1 not exists then create col1 ...
If col2 not exists then create col2 ...
If col3 not exists then create col3 ...

And i want to run all the above if statements in one query
Thanks
-srinivas

This gives you part of the solution. You can write me for more if needed.
If you wish to know more and I can help you more please write
Sub subAppendField()
Dim tdfTemp as tabledef, strTblname as string

Dim strN as string, varitype, varSize
strTableName = "tblYourtablename"

set tdfTemp = db.tabledefs(strTableName)
strN = “NewField”
If fnFieldNameExistsn (strTableName, strN) = true then GoTo
Vartype = vbLong
With tdfTemp
.Fields.Append .CreateField(strN, _
varType)
End With
End If
End Sub
You can iterate through the table field names like this
Function fnFieldNameExists(strTableName As String, strFieldName As String) As Boolean
Dim tblDef As TableDef
Dim db As Database
Set db = CurrentDb
Dim fld As Field
Set tblDef = db.TableDefs(strTableName)
For Each fld In tblDef.Fields
If fld.Name = strFieldName Then
fnFieldNameExists = True
End If
Next fld
db.close
set db = nothing

End Function
Write me for more info if this isn't what you want.