We help IT Professionals succeed at work.

issue with access application port to sql server

90 Views
Last Modified: 2020-05-08
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

CERTIFIED EXPERT
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
CERTIFIED EXPERT
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
CERTIFIED EXPERT
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
CERTIFIED EXPERT
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
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

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
Consulting
CERTIFIED EXPERT
Distinguished Expert 2017
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
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.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.