• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 171
  • Last Modified:

Set standard value in access table with vb code

I alter a table of an existing access 2000 database with the following vb code.

     mConn.Execute "ALTER TABLE tblArt " _
     & "ADD COLUMN Amount CURRENCY;"
     mConn.Close

This works fine but I also want to set a standerd value(default) of 0.00 for the column amount is that possible?

0
Victoria_
Asked:
Victoria_
  • 3
  • 3
1 Solution
 
SethiCommented:
This is the way to do it:
mConn.Execute "ALTER TABLE tblArt " _
    & "ADD COLUMN Amount CURRENCY Default 500.00;"
    mConn.Close
0
 
Victoria_Author Commented:
Dear Sethi,

I am afraid it is not that easy. I get a warning that there is a fault in the expression for the table.

0
 
SethiCommented:
Sorry. Forgot to apply square brackets around the Default statement. Use the following code, I have checked it at my end, its working fine now.
mConn.Execute "ALTER TABLE tblArt " _
   & "ADD COLUMN Amount CURRENCY [Default 500.00];"
   mConn.Close
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Victoria_Author Commented:
Dear Sethi,

I have copied and pasted your code but it still does not work. The column is created but it does not set a standard default value.

I work with vb 6.0 and Access 2000. Can you please check if your deafult value is set after running the code?
0
 
SethiCommented:
You are right. Here goes the code that works:
objConnection.Execute "ALTER TABLE  tblArt ADD COLUMN Amount CURRENCY;"
    objConnection.Execute "ALTER TABLE  tblArt ALTER COLUMN Amount SET DEFAULT 500"

After you have created a column, you can alter is default value.
0
 
Victoria_Author Commented:
Works smoothly, thanks for the effort
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now