Question Record locking with Access front end and SQL Server backend (2 Viewers)

[harmankardon]

Hello all,

I am working with a Access file that has linked tables (previously local tables that have since been upsized) that reside on SQL Server.

Each user gets their own copy of the Access front-end.

If I'm not using DAO and I'm just using built-in form functionality for updating and inserting etc..., how do I implement record-locking? I'm guessing this must be done at the SQL Server level, but I'm not sure.

Could anyone shed some light on this issue for me?

 

[gemma-the-husky]

you don't actually need to do anything.

access uses something called optimistic recordlocking in normal use.

there is rarely any need for anything more than this

 

[bparkinson]

Gemma is right. If your SQL Server and app are not set up right, by that I mean the correct locking level for a particular table, the one thing I have seen is blocking, which happens at the SQL Server level. If you have a SQL Server DBA on your team that's his problem, and he'll know how to straighten it out. If not, it's fairly rare (at least in my environment) so I'd deal with it if/when it happens.

 

[DavidAtWork]

I'm not sure whether you're trying to lock records deliberately to control updates or you mean you've experienced record locking and you're unable to update.
One thing I've noticed with sql backend/ frontend set up on a network you describe, it's a good policy to add an additional field to any tables that hold data records (not static lookups) using your SQL server Manager, choose timestamp as the data type and that will prevent you experiencing record locking messages from Access.
David

 

[harmankardon]

Thank you all kindly for your responses.

Just to clarify, I'm using bound forms for my input/update forms and the tables I'm using reside on SQL Server and are setup as linked tables inside my application. I'm wondering how to implement record locking in this scenario?

I've since discovered that Access record locking is not implemented when using ODBC linked tables.

http://office.microsoft.com/en-us/a...ss-database-on-a-network-mdb-HP005240860.aspx

"When you edit data in a linked SQL database table by using ODBC, Microsoft Access doesn't lock records; instead, the rules of that SQL database govern locking. In this instance, regardless of the record-locking setting you choose for your database, Microsoft Access always acts as though the No Locks setting has been selected."

I believe I could figure out how to use SQL Server record locking if I was using unbound forms in Access and using DAO to perform inserts/updates/deletes but I'm using bound forms so I'm at a bit of a loss.

Right now, the behavior is that person A and person B can both open the same record and both Person A and Person B can begin to edit the record. Now if person A hits save first, person B gets no message that the record has been changed since they opened it when they go to hit save, and it just overwrites whatever changes person A had made.

 

[bparkinson]

Yes, SQL Server locking trumps in this case. One approach would be to use stored procedures, but only for those INSERTS or UPDATES where locking is an issue. You would want to use BEGIN TRANSACTION and COMMIT TRANSACTION in your sp. For tables that you don't have locking issues with, just let Access commit your updates/inserts from the bound form. I do this all the time.

 

[gotlength]

Did you figure out a solution? I have the same problem. I do not want a person wasting tons of time entering data only to find out they cannot save it because it's been edited by someone else since they started.

 

[Pat Hartman]

You are disconnected from the server so you cannot actually lock records from Access, nor would you want to. It would be disruptive and difficult to free the locks when problems occured. If you have a situation where multiple people might need to update a particular record, you can implement your own psuedo-locking. I do this for an application that presents the users with a list of tasks that need doing and I need to "enqueue" (old mainframe term) a record when a user starts updating it and then free it when he is finished. When a user wants to edit a record, he needs to press the "edit" button. This immediately updates the record as being locked by him. In the BeforeUpdate event of the form, I remove his ID from the lock field. The lock field is visible on the form so a user can see if a record is locked and who is working on it. He is also not able to press the edit button and so he can't make changes. The form doesn't allow scrolling so I don't need to worry about the user saying he wants to edit but never changing anything. I check the status of the lock field in the unload event and prompt the user if the edit button was pushed but no change was actually made so he can unlock the record.

In spite of my efforts to unlock the records as soon as possible, occassional problems arise such as network outage or computer freeze. So, I have a query the admin can run to unlock a particular user's records or all records.

 

[gotlength]

Thanks for the help.

Would it help at all if I used ADO or DAO instead of ODBC? This is my first time using Access and SQL, so I not sure what would be better.

Thanks!

 

[Pat Hartman]

Would it help what? DAO and ADO are using ODBC. That is how "Access" gets to the non-Jet/ACE database. Open DataBase Connetivity is a conversion tool that takes "Access" syntax and converts it to something the target database can understand.

 

[gotlength]

If I were to use adodb and SQL Server OLEDB, doesn't that bypass ODCB? I've tried using the following code, but it still doesn't not lock an edited record.

Private Sub Form_Open(Cancel As Integer)
   Dim cn As ADODB.Connection
   Dim rs As ADODB.Recordset
         
   'Create a new ADO Connection object
   Set cn = New ADODB.Connection

   'Use the Access 10 and SQL Server OLEDB providers to
   'open the Connection
   'You will need to replace MySQLServer with the name
   'of a valid SQL Server
   With cn
      .Provider = "Microsoft.Access.OLEDB.10.0"
      .Properties("Data Provider").Value = "SQLOLEDB"
      .Properties("Data Source").Value = "server-2\sqlexpress"
      .Properties("User ID").Value = "testuser"
      .Properties("Password").Value = "1"
      .Properties("Initial Catalog").Value = "res"
      .Open
    
   End With
   
      
   'Create an instance of the ADO Recordset class, and
   'set its properties
   Set rs = New ADODB.Recordset
   With rs
      Set .ActiveConnection = cn
      .Source = "SELECT * FROM Customer"
      .LockType = adLockPessimistic
      .CursorLocation = adUseServer
      .CursorType = adOpenDynamic
      .Open
   End With
   
   'Set the form's Recordset property to the ADO recordset
   Set Me.Recordset = rs
   Set rs = Nothing
   Set cn = Nothing
End Sub

Shouldn't that work?

Thanks

 

[Pat Hartman]

Why do you think you need to lock records? Why do you think SQL Server isn't managing this for you? What specific problem are you having? Do you really want to expend all this effort to go with unbound forms to solve a non-problem?

 

[gotlength]

We run meetings, so one example of where I will need record locking is if employee #1 is doing a follow up for a meeting and they are changing say how many hours a customer attended at one of their past meetings. Then the customer calls in starts talking to employee #2. (employee #1 was not talking to them. Just doing paperwork.) Employee #2 changes the customers information while on the phone with the customer.

Then the customer who was talking to employee #2 on the phone hangs up and employee #2 tries to commit the record but then it won't let them save the changes. So they don't remember what they entered and they can't save it. Bad idea.

I want it to lock where if one person begins to edit the record, another person cannot edit the same record at the same time.

I've tried logging in from two computers at the same time and simulating this so I know it's not locking like that now. Right now it will let edit it on both at the same time. But when I try to save the record it gives me the message "The record has change by another user since you started editing it...."

I don't want to go through all of the extra effort to do unbound forms. Or if that will even work. But I must have it work.

Am I going about this completely the wrong way?

Using Access 2013 and SQL server 2012.

Thanks

 

[Pat Hartman]

I'm going to guess that you are unlikely to actually run into this problem. But, the situation I described where all users were pulling from the same to-do list DOES result in multiple people trying to do the same update unless I prevent it. I told you how I handle the problem. it only takes a few lines of code in each form. Is there some reason you don't want to do that?

 

[gotlength]

I'll have to give it a shot. I'll let you know.

Thanks

 

[mdlueck]

I don't want to go through all of the extra effort to do unbound forms. Or if that will even work. But I must have it work.

I briefly describe here how I use Access in a Client/Server environment, and have no trouble with record locking what so ever.

Client/Server Architecture
http://www.access-programmers.co.uk/forums/showpost.php?p=1110794&postcount=5

I guess what goes to "extra effort" is a trade-off.. a bit more coding up-front, and silence from my user community.

 

[harmankardon]

Well, here I am over a year later and I am starting to run into this problem more often. User A and User B start editing a record around the same time from a bound form. User A hits save and when User B hits save, they get an error saying the record has been changed and basically all they can do at that point is Undo which causes them to lose everything they've typed.

I think what I will do is implement a pseudo record locking (or check in/check out) system as described by Pat Hartman.

I do like the idea of using SQL Server SP's and unbound forms but I don't have the time or resources to make such changes. I have a lot of users who are familiar with version/source control software so the idea of having to "check out" a record before editing will feel natural for them.

 

[SonicClang]

harmankardon, are you still dealing with this issue? I was just reading through this thread. I'm doing something similar to what has been suggested. I've used a separate table (I call it ttTransactions) to store the record ID of the record a user wants to edit. If another user tries opening any form in the front-end that has edit capabilities, the form first checks to make sure the ID isn't in that table. If it is, a message displays and lets them know a transaction is currently in process for that record and then closes the form. When the person who was making the edits is done and the form closes, the record is deleted from the ttTransactions table and anyone else is free to make edits. I ran into the exact scenario you described, with an inventory control system I created. Multiple people are in the system at a time and the potential is high that two people could try editing a record at the same time.

I'm doing something similar to this solution to create an auto number for packages in that system. I didn't want to use the autonumber feature because if a user cancels an entry, that number is gone forever. My forms are also unbound. When a user clicks on the form to create a new package, the code finds the highest number already assigned, adds 1, and then temporarily puts that number in a table called ttCSNumbers. There are multiple workstations from which users can check in new packages. If someone else opens the form to check in a new package, the form checks both the table where the records are ultimately stored AND the ttCSNumbers table and takes the next one. Once the form is closed, the number is deleted from the ttCSNumbers field. If I didn't do this, two packages could be checked into the system with the same number. Sure, I could simply enforce unique values in the SQL table, but by the time the user fills out all the information and two people enter their initials and password they've done quite a bit of work. I would make a lot of users unhappy if they were constantly being told they just wasted 5 minutes because someone else had the form open.

Maybe there are better ways to do this programmatically, but this works for me. I've even done this same thing in an application that uses SharePoint 2010 lists as tables in an Access front-end. From that perspective, my solution can be used no matter what system you use as a back-end.

 

[harmankardon]

Hi SonicClang,

I have since transitioned to a different position within the company and am no longer actively working on any of the Access stuff I did last year.

I never did get the chance to implement a check-in/check-out system, and we still have people trying to edit records at the same time.

If I ever do get some time to revisit this, I'll definitely be using your posts and the previous posts as reference.

However I approach it, I think it will involve some sort of separate table to track who is editing what.