MS Access 2019 - SQL Server 2017 - Recordset cannot be updated

Question

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.

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)

In Access Module:

#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

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

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 commands like

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

works.

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.

You cannot just 'update' Primary Keys, they are assigned by the database and immutable (unless you enable `INSERT IDENTITY`). — steenbergh, Oct 04 '19 at 07:48
@steenbergh If I believe the post, the primary key is not an identity column, thus should be updateable. — Erik A, Oct 04 '19 at 07:53
Have you tested if the recordset is updateable from code (e.g. can you use `rs.AddNew`)? — Erik A, Oct 04 '19 at 07:55
This might also be related to how SQL server handles queries. If it's not really a table but a query, it's rarely updateable if executed in SQL server. Instead, one can use a view with an `INSTEAD OF UPDATE` trigger. — Erik A, Oct 04 '19 at 07:58
Check recordset properties (CursorLocation, etc ) in locals window, as they can vary from your settings if they would be illegal (e.g you set CursorLocation after open connection, not sur eif this is valid). — ComputerVersteher, Oct 04 '19 at 14:06
The problerm is that the recordset is not updatable.The property rs.recordcount is -1 efter adocom.execute. Therefore, it's not possible to use rs,addnew etc. — andarvi, Oct 05 '19 at 07:02
Can't you put the complex query into a **view**, then link that into Access and work with it? — Andre, Oct 05 '19 at 09:00
Yes, I can, but rs.recordcount is still -1, so the recordset is not updatable. — andarvi, Oct 06 '19 at 15:08
Well, for the recordset to be updatable, you will need to include the PK. Even if the PK is not some autonumber, you still have to include it in the query in addition to any additional fields. Access can't and does not generate a update field as per your example since you can't specify in the where clause what column (other then PK) to use for the update. You could use a un-bound form if you going to use a where/filter that is somthing other then the PK. — Albert D. Kallal, Oct 06 '19 at 16:53

ComputerVersteher · Answer 1 · 2019-10-07T14:06:40.930

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

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

This works for me:

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!

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

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

Thanks, I'll test your proposal tomorrow. Until then; It doesn’t appear that I switch between early and late binding with complier directive to get the intellisense during developing but stability during production and declare all enums “manually” on late binding. — andarvi, Oct 08 '19 at 13:58

MS Access 2019 - SQL Server 2017 - Recordset cannot be updated

1 Answers1

Linked