Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2008

    Unanswered: SQL and/or Exclusive Mode problem

    The better half of our planning crew has recently set up a SQL connection in a new database (my SQL knowledge is very limited to describe).

    Anyhow, we have a live DB copy with a table(s) as a direct SQL connection, each of the DB designers is supposed to have a design DB where they are designing their tables/queries/forms/reports outside of the live DB.

    So I copied the SQL connection over to my design DB (it opens properly), then created a smaller reference table (in design DB only) which again is set up/opens properly; all the relationships at the table level are correct.

    Next I created a query (in design DB only) which pulls in data from both the SQL table and the smaller ref table. I added in a calculation into a new field, added a few filters, etc etc. Nothing really exciting. The query is set up/opens properly.

    The problem occurs when I try to create a Form - PivotTable using the Form wizard. I tried to create the form using the previous Query.

    I then receive the "The wizard is unable to open your form in Form view or Datasheet viewm possible becuase another user has a source table open in exclusive mode. Your form will be opened in Design view."

    The live DB copy (where I got the SQL table) is set to Shared - No Locks - Use record-level locking is checked. My design DB has the same settings.

    I've tried Compact and Repair (possible solution i read online) and dug around for answers online to no success. I have closed and re-opened the DB. I have also shutdown and done a total reboot.

    I'm still trying to ramp-up my Access knowledge, so I'm willing to be humbled having to ask a simple question for the solution. It's entertaining enough trying to transition my classroom training into live application, but this error is throwing me off-track quickly.

    I'm more of an Excel guy being thrown to the Access wolves... hahaha

    Please have mercy on an Excel guy and help me out...

  2. #2
    Join Date
    Feb 2004


    Not sure if this will work or is feasible for your table.
    But could you dump the data from the SQL table to a local table. That way it isn't being shared. To refresh the data, you can create a query to delete the old data and reappend the new data to your local table.
    You know, I'm sick of following my dreams, man. I'm just going to ask where they're going and hook up with 'em later

  3. #3
    Join Date
    Nov 2008
    I haven'd deleted the "broken" form and now get a "The connection with the OLE server was lost, or the OLE server encountered an error while you were using it."

    Does this prove its somehow related to the SQL table I'm using?

  4. #4
    Join Date
    Nov 2008
    Still no progress

    I found another person experiencing the same problems and read a possible solution; unfortunately their solution didn't work for me.

    Our IT folks did a complete Access wipe/reinstall and I'm still experiencing the same errors. Unfortunately, I'm still treading water without that functionality.

    Any ideas? Suggestions?

  5. #5
    Join Date
    Nov 2008
    Well, the IT folks finally fixed it.

    The first IT attempt just wiped/re-installed Access program alone. Still the same error.

    The second IT attempt wiped out the entire Office suite, then re-installed everything from scratch. It then works perfectly.

  6. #6
    Join Date
    Dec 2004
    Madison, WI
    You may get the locked issue again (or another user is using the mdb exclusively.) Here's some things to look at:

    1. Look at the folder where the mdb resides (before opening the mdb). If there is an *.ldb file in that folder (ie. your mdb file is called: Myapp.mdb and there is an Myapp.ldb file in the same folder), it means that either someone else has the mdb file open or that it didn't close properly and needs to be compacted/repaired. If everyone closes out of the mdb (and you're ABSOLUTELY sure no one is in the mdb), you should no longer see the *.ldb file. If so, it could mean that again, the mdb file didn't close properly for a specific user and the *.ldb file is left hanging around. You should be able to simply open the mdb and exit and then see the *.ldb file dissappear. If not, open the mdb holding down the shift key while opening (to bypass any startup code) and then compact/repair and exit. Usually in order to do any design changes, the *.ldb file must not exist in the folder when you open the mdb. A lot of times an "unknown" user will have the mdb file open keeping that *.ldb file existing in that folder and you need to "hunt" down who it is. I might suggest using the JetUserLog utility found in the code bank which helps find out who's currently using a specific mdb file. If you are using a SQL Server backend, there are also some coding utilities in the code bank to find out who's utilizing the SQL Server tables which may help track down who's currently using the mdb.

    2. Check the MSAccess settings on each of the user's computers to make sure no one is opening the mdb exclusively. Tools -> Options, then advance tab. Make sure that "Shared" is checked and "No locks". It looks like you've already done this though and usually these settings are not changed unless someone has changed them for some reason or another. They really shouldn't need to open the mdb exclusively but sometimes again, this setting is changed by a user. You are correct in that it should be "Shared" with "No locks".

    3. If you're using "bound" forms (ie. the form has a recordsource), depending on how you have the form designed, this can sometimes "lock" things. Designing unbound forms is the ideal route to go but this does require more programming time and a bit of vba coding knowledge. Having unbound forms almost always resolves the "locked by another user" issue but plan on a fair amount of redesigning the forms for unbound. It's not a necessity but unbound forms does have many advantages.

    4. You can utilize a vbscript to "clone" the source mdb and have it generate a "copy" of the source mdb which the vbscript automatically then opens for each user (ie. each user has their own mdb file open which was "cloned" from the source mdb and the source mdb file is just used for cloning and copying new code to). Here's the link if you'd like to use this method. I have all my users open any of my mdb's using this vbscript and have never had any "locked by another user issues!!" Here's the link if you'd like to do this: It's very easy to configure the vbscript (you only have to change 1 line of code to tell it where the mdb resides and your ready to use the script.) You would create a vbscript for each mdb you're users are utilizing and then just have the user's run the vbscript to open whatever mdb the vb script is configured to open.

    I would highly encourage using this method as it has again, never caused a locked issue for me on any mdb's opened using this method. It also has many, many other benefits such as being able to copy new code without having to make everyone close out to copy the new code.

    I don't think re-installing office/msaccess is really your issue. It's more of a specific user has the mdb opened and locked or is "hanging" the *.ldb file when they close out of the mdb for some reason. For example, if a user has only read permissions to the folder the mdb resides (and not write permissions), this can often cause a lock issue as the *.ldb file cannot be created/modified when the mdb is opened by that user. All users using the mdb must have read/write permissions to that folder. FYI - the *.ldb file is sort of the file which keeps track of who's using the mdb. It can often get "corrupted" especially if someone unexpectedly exits the mdb (ie. they lose their network connection or their computer hangs up.) These are the most common issues for a "hanging" *.ldb file in the folder. Either way, you need to see NO *.ldb file before you open the mdb to do some coding/design changes. On some rare occasions, no matter what you do the *.ldb file still hangs around (and you're ABSOLUTELY sure no one is in the mdb.) If this is the case, the networking "with god permissions" can often just delete the *.ldb file (providing no one is in the mdb!!) You probably won't be able to delete this file as it usually requires top privelages to that folder.

    5. Are you using MSAccess security? (ie. you utilize an mdw file.) If not, then disregard. If so, permissions setup in the security mdw file can cause issues. If you don't have design permissions, you can't make design changes. But this doesn't sound like it's your problem and it also doesn't sound like you're using MSAccess security.

    6. If you're using a SQL Server backend (with linked SQL Server tables in the mdb), SQL Server usually does a good job of managing transactions and not locking any tables. But this is not usually related to designing a form issue and getting the locked by another user error. That's often associated with the *.ldb file still existing in the folder. Again, make sure there is no *.ldb file when you open the mdb and you shouldn't have any problems with designing a form (unless again, you're utilizing MSAccess security and don't have the correct permissions.)

    Also note that if a user opens the mdb and opens a form (bound to a table/query), and leaves that form open for a long time, this can sometimes cause "locking" issues by that user. You may want to consider adding a timer event where if the user doesn't update something on the form, it closes the form. Also make sure your forms are not opening "ALL" the records when it opens (ie. use criteria to in the recordsource query). Having it open to the specific record helps keep the entire table from being locked should the user "sit" on a specific record on the form for hours without doing anything which can lead to a "time-out" type of issue (but again, SQL Server "usually" helps keep this issue in line.)

    Check to make sure everyone has a good solid connection over the network to the mdb. A user with a connection which "hiccups" periodically can cause locking issues while they are in the mdb and the connection is dropped or hiccups.

    Again, I encourage trying out the vbscript in the link above. It will save you many headaches with tracing down who has the mdb open and locked and the ability to easily make design changes without EVER having any problems with locked users. Using this technique, no one is EVER in the source mdb which allows you to freely make design changes.
    Last edited by pkstormy; 11-21-08 at 23:10.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts