troubleshooting Question

issue with access application port to sql server

Avatar of mitai
mitaiFlag for Canada asked on
DatabasesMicrosoft AccessMicrosoft SQL Server* DAO
9 Comments1 Solution96 ViewsLast Modified:
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
      .edit
     .fields("itemamt") = 33
    .update
    .close
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?
ASKER CERTIFIED SOLUTION
Log in to continue reading
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform for $9.99/mo
View membership options
Unlock 1 Answer and 9 Comments.
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
The Value of Experts Exchange in My Daily IT Life

Experts Exchange (EE) has become my company's go-to resource to get answers. I've used EE to make decisions, solve problems and even save customers. OutagesIO has been a challenging project and... Keep reading >>

Mike

Owner of Outages.IO
Phoenix, Arizona, United States
Member Since 2016
Join a full scale community that combines the best parts of other tools into one platform.
Unlock 1 Answer and 9 Comments.
View membership options
“All of life is about relationships, and EE has made a virtual community a real community. It lifts everyone's boat.”
William Peck

Member since 2004