Error handling user not having access to backend of split database

[rivereridanus]

Hello!



I have a split database with the backend sitting on a common network file share. All users will eventually have access to the network folder in question, but currently not everyone does (it's a work in progress...). The users who do NOT yet have access to the file share are able to open their front end copy of the database, but the default form's On_Load event runs a query- at which point the users who do not have access get an error:

The Microsoft Access database engine cannot open or write to the file '<<database backend>>.accdb'. It is already opened exclusively by another user, or you need permission to view and write to its data.

Now I already know that the problem is simply that this user (and future users) doesn't have access to the network folder yet. As we have more people added to the teams that will use this, this will continue to happen every so often. Ijust want to error handle this case.

I wrote some code so that the DB would throw a friendly error in this case, and I've tested that it works for me (and I DO have access) in a situation where I temporarily renamed the tables used in the queries. However it does not work in the case where the user does not have read/write access to the network folder.

Private Sub Form_Load()
On Error GoTo Err_Handle:

//lots of code here
   Exit Sub
Err_Handle:
    MsgBox "You do not currently have access to the Turnover Database and cannot use the Turnover Tool." & vbCrLf & vbCrLf & "Please request the MCA role 'THIS IS THE ACCESS ROLE YOU NEED.", vbExclamation, "Unable to Access Database"
    DoCmd.CloseDatabase
End Sub

How can I write a friendly message that handles this case so that the users can't actually "Break" and "Debug" the code?

Thank you!!

[ranman256]

Email them the front end, it can run on their desktop.
they only need access to the backend.

[rivereridanus]

Email them the front end, it can run on their desktop.
they only need access to the backend.

Thanks for your reply! That is the current status- they all have a local copy. The issue is that some of them don't yet have access to the network folder where the backend database lives. They can open the front end copy, but the On_Load has a query in it so it breaks on that code since they can't yet read the backend db.

I'm not trying to solve that problem since I already know about it- I'm just trying to error handle the message that comes through when it happens.

[ssanfu]

To see the true error number and message, add the line in BLUE to the code:

Private Sub Form_Load()
On Error GoTo Err_Handle:

'//lots of code here
   Exit Sub
Err_Handle:

    MsgBox Err.Number & ": " & Err.Description
    
    MsgBox "You do not currently have access to the Turnover Database and cannot use the Turnover Tool." & vbCrLf & vbCrLf & "Please request the MCA role 'THIS IS THE ACCESS ROLE YOU NEED.", vbExclamation, "Unable to Access Database"
    DoCmd.CloseDatabase
End Sub

There is a difference between a user that does not have R/W access to a folder and getting an error and you renaming tables and getting an error.
Have a user that does not have R/W access try and open the dB to get the error number.



Here is an example of one of my error handlers:

login_open_error:
   Select Case Err.Number
      Case 3078, 3024, 3044    ' table not there
         MsgBox "The data files are not available.  Please locate the Back End Database now."
         btnSelectBE_Click
      Case Else
         MsgBox Err.Number & ": " & Err.Description
         Resume login_open_exit
   End Select
End Sub

[ItsMe]

That is basically it, you need to trap for the error number. So the users are already members of the Users group and you are waiting for IT to provide them with additional privileges? Are you using Universal Naming Convention (UNC) for your paths?