导入Microsoft Access数据库时保留自动编号列的值 [英] Keep value of autonumber column when importing into Microsoft Access database

问题描述

我想做的是以编程方式将多个表从Microsoft SQL Server导入到Microsoft Access.

What I try is to import several tables programmatically from Microsoft SQL Server to Microsoft Access.

每个SQL Server表都有一个Identity列，相应的Access表也有一个autonumber列.

Each SQL Server table has an identity column, and the corresponding Access tables, an autonumber column, too.

现在，我想生成SQL脚本以将数据从SQL Server复制到Access，并使自动编号列的值与SQL Server中的值相同.

Now I want to generate SQL scripts to copy the data from SQL Server to Access and have the autonumber colum the same value as in SQL server.

这可能吗?

以另一种方式从Access访问SQL Server时，使用SET IDENTITY_INSERT [MyTable] ON和更高版本的SET IDENTITY_INSERT [MyTable] OFF相当容易.

When doing it the other way from Access to SQL Server, it is rather easy by using SET IDENTITY_INSERT [MyTable] ON and later SET IDENTITY_INSERT [MyTable] OFF.

我发现没有Microsoft Access这样的声明.

I found out that there is no such statement for Microsoft Access.

此外，我尝试创建Access表，首先将身份字段输入为LONG类型，然后再使用ALTER TABLE ... ALTER COLUMN语句切换到自动编号.我没有这样做.

In addition I tried to create the Access tables to import into first with the identity field as type LONG and later use the ALTER TABLE ... ALTER COLUMN statement to switch to autonumber. I failed in doing so.

所以我的问题是:有什么方法可以实现我的目标?

推荐答案

如果使用Insert Into并在MS Access中指定所有列名称，则应该可以使用.

If you use Insert Into and specify all column names in MS Access, it should work.

我刚刚创建了一个具有以下结构的表

I just created a table with the following structure

Id (autonumber)
Firstname (text)
Secondname (text)
Lastname (text)

我跑了这句话

docmd.RunSQL "insert into table2 (id, firstname, secondname, lastname) values (27, 'a', 'b', 'c')"

工作正常，并将27插入了自动编号列

It worked and inserted 27 into the autonumber column

这篇关于导入Microsoft Access数据库时保留自动编号列的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！