2

I have a customer who would like a customization to an old, Visual Basic 5 application which uses an Access 97 database and Jet 3.5 as the database engine.

The desired customization requires a column to be added to an existing table. The following works fine:

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

However, I would like to set a default value (i.e. either 0 or 1) for the new column. I have tried the following and a number of variations:

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

But they all result in an error stating, "Syntax error in ALTER TABLE statement. (3293)"

In researching this problem, I've seen some information which eludes to the DEFAULT clause not being supported in my antiquated configuration of Access 97 and Jet 3.5.

Can anyone confirm this or point me in the right direction to get this to work?

Thanks for your help.

HK1
  • 11,941
  • 14
  • 64
  • 99
user1373826
  • 21
  • 1
  • 2

2 Answers2

4

You could do this by using the DAO object.

Microsoft says this about modifying Access tables:

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

You can read more about it at the link below. There are examples although I didn't see where they specifically show using the DefaultValue property. http://technet.microsoft.com/en-us/library/cc966376.aspx

HK1
  • 11,941
  • 14
  • 64
  • 99
1

Per Access 97/Jet 3.5 SQL documentation no mention of the DEFAULT clause is made when describing the 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 that I know for sure is to set a default value is to open up the table design in the gui and then under field properties enter a default value. Do you have access to an installation of Access 97?

Though, I'm also guessing that with VB/VBA you can probably access the default value property for the field and set or modify - just not using sql.

RThomas
  • 10,702
  • 2
  • 48
  • 61
  • I need to do it through code so that the database on each individual user's machine gets updated. Though, as I'm thinking about this, I'm not sure that would do what I really need, even if it did work. What I really need to do is update all existing records with the desired default value for the new column. So I might just have to write some code that iterates though all the records and writes that value. – user1373826 May 04 '12 at 00:19
  • Yep, even the default setting in jet 4.0 doesn't update records that already exist. Sorry I didn't have a better answer :). – RThomas May 04 '12 at 00:29
  • 1
    @user1373826 Just update the records through SQL `UPDATE Users SET Status = 1 ` – MarkJ May 04 '12 at 10:47