Question Record locking with Access front end and SQL Server backend

[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.

 

[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!

 

[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

 

[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

 

[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.