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)
.fields("itemamt") = 33
set rst = nothing
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?