I am only trying to add fields that don't exist in the table already from another table list. It works if all the fields are missing but when i already have that field in my table, i get an error. How can i limit the Add column to only the fields that are missing? Any help will be greatly appreciated.
Function AddField()
Dim db As Database
Dim tbl As tableDef
Dim fld As DAO.Field
Dim Str As String
Dim sName As String
Set Criteria = CurrentDb.OpenR ecordset("Crite ria Table")
Set db = CurrentDb
Set tbl = db.TableDefs("C opy_TEstTable_t est")
If Not (Criteria.EOF And Criteria.BOF) Then
Criteria.MoveFi rst
Do Until Criteria.EOF = True
Str = [Criteria].[Linked Table]
Criteria.MoveNe xt
For Each fld In tbl.Fields
If (fld.NAME) = Str Then
Debug.Print fld.NAME; Str
Else
Str2 = "ALTER TABLE Copy_TEstTable_ test ADD COLUMN " & Str & " TEXT(25);"
DoCmd.RunSQL (Str2)
End If
Next fld
Loop
Else
End If
Criteria.Close 'Close the recordset
Set rs = Nothing 'Clean up
End Function
Function AddField()
Dim db As Database
Dim tbl As tableDef
Dim fld As DAO.Field
Dim Str As String
Dim sName As String
Set Criteria = CurrentDb.OpenR ecordset("Crite ria Table")
Set db = CurrentDb
Set tbl = db.TableDefs("C opy_TEstTable_t est")
If Not (Criteria.EOF And Criteria.BOF) Then
Criteria.MoveFi rst
Do Until Criteria.EOF = True
Str = [Criteria].[Linked Table]
Criteria.MoveNe xt
For Each fld In tbl.Fields
If (fld.NAME) = Str Then
Debug.Print fld.NAME; Str
Else
Str2 = "ALTER TABLE Copy_TEstTable_ test ADD COLUMN " & Str & " TEXT(25);"
DoCmd.RunSQL (Str2)
End If
Next fld
Loop
Else
End If
Criteria.Close 'Close the recordset
Set rs = Nothing 'Clean up
End Function