Results 1 to 13 of 13
  1. #1
    jasdem is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2013
    Posts
    27

    Linked tables and exclusively Opened nonsense

    I have a database that I manage. Multiple users need to enter data at the same time. I have the database (FE and BE) in a trusted location and the linked data files are in a trusted location. However, from time too time I receive an error that a linked table is being used exclusively by another user. I am not sure what I need to do to overcome this issue.

  2. #2
    mrojas is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Sep 2013
    Location
    Concord California
    Posts
    72
    This normally occurs when a user has opened a table in design mode.

  3. #3
    jasdem is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2013
    Posts
    27
    The users are front end only and don't have access to design mode. Could it have something to do with data locking information files?

  4. #4
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,862
    Maybe the table is locked for editing? Are you using DAO to edit records? I believe dbPessimistic is default.

  5. #5
    Dal Jeanis is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    This can happen if there is a user who does not have the right to create/update the lock file (.ldb) in the directory where the BE is. What happens is, since Access can't create the ldb file to share permissions, Access opens it exclusive.

    Check user permissions on the LAN in that directory.

  6. #6
    jasdem is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2013
    Posts
    27
    I double checked with our staff and everyone in the unit has the same settings (modify, read/write, read & execute). I am not 100% sure but I don't think that people are in exclusive. Typically, 6+ users are in the db at one time. It is only on occasion that we have this issue. I also checked and everyone has the same permissions with the linked table. The problem occurs more frequently when people are creating emails and sending them or generating reports. The problem also is specific to this one table. Could it be a property of the forms? Also, this problem started happening when we switched to Windows 7 and Access 2010.

  7. #7
    jasdem is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2013
    Posts
    27
    Quote Originally Posted by ItsMe View Post
    Maybe the table is locked for editing? Are you using DAO to edit records? I believe dbPessimistic is default.
    FE users just draw information from that table. They are not editing the table. Could it be with multiple users drawing from that table at the same time?

  8. #8
    Dal Jeanis is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    Okay, lots of possibilities here.

    1) Generating reports should never cause a lock. (For some values of "should" and "never".)

    2) Creating emails can cause delays while the various systems integrate with each other...

    3) The change to Win 7 and to Access 2010 is probably significant.
    A) Was your prior O.S. Win XP (sigh) or Vista (cough)?
    B) Was your prior Access 2003 (sigh) or 2007 (cough)?
    C) How did you convert?
    D) Was the db split before conversion to Access 2010 or after?

    4) If there is only a single table affected, I'd start by isolating all the processes, queries, forms, and reports that are dependent on that table.

    5) If the email generation process is complex or lengthy (mailmerge-type), and is believed to be your best candidate for causing the lock, then you might consider a two-step process that extracts the required db records to a temp table in the FE, then generates the emails. If that patch solves the problem, then you can look deeper into what part of the prior process was causing the lock. If not, then you've eliminated a suspect.

  9. #9
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,862
    Quote Originally Posted by jasdem View Post
    FE users just draw information from that table. They are not editing the table. Could it be with multiple users drawing from that table at the same time?
    It is doubtful. If I were to use only my imagination, I would guess that a DAO recordset only goes Pessimistic during the .Edit command. So a simple .FindFirst or .MoveNext would not, IMO, lock out another user. You could easily do a search within the VBA editor for "DAO". Anywhere there is setting a DAO.Recordset you could add ,dbOptimisitc after the query or table name.

    Example
    ("qryName", dbOptimistic)

    Since you are only viewing information, moving the default to Optimistic will have no adverse effect.

    Aside from that, the warning msg. you are receiving is interesting. Perhaps you can post exactly what it is saying here. It does not seem like a Windows active directory message or Windows permission msg. It seems like a msg. associated to how the DB was opened by a user with Admin rights.

    I would check in the settings of the DB. If you have many front ends, I would check each one. I believe you can globally set the capability of going exclusive. Under "Client Settings" and "Advanced" there is a default for opening the DB "Shared" or "Exclusive". I would start there.

  10. #10
    jasdem is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2013
    Posts
    27
    Quote Originally Posted by Dal Jeanis View Post
    Okay, lots of possibilities here.

    1) Generating reports should never cause a lock. (For some values of "should" and "never".)

    2) Creating emails can cause delays while the various systems integrate with each other...

    3) The change to Win 7 and to Access 2010 is probably significant.
    A) Was your prior O.S. Win XP (sigh) or Vista (cough)?
    B) Was your prior Access 2003 (sigh) or 2007 (cough)?
    C) How did you convert?
    D) Was the db split before conversion to Access 2010 or after?

    4) If there is only a single table affected, I'd start by isolating all the processes, queries, forms, and reports that are dependent on that table.

    5) If the email generation process is complex or lengthy (mailmerge-type), and is believed to be your best candidate for causing the lock, then you might consider a two-step process that extracts the required db records to a temp table in the FE, then generates the emails. If that patch solves the problem, then you can look deeper into what part of the prior process was causing the lock. If not, then you've eliminated a suspect.

    I believe that we went from Vista to 7, I know we went form '07 to '10. The db's were split before the conversion. I wasn't the manager of the db during the conversion so I cannot speak to that.

    The emailing are set to take a snap shot of the table so others can send emails concurrently. SO I believe that the 2-stage process is set to run already.

  11. #11
    jasdem is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2013
    Posts
    27
    Quote Originally Posted by ItsMe View Post
    It is doubtful. If I were to use only my imagination, I would guess that a DAO recordset only goes Pessimistic during the .Edit command. So a simple .FindFirst or .MoveNext would not, IMO, lock out another user. You could easily do a search within the VBA editor for "DAO". Anywhere there is setting a DAO.Recordset you could add ,dbOptimisitc after the query or table name.

    Example
    ("qryName", dbOptimistic)

    Since you are only viewing information, moving the default to Optimistic will have no adverse effect.

    Aside from that, the warning msg. you are receiving is interesting. Perhaps you can post exactly what it is saying here. It does not seem like a Windows active directory message or Windows permission msg. It seems like a msg. associated to how the DB was opened by a user with Admin rights.

    I would check in the settings of the DB. If you have many front ends, I would check each one. I believe you can globally set the capability of going exclusive. Under "Client Settings" and "Advanced" there is a default for opening the DB "Shared" or "Exclusive". I would start there.


    No one is running the db in exclusive. We still are operating alright aside from when sending emails. What will the pessimistic/optimistic do?

  12. #12
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,862
    I got this from the help files

    Code:
    dbOptimistic value =3 Optimistic concurrency based on record ID. Cursor compares record ID in old and new records to determine if changes have been made since the record was last accessed.
     
    dbOptimisticBatch value =5 Enables batch optimistic updates (ODBCDirect workspaces only).
     
    dbOptimisticValue value =1 Optimistic concurrency based on record values. Cursor compares data values in old and new records to determine if changes have been made since the record was last accessed (ODBCDirect workspaces only).
    Note 
    ODBCDirect workspaces are not supported in Microsoft Access 2010. Use ADO if you want to access external data sources without using the Microsoft Access database engine.
    dbPessimistic value =2 Pessimistic concurrency. Cursor uses the lowest level of locking sufficient to ensure that the record can be updated.
    The way I understand it Pessimistic will try to lock the record sooner than Optimistic. Pessimistic will lock the record as soon as the record is placed in edit mode. Optimistic will try to lock it at the last moment just before Access attempts to write to the specific row via the .Update command.

    I don't know how you are getting the info now for hte email list. You may want to consider something like dbOpenSnapshot, dbForwardOnly

    What exactly is the error masg saying to the user. Have you determined if it is Windows vs Access?

  13. #13
    Dal Jeanis is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    Well, my next SWAG* diagnostic method would be to create a test version of the front end that intentionally dropped the link to the backend (either the one table if you're sure it's a single table, or the whole BE if not) before engaging in sending email, then relinked to the BE afterward.

    This would be on the theory that Office automation was causing the problem due to handshaking issues between Access and Outlook.

    Hmmm. That brings up some interesting possibilities. I cut the time of an Access-to-Excel export routine by about 90% by changing a few coding techniques. The exact techniques aren't precisely relevant to Outlook, but I might be able to detect any similar opportunities.

    Please post the mail/merge code.

    *SWAG = Scientific Wild-Assed Guess


    Oh, and one other question - does your database track contacts? That is, is it keeping track of what emails have been sent? If so, that process is the first place that I'd debug, again setting it to a two-step process - update to a local temp table during the mailmerge, then send update transactions to the BE after mails were verified as sent.

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

Similar Threads

  1. Replies: 2
    Last Post: 04-25-2012, 08:27 AM
  2. Replies: 5
    Last Post: 02-02-2012, 06:42 PM
  3. Opening a back-end database exclusively from front-end
    By TheChairman in forum Database Design
    Replies: 7
    Last Post: 01-06-2012, 07:58 AM
  4. cannot opend db exclusively
    By drewetzel in forum Access
    Replies: 3
    Last Post: 01-03-2012, 11:28 AM
  5. Speed up linking tables when front end is opened
    By snoopy2003 in forum Programming
    Replies: 6
    Last Post: 03-19-2011, 01:01 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