MS Access 2019 - SQL Server 2017 - 记录集无法更新 [英] MS Access 2019 - SQL Server 2017 - Recordset cannot be updated

问题描述

我在 SQL Server 2017 数据库中创建了一个表.然后我在 MS Access 2019 中创建了一个表单，该表单使用 ADO 对象作为记录源".在表单上，​​有一个带有 controlsource 属性=Field 的文本框.然后，当我打开表单时，一切都按预期工作(字段的内容显示在文本框中，我可以前后移动光标等等)，除了一件事:无法更改、添加或删除记录.

I've created a table in a SQL Server 2017 database. Then I've created a form in MS Access 2019 that use an ADO Object as "record source". On the form, there is a Textbox with the controlsource property=Field. Then, when I open the form, everything works as expected (the content of Field shows up in the Textbox, I can move the cursor backwards and forwards and so on) except for one thing: It's not possible to change, add or delete records.

在 SQL Server 上创建表(使用 SSMS):

Create a table On SQL Server (use SSMS):

CREATE TABLE Table1 (Field INT PRIMARY KEY)
INSERT INTO Table1 (Field) VALUES (15)
INSERT INTO Table1 (Field) VALUES (12)

在访问模块中:

#Const EarlyBinding = -1

#If EarlyBinding Then
  ' Irrelevant in this context
#Else
  Global Const adUseClient As Long = 3
  Global Const adLockBatchOptimistic As Long = 4
  Global Const adOpenDynamic As Long = 2
#End If

在访问表单的 VBA 代码中:

In Access Form's VBA Code:

Private Sub Form_Load()
  Dim sqldb as Object, adocom as Object, rs As Object
  Set sqldb = CreateObject("ADODB.Connection")
  Set adocom = CreateObject("ADODB.Command")
  Set rs = CreateObject("ADODB.Recordset")
  SQLDB.Open "Driver={SQL Server Native Client 11.0};Server=SQL;Database=Test;Trusted_Connection=yes;"
  SQLDB.CursorLocation = adUseClient
  rs.LockType = adLockBatchOptimistic
  rs.CursorType = adOpenDynamic    
  adocom.CommandText = "SELECT Field FROM Table1"
  set rs = adcom.execute
  set Me.Recordset=rs
end sub

这个问题是从一个更复杂的代码归结"出来的.如果我使用 DAO，我传递给 SQL Server 的查询需要几分钟才能执行，所以重要的是让 SQL Server 执行查询(需要 <1 秒).

This problem is "boiled down" from a much more complex code. The query I pass to SQL Server takes several minutes to execute if I use DAO so the big point is to let SQL Server execute the query (takes < 1 sek).

那么我必须做什么才能添加、修改或删除记录?

So what do I have to do to make it possible to add, modify or delete a record?

DML 命令如

adocom.execute "UPDATE Table1 SET Field=25 WHERE Field=15" 

有效.

但是，问题是，我希望访问表单以与连接到 SQL Server 的 ADO 记录集完全相同的方式运行，就像如果记录集由连接到后端 .accdb 的 DAO 对象处理一样-文件.所以记录集应该是可更新的.属性 rs.recordcount 为 -1，表示记录集不可更新.如果该属性是 adocom.execute 行之后的 -1 以外的其他属性，那么我们就回家了.

But well, the thing is, I want the Access Form to act in exactly the same way with an ADO recordset connected to a SQL Server as it would act if the recordset was handled by a DAO object connected to a backend .accdb-file. So it’s the recordset that should be updatable. The property rs.recordcount is -1 which indicates that the recordset is not updatable. If that property is something else but -1 after the adocom.execute line , then we’re home.

推荐答案

看起来像 .Execute 方法返回只读、只进游标记录集.

Seems like the .Execute method returns read-only, forward-only cursor recordset.

您必须使用Recordset.Open方法来获取非只读记录集.

You have to use theRecordset.Openmethod to get a not read-only Recordset.

这对我有用:

Private Sub Form_Load()

Dim sqldb As Object, adocom As Object, rs As Object
Set sqldb = CreateObject("ADODB.Connection")
Set adocom = CreateObject("ADODB.Command")
Set rs = CreateObject("ADODB.Recordset")
sqldb.Open "Driver={SQL Server Native Client 11.0};Server=SQL;Database=Test;Trusted_Connection=yes;"
sqldb.CursorLocation = adUseClient
rs.LockType = adLockBatchOptimistic
rs.CursorType = adOpenDynamic

rs.Open "SELECT Field FROM Table1", sqldb 'changed code

Set Me.Recordset = rs

End Sub

正如我在上面的评论中告诉您的，您应该始终检查本地窗口中记录集的设置，因为它们可能与您设置的不同！

As I told you in my comment above, you should always check the settings of the recordset in locals windows, as they may differ from what you have set!

在你的代码中，记录集.LockTypegetsadLockReadOnly，什么解释了只读形式.

In your code, the recordsets.LockTypegetsadLockReadOnly, what explains read-only form.

顺便说一句，您的代码看起来像使用后期绑定ADODB，但使用ADODB-ConstantslikeadUseClient.如果您使用后期绑定ADODB

Btw, your code looks like using late-boundADODB, but usesADODB-ConstantslikeadUseClient. They need to be defined seperate if you use late-boundADODB

这篇关于MS Access 2019 - SQL Server 2017 - 记录集无法更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！