Results 1 to 11 of 11
  1. #1
    BMurph723 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2016
    Posts
    6

    Angry The database has been placed in a state by user XYZ that prevents it from being opened or locked

    Currently using a shared Access 2010 Database with many users, some of whom use VPN to access the network remotely. I had everyone update their default open mode to 'Shared' and their default record-locking to 'Edited Record' yet there are still many occasions in which the following happens:

    "The database has been placed in a state by user XYZ that prevents it from being opened or locked." and everyone else is locked out of the DB.



    These people are only entering data and are not attempting any design changes to the DB architecture. I realize that this can be caused by network interruptions, folks leaving the file open for many hours at a time, or leaving the file open and walking away from their machine/logging off their account. I have tried communicating to the folks that they need to avoid these practices and for the most part they have complied. However, there are days when the network may be a little bit slow or go in and out all day long and it is constantly causing issues with our DB.

    Are there any other actions I can take to help prevent this from happening?

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    The users should NOT be using the same database file. (front end)
    Each user should have their own copy to run on their PC , or in their own network folder. (to prevent locked db error)

    Now, the BACKEND, (tables) should only exits in 1 place on the network for all to share, but no one gets in directly.

    see: SPLIT DATABASE.

  3. #3
    BMurph723 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2016
    Posts
    6
    Thanks, ranman. After I split the DB, I guess it is recommended that I distribute the Front End to all users, rather than saving the Front End file on the network? Will this cause an issue with remote users who use VPN to connect to the network?

    And do the users just save the FE file on their desktop? So that as long as they are connected to the network, any data they enter will link to the BE file? I realize the FE file is more of an application at this point, but I just want to be sure I understand how it works so that I can instruct the users on how to use it properly.

  4. #4
    BMurph723 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2016
    Posts
    6
    ranman, i now understand what splitting the database is effectively doing. My only concern, however, is making design changes to the front end file that need to be reflected in everyone's copy of the front end. To do this would I have to make the changes to my own localized FE file and then redistribute the FE to the users?

  5. #5
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    To do this would I have to make the changes to my own localized FE file and then redistribute the FE to the users?
    Yes, that is the process. You would have to re-link the FE to the BE before you redistributed the FE to the users

  6. #6
    BMurph723 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Apr 2016
    Posts
    6
    Thanks, ssanfu. I am now experiencing issues with remote VPN users, however. When they try to open their FE files, their machine cannot locate the BE file. I guess this is because of how they mapped to our network drive, but I'm not sure what to do to fix this problem. Please advise. Thanks!!

  7. #7
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    How are the VPN uses linked to the BE?
    Mapped drive? Or UNC??
    Was the BE moved?

    Have you re-linked the remote VPN users?
    Don't know why the remote VPN users link would break just because you changed your locak copy of the FE.

  8. #8
    BMurph723 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Apr 2016
    Posts
    6
    We were using a shared file on the network which was not split. Once I split the DB and distributed ACCDE FE files to the users, the VPN users found that they could not use the FE file because the file could not locate the BE file (due to the network path being different than the LAN path I used from my own FE file). The local users had no issues whatsoever. I believe the VPN users are mapped to the network drive.

    I'm trying to think of some easy fixes I could do to get everyone online. Would the VPN FE files be able to locate the BE if I saved the FE file on the network drive and had the VPN users copy it to their desktops? [I don't think this would help at all, since the network path would remain the same, but I'm just spit-balling here.]

    The VPN users cannot access the Linked Table Manager to select the correct BE files in both the ACCDE and ACCDB versions of the FE file, so I think this is a networking issue that I will have to have our local IT help me with. Any suggestions you can give would be appreciated, though.

  9. #9
    BMurph723 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Apr 2016
    Posts
    6
    I guess I could have each VPN user split their own DB and then use the Linked Table manager to link to the correct BE file...that would be my last resort, however.

  10. #10
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Can you log in as a VPN user and link the FE to the BE? I would use the UNC path instead of a mapped drive.
    Then sent that FE to the VPN users.

  11. #11
    redbull's Avatar
    redbull is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Mar 2012
    Location
    Missouri
    Posts
    480
    So, over vpn everyone is mapped differently? Or is there any consistency there? you can code the linking between the front end and backend.

    UNC should work across the board, IF the users are connected.

    Updating everyones front end is easier than you think, email out a vbs file link (stored on the network drive) that will copy the front ends to their desktops.


    I used to use backends for recording program activity, across 100's of users. The VPN users were always a pain because their append queries took so long that it caused some lost data. Eventually I did a check for a running .exe, if it was there I put remote people on their own backend.



    Heres something that might get you started with creating the link in vba.
    Code:
    Sub CreateDBLink
    
    Dim BackEnd as DAO.Database
    on error resume next '                          or use an error handler, but it'll do the same thing. 
    
    Set BackEnd = CurrentDB.TableDefs("tblLink")
    
    if err.number = 3265 then '                             The link wasn't established for some reason, here is where you could put in the vpn backend paths.
    docmd.transferdatabase aclink, "Microsoft Access", "Location.accdb",actable, "tblLink"
    end if
    
    end sub

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

Similar Threads

  1. Replies: 3
    Last Post: 08-26-2015, 09:46 AM
  2. Replies: 7
    Last Post: 09-11-2014, 12:26 PM
  3. Replies: 1
    Last Post: 07-20-2012, 05:35 PM
  4. Replies: 1
    Last Post: 08-25-2011, 11:41 AM
  5. Mail Merge document locked when opened from Access
    By retro in forum Import/Export Data
    Replies: 0
    Last Post: 08-14-2009, 03:12 AM

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