Link to home
Start Free TrialLog in
Avatar of Marilync1266
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_PO", dbBoolean)
    fld.DefaultValue = 0
    tdf.Fields.Append fld
    Set fld = Nothing
   
    Set fld = tdf.CreateField("Freight_Amt", 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
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Set db = DBEngine.OpenDatabase("C:\Quote Templates\Data Files\Quote Data.mdb")
    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_PO", dbBoolean)
    fld.DefaultValue = 0
    tdf.Fields.Append fld
    Set fld = Nothing
Avatar of Marilync1266
Marilync1266

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?
Just 2 - freight_po and freight_amt
ASKER CERTIFIED SOLUTION
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Excellent!  Thanks!