Results 1 to 5 of 5
  1. #1
    EddieN1 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2011
    Posts
    313

    Do you need Access Record Locks if Backend is SQL Server


    If all my data resides in linked, SQL Server tables (2008 R2), do I need for Access to try to maintain the record locks? If not, how can I disable record locks within the database? I have about 200 users using the database and I can't rely on them changing their individual environments. In fact, they use other Access databases that may need to use Access record locks. Thanks, Eddie

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    you have 200 people using your access database? are they just using it for information lookup?

    Normally if you do not define a primary key when you link a SQL table the data is not updatable, particularly if the source table does not have a defined PK.

    If the source table is updatable and your linked table is also showing as updatable, you can probably change permission on the SQL side for the individual users so that they have read only access to the data, but I'd be careful about doing that especially if the tables are part of another piece of software and their SQL login is so that they can use a front end that is not your Access db.

  3. #3
    EddieN1 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2011
    Posts
    313
    rpeare, No, they aren't using it just for lookups. They're doing moderate volume (10,000 transactions per day) inserts and updates. They don't normally do many deletes. When the backend data was stored in an Access database, we could get only about 15-20 in the system or the backend would get corrupted. Since we moved the backend to SQL Server, we haven't had a problem with this workload. My question is that if SQL Server controls record locks, is it necessary for Access to do so as well?

  4. #4
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    That's a good question. I've never done experimentation to find out. Is it possible for you to create a fake record and test on two different people's machines. Both of them open the same record at the same time, one person deletes the record while the other person still has it up and see what happens (particularly if the person who is not deleting makes a change and saves the record)

    I'd experiment on some test data in a test database first and see what your limitations are on the editing/deleting of the same record.

  5. #5
    EddieN1 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2011
    Posts
    313
    I'm going to mark this as solved since we don't seem to be getting anywhere. In summary, the record locks work fine with an Access Front End and SQL Server backend provided you have a Timestamp field in each table you plan to update. Whether you need to tell Access to maintain record locks or not apparently is optional. Thanks, Eddie

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 02-16-2013, 12:35 PM
  2. Replies: 7
    Last Post: 04-05-2012, 07:38 AM
  3. Pros & Cons or Record locks
    By h3nno in forum Forms
    Replies: 0
    Last Post: 03-22-2012, 10:48 PM
  4. Replies: 1
    Last Post: 09-13-2011, 01:52 PM
  5. Replies: 2
    Last Post: 03-21-2011, 12:55 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums