问题描述
如何使用SQL更改MS Access中的表以将数据类型更改为自动编号"?
How can you alter a table in MS Access using SQL to change a data type to AutoNumber?
我试图跟随但没有成功
ALTER TABLE PERSON ALTER COLUMN PERSON_ID Integer PRIMARY KEY counter
);
ALTER TABLE PERSON ALTER COLUMN PERSON_ID Integer PRIMARY KEY AUTONUMBER
);
ALTER TABLE PERSON ALTER COLUMN PERSON_ID Integer PRIMARY KEY AUTOINCREMENT
);
每次我都会遇到相同的问题语法错误"，并突出显示SQL中的最后一个单词.
Each time I get the same issue "Syntax error" and it highlights the last word in the SQL.
推荐答案
对于Access中的数据定义(DDL)查询，可以使用COUNTER定义自动编号"字段.您试图在同一个字段上同时使用Integer和counter，但这是行不通的.
For a Data Definition (DDL) query in Access you use COUNTER to define an AutoNumber field. You were trying to use both Integer and counter on the same field, and that won't work.
我刚刚尝试过，它在Access 2010中对我有用:
I just tried this and it worked for me in Access 2010:
ALTER TABLE PERSON ALTER COLUMN PERSON_ID COUNTER PRIMARY KEY
请注意，为了使该语句起作用
Note that in order for this statement to work
- 表必须为空，并且
- 该表必须没有主键，甚至在[PERSON_ID]字段上也没有.
如果表中已包含行，则Access将不允许您将Numeric (Long Integer)字段转换为AutoNumber.在这种情况下，您需要使用自动编号主键创建一个新表，然后将旧表中的行插入到新表中.
If the table already has rows in it then Access will not allow you to convert a Numeric (Long Integer) field to AutoNumber. In that case you need to create a new table with the AutoNumber Primary Key and then insert the rows from the old table into the new table.
例如，对于名为[PERSON]且具有列的表
For example, for an existing table named [PERSON] with columns
PERSON_ID INTEGER
PERSON_NAME TEXT(50)
PERSON_ID INTEGER
PERSON_NAME TEXT(50)
您需要创建一个新表
CREATE TABLE PERSON_NEW (PERSON_ID COUNTER PRIMARY KEY, PERSON_NAME TEXT(50))
然后复制记录
INSERT INTO PERSON_NEW
SELECT * FROM PERSON
这篇关于访问SQL-将ALTER COLUMN转换为AutoNumber吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！