Results 1 to 9 of 9
  1. #1
    Newbie2db is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Jan 2018
    Posts
    5

    No Record Locking issue

    I will try to make this a brief as I can.


    We have 2 Access DB's that were written a lonnng time ago (20 years or so ago).

    We are small division of a larger company and we had our own File Server and Corp wanted us to move our files over to their new servers.

    We have to join the security/workgroup administrtor which that system.mdw file is on a different server.
    Before we moved the DB's over to the new server my users were able to login into the DB's and edit no matter how many users were accessing the DB's.
    One of the DB's was working just fine, but the other one was not.

    I remembered about 8 years ago Corp purchased us new servers and a consulting company moved everything over to the new servers.
    We had issues with the DB's working correctly but I remembered that one of the consultants said they had to point the DB to the new server. and everything was fine for the last 8 years.

    Now that we have moved over to new servers again, one of the DB's was working correctly but the other one was not. I mentioned to someone who knows a little bit more about Access DB's than myself about pointing the DB's to the new server.
    They made the change.
    Once they made the change the DB that was not working properly was working properly except for only one user at a time can access the DB. The other DB that was working proper before ... more than one user at a time can login and access it... now only one user can access it at a time.

    When a user opens up one of the DB's I see in Explorer a file with a type: Microsoft Office Access Record-Locking Information file. Before the move and change we never had this file there.

    I need to get this corrected.

    I have gone into Tools/Options/Advance
    Default Open Mode: Shared
    Default Record Locking : No Locks
    Open Databases using record-level locking box checked.
    These settings are the same on the new server as they are on old server.
    I just don't understand why only one user at a time can.

    Is there someplace else that I need to go and change some settings.

    Any help would be greatly appreciated.

    thanks in advance.

    Kind of New to DB's

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Is the database split into Frontend (FE: forms queries) and backend (BE: tables) ? (it should be)
    if so,
    When you move the BE tables, the FE will stop working, so you must use LINKED TABLE MANAGER (on the FE) to point to the new folder\server.

    I cant remember if system.mdw needed anything.

  3. #3
    Newbie2db is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Jan 2018
    Posts
    5
    Quote Originally Posted by ranman256 View Post
    Is the database split into Frontend (FE: forms queries) and backend (BE: tables) ? (it should be)
    if so,
    When you move the BE tables, the FE will stop working, so you must use LINKED TABLE MANAGER (on the FE) to point to the new folder\server.

    I cant remember if system.mdw needed anything.

    Thank you for responding back. I will "try" to "LINKED TABLE MANAGER" on the FE. I just have to remember where that is.
    I hope to get to it later today since I am not in the office right now.
    Thanks again.

  4. #4
    Newbie2db is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Jan 2018
    Posts
    5

    one DB split the other is not

    Quote Originally Posted by ranman256 View Post
    Is the database split into Frontend (FE: forms queries) and backend (BE: tables) ? (it should be)
    if so,
    When you move the BE tables, the FE will stop working, so you must use LINKED TABLE MANAGER (on the FE) to point to the new folder\server.

    I cant remember if system.mdw needed anything.

    I don't know if it is the way this one DB was written or what but when I go into the DB then Tools/database utilities/Linked table manager I only see BE no FE. and the BE is linked to the new server. Would the FE be in the same Linked Table Manager or do I need to go somewhere else?

    I saw on another forum that "you should always split a multiuser database into front end (FE) and back end (BE) databases. A copy of the FE database should be installed on each user's local hard drive. The BE database, which contains only the shared tables, is located on a file server." I had done that and had one user make an addition to the DB and had another user check to see if the addition was there and it was. So I put that DB on all involved users desktop.

    I think we are good for that DB, however the other DB is not Split.
    The Tools\Options\Advance does have
    Default open mode set to shared
    Default record locking set to No Locks
    Open databases using record-level locking box is Checked.

    I don't know what else to do in order for multiple users to use this db at the same time.
    i am thinking of making a copy of this DB and try doing a split on it and see what happens.

  5. #5
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by Newbie2db View Post
    I don't know if it is the way this one DB was written or what but when I go into the DB then Tools/database utilities/Linked table manager I only see BE no FE. and the BE is linked to the new server. Would the FE be in the same Linked Table Manager or do I need to go somewhere else?
    To link the FE to the BE tables, you would be in the FE.


    Quote Originally Posted by Newbie2db View Post
    <snip>however the other DB is not Split.
    For multi-user, it should/must be split. Otherwise you have a great change of corruption and losing your dB. DO backups!!!


    Quote Originally Posted by Newbie2db View Post
    I don't know what else to do in order for multiple users to use this db at the same time.
    First step would be to split the dB.

    Don't have access to A2000 or A2003, but there is a setting that is either "Shared or Exclusive".
    Look for an option "Default Open Mode".

  6. #6
    Newbie2db is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Jan 2018
    Posts
    5
    Quote Originally Posted by ssanfu View Post
    To link the FE to the BE tables, you would be in the FE.



    For multi-user, it should/must be split. Otherwise you have a great change of corruption and losing your dB. DO backups!!!



    First step would be to split the dB.

    Don't have access to A2000 or A2003, but there is a setting that is either "Shared or Exclusive".
    Look for an option "Default Open Mode".

    Never Split a DB before, so my question is:

    If I make a copy of the DB and go through the process of splitting the copy DB it will NOT affect the original DB that the users are accessing now, correct?
    I will need to do the split on the same server that the original DB is on since we don't have Access 2003 on any computer or server.

    Thanks
    R

  7. #7
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You definitely want to back up what you have just in case things go south. Some of what you write is a bit confusing to me (like a db is "linked" to a server). If this is a corp network, then I envision the following (NOTE ignores the use of a mdw file):
    The master db is split into FE and BE. The BE is stored on a server (not linked to it). Each user gets a copy of the FE to put on their desktop or personal network drive space. Before making any copies, the FE is linked to the BE via the linked table manager, and this is done in the FE, not BE. Each copy will then point to the BE tables. Also before distributing, the FE Trusted zone settings should include the BE location to be on the safe side. Can't recall if each FE needs this for the individual's FE location as well. If so, one way of having to get around dealing with TZ settings on each user's fe after each new FE release is to keep them all in a shared network folder and have them use desktop shortcuts to their copy. The BE shared setting must not be set to open exclusively (this isn't the default value). The FE then gets distributed. Also, each user must have full rights on the folders involved (except maybe the ability to create or delete folders) because Access needs to alter the files, plus create/modify the laccdb file as well as the last user deleting it when Access closes.

    The mdw is no longer supported and for the most part, has become archaic. You might want to consider revamping the db's so that they aren't needed. User access control is often governed by a table of users that also contains system level types such as admin, user, supervisor, etc. and is used to control not only who can see/use what, but who gets in. The fact that the number of concurrent users allowed seems to have changed on its own suggests that there is an issue with the mdw file. This could be as simply as your db's not now knowing where to find it.
    If I make a copy of the DB and go through the process of splitting the copy DB it will NOT affect the original DB that the users are accessing now, correct?
    MAYBE YES. Let's say you copy a pair of db's (fe/be) to your desktop and want to play with it. You delete all the data, figuring that your copied tables are the ones you're deleting from. Imagine then getting a call saying that all the production data is gone (because you're the db admin). Guess why? Your fe copy is still pointing to the network be file because you didn't link your desktop fe to your desktop be.
    Before the move and change we never had this file there.
    Well, that file is supposed to be there. It's been so long since I had anything to do with the mdw setup that I can't remember much about it, and have to wonder if it was suppressed by the use of the mdw db to control access. If that is the case, it would seem to support my idea that your db's no longer have access to your mdw files.
    Last edited by Micron; 02-12-2018 at 12:00 PM. Reason: clarification
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    Newbie2db is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Jan 2018
    Posts
    5
    Thanks for your response all for your responses and I appreciate your time to respond. I spoke with the IT group and they are going to get someone to help out on this issue.

  9. #9
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    In addition to Micron's comments....

    Quote Originally Posted by Newbie2db View Post
    I will need to do the split on the same server that the original DB is on since we don't have Access 2003 on any computer or server.
    If everyone is opening the SAME copy of the FE on the server, you are asking for corruption. Not a matter of IF, just WHEN.


    I don't know if it is available, but you might look for the Access 2003 developers edition. That would allow you to create run a time version - you wouldn't need to have Access 2003 installed on any local computer.

    I think that a copy of the Access 2003 developers edition will be very hard to find. You could convert the mdb version (A2003) to a later version of Access (A2007 - 2013). After converting to the new version and debugging, the FE (everything except the tables) could be made into a run time version.
    Again, if you have a run time version of the FE, you do not need a full version of Access on every/any computer.
    Obviously, you would need a full version to edit/change the FE/BE designs.
    As always, make a couple of backups BEFORE starting this process.

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

Similar Threads

  1. Replies: 3
    Last Post: 11-01-2013, 10:16 AM
  2. Locking a record
    By aytee111 in forum Access
    Replies: 3
    Last Post: 04-24-2012, 05:31 PM
  3. Backend Record Locking
    By Bennet in forum Access
    Replies: 4
    Last Post: 11-23-2011, 03:46 AM
  4. Record Locking
    By nyAccess in forum Database Design
    Replies: 4
    Last Post: 07-17-2011, 05:40 AM
  5. Record Locking
    By jlclark4 in forum Database Design
    Replies: 10
    Last Post: 06-15-2011, 02:22 PM

Tags for this Thread

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