Geek Answers Handbook

DEFAULT in ALTER TABLE statement resulting in syntax error

I have a client who needs to configure an old Visual Basic 5 application that uses an Access 97 and Jet 3.5 database as the database engine.

The required setup requires adding a column to an existing table. The following works great:

strSQL = "ALTER TABLE Users ADD COLUMN Status BYTE" pdbDatabase.Execute strSQL

However, I would like to set the default value (i.e. 0 or 1) for the new column. I tried the following and several options:

 strSQL = "ALTER TABLE Users ADD COLUMN Status BYTE DEFAULT 1"

But all of them lead to an error: "Syntax error in the expression ALTER TABLE (3293)"

While investigating this problem, I saw some information that is not consistent with the DEFAULT clause, which is not supported in my legacy Access 97 and Jet 3.5 configuration.

Can someone confirm this or point me in the right direction to make this work?

Thank you for your help.

+2
source share
2 answers

You can do this using a DAO object.

Microsoft is talking about changing access tables:

In addition, certain types of Microsoft Access-specific properties, such as the ValidationRule and DefaultValue properties for fields, can only be set through the Microsoft Access user interface or through the DAO in code.

You can learn more about this at the link below. There are examples, although I have not seen where they specifically show the use of the DefaultValue property. http://technet.microsoft.com/en-us/library/cc966376.aspx

+4
source

The documentation for Per Access 97 / Jet 3.5 SQL is not mentioned in the DEFAULT clause when describing ALTER Table or CREATE Table statements. It is described as a new feature of Jet 4.0 here: http://support.microsoft.com/kb/275561

The only way I know for sure to set the default value is to open the table project in gui, and then enter the default value using the field properties. Do you have access to install Access 97?

Although, I also assume that with VB / VBA, you can probably access the default value property for the field and set or change - just without using sql.

+1
source

More articles:


All Articles