issue with access application port to sql server

mitai
mitai used Ask the Experts™
on
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?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Distinguished Expert 2017

Commented:
Are you updating a record that is open on the form?  The code you posted is unnecessary if this is a bound form. You would just use:
Me.itemamt = 33

to update the current record.  You would never use an update query.

 It is also showing hardcoded values so it isn't particularly useful.  If that isn't the actual code, you might have removed something relevant.

Author

Commented:
no the form is unbound and i am getting values from the form but they are pretty much what i did.  If i change this to ADO instead of DAO then everything seems to work.  Its almost like there is a bug in DAO that is keeping the lock on the records even though i have closed the recordset and set it to nothing.
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
I'm surprised you are not getting an error that says something about adding the dbSeeChanges option as a value in the Options argument of the recordset.

1.  Does the table contain a primary key?
2.  Does the table contain a column with a RowVersion (Timestamp) data type?  (this is not a date/time field).

I would strongly encourage you to modify all tables in SQL Server which do not have both a PK and a Timestamp to ensure that they have both.

Dale
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Distinguished Expert 2017

Commented:
You left out all the code that would allow us to identify whether you are using DAO or ADO.

Access is a RAD (Rapid Application Development) Tool.  If you are not using the RAD features of Access such as bound forms, there is absolutely no reason to use Access as a FE at all.  You are much better off using some other development platform that doesn't come with the baggage that Access carries in order to be RAD.  You have saddled yourself with a tool that certain "real" programmers consider inferior because it can't twiddle bits or let you build graphic games and have gotten nothing for it.
Fabrice LambertConsulting
Distinguished Expert 2017

Commented:
@PatHartman:
You left out all the code that would allow us to identify whether you are using DAO or ADO.
set db = currentdb
OP is using DAO.

@mitai:
What about an update query instead of building a recordset ?
Dim sql As String
sql = vbNullString

sql = sql & "UPDATE inventory" & vbCrLf
sql = sql & "Set itemamt = 33" & vbCrLf
sql = sql & "WHERE customerid = 1" & vbcrlf
sql = sql & "  AND productid  = 10;"

Dim db As DAO.Database
Set db = CurrentDb

db.Execute sql, dbFailOnError

Open in new window

Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

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

 What do you mean by a "key lock", and what is the issue your running into?

 There is nothing wrong with the code you posted, although as Dale suggested, I would have written it as an SQL Update rather than opening a recordset, which is extra overhead.

 Still, it should work without issue.

Jim.

Author

Commented:
Pat, i agree that it is a rapid tool and that in a perfect world that would be the case.  I have inherited this app and am tasked with moving it to sql server and i don't have the time for a complete rewrite otherwise i would do it in .net or something else.  I have converted all the tables to sql using the sql server tools and if i change that same code to use ADO it works well and lets go of the lock on the sql table.

Jim, when i do the .update statement sql server creates multiple locks on the inventory table.

it creates a page lock and it creates a key lock as well, i know sql server pretty well but i am not sure why it needs both but it does create both.  if i use ADO then once i do the .close of the recordset both of the locks disappear and everything is back to normal.  If i use dao, the locks persist even after i have closed the recordset and set the recordset to nothing.

It's almost like this is a bug in dao or there is something different that i am not doing to allow those locks to release.

Pat all the tables have a PK and i do believe they have the timestamp as well but i will check into that.

Again this is a large application, i would just recode it to use ADO and this may be my best option but i would hate to do this work to find out there was something i missed and it would work using the existing DAO code.  Any other suggestions?

thanks
Fabrice LambertConsulting
Distinguished Expert 2017

Commented:
If everything fail, write a stored procedure on the server, and call it.
Distinguished Expert 2017

Commented:
My understanding is that Access does not influence locking on the server regardless of your form settings so whether you are using DAO or ADO should not matter.  Are you sure the form is not actually bound?  That could cause a conflict if you are sitting on a bound record and then update it via DAO or an update query.

If the application is currently using Jet/ACE, is it not also using bound forms?  It isn't unheard of but it would be extremely rare to see Jet/ACE as the BE and unbound forms as the FE.  If you already have bound forms, use them.

PS, DAO is optimized for Jet/ACE.  If you are going to the trouble of rewriting the app to use unbound forms, you should also convert to ADO.  The only reason for sticking with DAO when you use bound forms is because bound forms use DAO recordsets and you can get into trouble mixing and matching DAO and ADO if you are not careful.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial