Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Set Column Default Value SQL syntax error in Access 2003

Posted on 2009-02-17
8
Medium Priority
?
361 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

0
Comment
Question by:dschrishuhn
  • 4
  • 3
8 Comments
 
LVL 16

Expert Comment

by:brad2575
ID: 23661631
try removing the quotes around the 0
0
 
LVL 28

Expert Comment

by:TextReport
ID: 23661762
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
0
 

Author Comment

by:dschrishuhn
ID: 23661793
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.


0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:dschrishuhn
ID: 23661802
Hi Andrew,

Could you give an example in code? Thanks- O
0
 
LVL 28

Expert Comment

by:TextReport
ID: 23661836
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

0
 

Author Comment

by:dschrishuhn
ID: 23662263
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

0
 
LVL 28

Accepted Solution

by:
TextReport earned 150 total points
ID: 23662295
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
0
 

Author Comment

by:dschrishuhn
ID: 23662435
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"
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question