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.
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?
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 = nothingthis 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
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.
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
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
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.
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.
@PatHartman:
@mitai:
What about an update query instead of building a recordset ?
You left out all the code that would allow us to identify whether you are using DAO or ADO.
set db = currentdbOP 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
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.
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
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.