vba 错误 3622 - 访问具有 IDENTITY 列的 SQL Server 表时，必须将 dbSeeChanges 选项与 OpenRecordset 一起使用
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20004932/
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
Error 3622 - You must use the dbSeeChanges option with OpenRecordset when accessing a SQL Server table that has an IDENTITY column
提问by Carl S
I'm running MS Access 2007, connecting to a MS SQL 2008 R2 server. I have a form with a multiselect box that I use to update status for several fields for the servers selected. Currently I'm using the ServerName field in the multiselect box. The problem is that there can be multiple records with the same server name. So to get around this I want to change it from ServerName to record number (ID). When I do this though VB gives the error "Error 3622 - You must use the dbSeeChanges option with OpenRecordset when accessing a SQL Server table that has an IDENTITY column". I've looked at many different posts on different websites, but can't figure out how to implement this into my code. The script works perfectly when I use ServerName, it also works perfectly if I go to the SQL server and change the field to Identity = False. The problem with that is that I can't create new records with the autonumber. It also works perfectly if I hard code the line numbers in an Update query. The problem with that is I can't hardcode the line numbers for everyone that uses the database. The issue only appears to be related to VB. Below is what I have currently. As you can see I tried adding the dbSeeChanges to the Execute line.
我正在运行 MS Access 2007，连接到 MS SQL 2008 R2 服务器。我有一个带有多选框的表单，用于更新所选服务器的多个字段的状态。目前我在多选框中使用 ServerName 字段。问题是可能有多个记录具有相同的服务器名称。所以为了解决这个问题，我想将它从 ServerName 更改为记录号 (ID)。当我这样做时，虽然 VB 给出了错误“错误 3622 - 访问具有 IDENTITY 列的 SQL Server 表时，您必须将 dbSeeChanges 选项与 OpenRecordset 一起使用”。我查看了不同网站上的许多不同帖子，但不知道如何将其实现到我的代码中。当我使用 ServerName 时，脚本运行良好，如果我转到 SQL 服务器并将该字段更改为 Identity = False，它也可以完美运行。问题是我无法使用自动编号创建新记录。如果我在更新查询中对行号进行硬编码，它也能完美运行。问题是我无法为使用数据库的每个人硬编码行号。该问题似乎仅与 VB 相关。以下是我目前所拥有的。如您所见，我尝试将 dbSeeChanges 添加到 Execute 行。
Private Sub btnRoarsStatus_Click()
Dim strSQL As String
Dim Criteria As String
Dim Itm As Variant
With Me.lstServerNames
If .ItemsSelected.Count > 0 Then
For Each Itm In .ItemsSelected
Criteria = Criteria & "," & .ItemData(Itm)
Next Itm
' remove leading comma
Criteria = Mid(Criteria, 2)
' execute the SQL statement
strSQL = "UPDATE buildsheet SET [Roars Status] = " & Chr(34) & _
Me.cboRoarsStatus & Chr(34) & " WHERE ID IN(" & Criteria & ")"
Debug.Print strSQL
CurrentDb().Execute strSQL, dbSeeChanges
Else
MsgBox "You must select one or more items in the list box!", _
vbExclamation, "No Selection Made"
Exit Sub
End If
End With
MsgBox "Completed", vbExclamation, "Completed"
End Sub
回答by Ephedra
I had a similar Problem with a Delete Statement that I wanted to execute and solved it by:
我有一个与删除语句类似的问题，我想执行它并通过以下方式解决它：
CurrentDb.Execute SqlStr, dbSeeChanges
note the missing () after CurrentDb
注意 CurrentDb 后面缺少的 ()
回答by Navia
For me worked this:
对我来说是这样的：
CurrentDb.Execute strSQL, **dbFailOnError +** dbSeeChanges
dbFailOnErrorwas the key...
dbFailOnError是关键...
回答by Mike M Eghtebas
CurrentDb().Execute strSQL, someotherConstant, dbSeeChanges
CurrentDb(). 执行 strSQL, someotherConstant, dbSeeChanges
I think, someotherConstantmaybe missing.
我认为，可能缺少其他常量。
