vba 使用 MS Access 插入 SQL Server
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25104925/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Inserting into SQL Server using MS Access
提问by SalkinD
I know this will be a common problem, nevertheless I didn't find any solution.
我知道这将是一个常见问题，但我没有找到任何解决方案。
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...
我将访问数据库迁移到 SQL Server Express。SELECT 和 UPDATE 工作得很好，实际上比 Access 引擎更好。但我无法运行标准插入...
This does not work:
这不起作用：
Set rc = CurrentDb.openRecordset("SELECT * FROM DBLog WHERE false")
rc.AddNew
rc.update '-->ERROR
rc.close
This does not work either:
这也不起作用：
DoCmd.RunSQL("INSERT INTO [test2].dbo.DBLog (type) VALUES (6)")
This does work: sending the above SQL with pass through. So its not the SQL Servers problem.
这确实有效：通过传递发送上述 SQL。所以它不是 SQL Servers 的问题。
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?
我在数据库中设置了 IDENTITY 和 PRIMARY。Access 也知道主要的。在设计视图中虽然没有“自动编号”，但这可能是问题所在。但是如何解决呢？
回答by Albert D. Kallal
As a general rule, after migrating to SQL server for the back end, then a typical and common open reocrdset of
作为一般规则，在迁移到后端的 SQL Server 后，然后是一个典型且常见的开放记录集
Set rst = CurrentDb.OpenRecordset("Contacts")
Needs to become
需要成为
Set rst = CurrentDb.OpenRecordset("Contacts", dbOpenDynaset, dbSeeChanges)
So, in your case:
所以，在你的情况下：
Set rc = CurrentDb.openRecordset("SELECT * FROM DBLog WHERE false" dbOpenDynaset, dbSeeChanges)
回答by HansUp
Since you have a linked table named DBLog, I would execute an INSERTstatement, similar to the one which worked in SQL Server itself, against that linked table.
由于您有一个名为DBLog的链接表，我将INSERT针对该链接表执行一条语句，类似于在 SQL Server 本身中工作的语句。
Dim db As DAO.Database
Dim strInsert As String
strInsert = "INSERT INTO DBLog ([type]) VALUES (6)"
Set db = CurrentDb
db.Execute strInsert, dbFailonerror
I enclosed the field name typein square brackets because it is a reserved word.
我将字段名称类型括在方括号中，因为它是一个保留字。
