We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

Set Column Default Value SQL syntax error in Access 2003

dschrishuhn
dschrishuhn asked
on
Medium Priority
372 Views
Last Modified: 2012-05-06
Hello,

What I want to do is add a new column to an existing table and set the default value.  I've been playing around with syntax for a while, maybe you guys can help me.  Thanks in advance

-Omar
Dim db As DAO.Database
Set db = CurrentDb()
 
CurrentDb.Execute "ALTER TABLE tblagedinvoices ADD COLUMN omar LONG"
 
CurrentDb.Execute "ALTER TABLE tblagedinvoices ALTER COLUMN omar SET DEFAULT '0'"

Open in new window

Comment
Watch Question

Commented:
try removing the quotes around the 0
I would use the DAO Tabledef and aff the field there. I don't think th DEFAULT can be set in the ALTER TABLE.
Cheers, Andrew

Author

Commented:
Thanks for the quick response.  I've tried it with and without single quotes and I've also tried #0# with no luck.

One thing I did notice is in the vba code window, when I mouse-over "DEFAULT" it shows DEFAULT = 0.      Not sure why since I haven't even declared it, much less defined it.


Author

Commented:
Hi Andrew,

Could you give an example in code? Thanks- O
To do this in DAO then use the code below
Cheers, Andrew
Dim db As Database
Dim td As TableDef
Dim fld As Field
 
    Set db = CurrentDb
    Set td = db.TableDefs("tblagedinvoices")
    Set fld = td.CreateField("Omar", dbLong)
    fld.DefaultValue = 0
    td.Fields.Append fld
    
    Set td = Nothing

Open in new window

Author

Commented:
Thanks Andew, I just tried your code.
It creates the new field in the table, but doesn't show the default value.  I've also tried with different numbers apart from 0.  The entire column has no values.
It executed without error.

Thank you for your help so far

The code sets the default value. It does not update existing records, for that you will have to run an UPDATE query

UPDATE tblagedinvoices SET omar = 0

Cheers, Andrew

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
Your code executed without error.  I opened up the table and saw that it did create the new column "Omar" as the correct datatype but it didn't populate the default value.  I got on the wagon after ADO, so I   think I'll have to research the DAO .fields.append method.

Regardless, as you suggested, I was able to use an update qry to do what I want.  Originally, I thought I'd be able to do it all in the same statement.

Final Product:

CurrentDb.Execute "ALTER TABLE tblagedinvoices ADD COLUMN omar LONG"
CurrentDb.Execute "UPDATE tblagedinvoices SET omar = 0"
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.