ALTER TABLE语句中的DEFAULT子句导致语法错误 [英] DEFAULT clause in ALTER TABLE statement resulting in syntax error

问题描述

我有一个客户想要对使用Access 97数据库和Jet 3.5作为数据库引擎的旧的Visual Basic 5应用程序进行自定义.

所需的自定义要求将一列添加到现有表中.以下工作正常:

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

但是，我想为新列设置默认值(即0或1).我尝试了以下方法和多种变体:

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

但是它们都导致错误，指出"ALTER TABLE语句中的语法错误.(3293)"

在研究此问题时，我已经看到一些信息，这些是我的Access 97和Jet 3.5的过时配置中不支持DEFAULT子句的.

任何人都可以确认这一点或为我指出正确的方向，以使其正常工作吗?

感谢您的帮助.

解决方案

您可以通过使用DAO对象来实现.

Microsoft所说的有关修改Access表的内容:

此外，某些类型的特定于Microsoft Access的属性(例如，字段的ValidationRule和DefaultValue属性)只能通过Microsoft Access用户界面或通过代码中的DAO进行设置.

您可以在下面的链接中了解更多信息.有一些示例，尽管我没有看到它们使用DefaultValue属性具体显示在何处. http://technet.microsoft.com/en-us/library/cc966376.aspx

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.

解决方案

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

这篇关于ALTER TABLE语句中的DEFAULT子句导致语法错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！