Marilync1266
asked on
Check for existence of a field in a Microsoft Access table
I have a vba program that alters a linked table - a new field is added. Before I create the field, I need to make sure that it doesn't already exist. Here is the code I have to do the alter:
Public Function Alter_Table()
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim strSQL As String
On Error GoTo Alter_Table_Exit
Set db = DBEngine.OpenDatabase("C:\ Quote Templates\Data Files\Quote Data.mdb")
Set tdf = db.TableDefs("tbl_Quotes")
Set fld = tdf.CreateField("Freight_P O", dbBoolean)
fld.DefaultValue = 0
tdf.Fields.Append fld
Set fld = Nothing
Set fld = tdf.CreateField("Freight_A mt", dbCurrency)
fld.DefaultValue = 0
tdf.Fields.Append fld
Set fld = Nothing
Set tdf = Nothing
db.Close
Set db = Nothing
Set db = CurrentDb()
Set tdf = db.TableDefs("tbl_Quotes")
tdf.RefreshLink
Alter_Table_Exit:
DoCmd.OpenQuery ("qupd_Freight_Amt_0")
Set tdf = Nothing
db.Close
Set db = Nothing
Exit Function
End Function
Public Function Alter_Table()
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim strSQL As String
On Error GoTo Alter_Table_Exit
Set db = DBEngine.OpenDatabase("C:\
Set tdf = db.TableDefs("tbl_Quotes")
Set fld = tdf.CreateField("Freight_P
fld.DefaultValue = 0
tdf.Fields.Append fld
Set fld = Nothing
Set fld = tdf.CreateField("Freight_A
fld.DefaultValue = 0
tdf.Fields.Append fld
Set fld = Nothing
Set tdf = Nothing
db.Close
Set db = Nothing
Set db = CurrentDb()
Set tdf = db.TableDefs("tbl_Quotes")
tdf.RefreshLink
Alter_Table_Exit:
DoCmd.OpenQuery ("qupd_Freight_Amt_0")
Set tdf = Nothing
db.Close
Set db = Nothing
Exit Function
End Function
ASKER
If it exists - I don't want to display a message, I need to continue in the code to check the next field - Freight_Amt
ok, how many fields are you going to check?
ASKER
Just 2 - freight_po and freight_amt
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Excellent! Thanks!
Set tdf = db.TableDefs("tbl_Quotes")
'add this codes
For Each fld In tdf.Fields
If fld.Name = "Freight_PO" Then
MsgBox "Field exists"
Exit Function
End If
Next
'end of codes to add
Set fld = tdf.CreateField("Freight_P
fld.DefaultValue = 0
tdf.Fields.Append fld
Set fld = Nothing