Avatar of mitai
Flag for Canada

asked on 

issue with access application port to sql server

I have a Microsoft access application that i am trying to port to use the sqlserver express database in the back end instead of tables within the application.  I changed all of the tables in the access database to attached tables and for the most part everything works tickity boo.

I do have an issue on some of my code where i am trying to update a field.

set db = currentdb
set rst = db.openrecordset("select * from  inventory where customerid = 1 and productid = 10",dbopendynaset, dbseechanges)
with rst
     .fields("itemamt") = 33
end with
set rst = nothing

Open in new window

this code works fine except when it does the update, sql server adds a key lock on the database and doesn't release it

later in my code i go to update something else and whey i go to open the recordset (querying on the inventory table again) it locks and eventually returns an error

Has anyone else seen this behavour with linked sql server tables?  is there a way to force the lock to disappear when i close the recordset?
DatabasesMicrosoft AccessMicrosoft SQL Server* DAO

Avatar of undefined
Last Comment

8/22/2022 - Mon