How do you add a field to a MS Access table with a default value in VBA

Hi,  

I need to add a currency field (Freight) to my table (tbl_Quotes).  I want to add a default value of 0 in the field so that it is not null.  This needs to be done in VBA code.  I'm somewhat familiar with the Alter Table command but do not know how to add a default value.

Thanks,
Marilync1266Asked:
Who is Participating?
 
Dale FyeConnect With a Mentor Commented:
You can use DAO for this as well.  This function would add the field "Amount" to a table named "tbl_Debits" and set the default value to 0

Public Sub AddField()

    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
   
    Set db = DBEngine.OpenDatabase(FullPathAndNameOfExternalDatabase)
    Set tdf = db.TableDefs("tbl_Debits")
   
    Set fld = tdf.CreateField("Amount", dbCurrency)
    fld.DefaultValue = 0
   
    tdf.Fields.Append fld
   
    set fld = nothing
    set tdf = nothing
    db.Close
    Set db = Nothing
   
End Sub
0
 
peter57rCommented:
You have to execute the sql through an ado connection,  which you can do just with ...

CurrentProject.Connection.Execute  "ALTER TABLE tablename ADD COLUMN columnname SHORT DEFAULT 0"
0
 
Marilync1266Author Commented:
How do I reference a database outside of the run where I'm running the code?
0
 
Marilync1266Author Commented:
Great, this worked!
0
 
Dale FyeCommented:
Glad to help.
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.