使用MS Access插入SQL Server [英] Inserting into SQL Server using MS Access

问题描述

我知道这将是一个普遍的问题，但是我没有找到任何解决方案.

I know this will be a common problem, nevertheless I didn't find any solution.

我将访问数据库迁移到了SQL Server Express中. SELECT和UPDATE可以很好地工作，实际上比Access引擎要好.但是我无法运行标准插入...

I migrated my access database into SQL Server Express. SELECT and UPDATE work well, actually better that with the Access engine. But I can't get standard insertions running...

这不起作用:

Set rc = CurrentDb.openRecordset("SELECT * FROM DBLog WHERE false")
rc.AddNew
rc.update  '-->ERROR
rc.close

这也不起作用:

DoCmd.RunSQL("INSERT INTO [test2].dbo.DBLog (type) VALUES (6)")

这确实有效: 通过传递发送上述SQL.因此，这不是SQL Server的问题.

This does work: sending the above SQL with pass through. So its not the SQL Servers problem.

我在数据库中设置了IDENTITY和PRIMARY. Access也知道主要知识.在设计视图中，虽然没有自动编号"，但这可能是问题所在.但是如何解决呢?

I have set IDENTITY and PRIMARY in the Database. Access also knows the primary. In design view although there is no "Autonumber", and this may be the problem. But how to resolve that?

推荐答案

作为一般规则，在迁移到后端的SQL Server之后，然后是一个典型且通用的开放式记录集

As a general rule, after migrating to SQL server for the back end, then a typical and common open reocrdset of

Set rst = CurrentDb.OpenRecordset("Contacts")

需要成为

Set rst = CurrentDb.OpenRecordset("Contacts", dbOpenDynaset, dbSeeChanges)

因此，在您的情况下:

Set rc = CurrentDb.openRecordset("SELECT * FROM DBLog WHERE false" dbOpenDynaset, dbSeeChanges)

这篇关于使用MS Access插入SQL Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！