0

I am currently working within Access 2013. I am building a database for a small group of people, in which the database will be in a shared folder (split database). The problem that I am having with the database is that, I can login successfully through the login screen, but when someone else tries to login they will get "Run-time error '3051': The Microsoft Jet database engine cannot open the file 'xxx.mdb'. It is already opened exclusively by another user, or you need permission to view its data". Also, when they bypass the login in screen by going to design view (a function only used for testing), they cannot open a table.

My code: 

Option Compare Database
Option Explicit

Private Sub btnLogin_Click()
Dim rs As Recordset

     Set rs = CurrentDb.OpenRecordset("tblUser", dbOpenSnapshot, dbReadOnly)

     rs.FindFirst "UserName='" & Me.txtUserName & "'"

If rs.NoMatch = True Then
    Me.lblWrongUser.Visible = True
    Me.txtUserName.SetFocus
    Exit Sub
End If
     Me.lblWrongUser.Visible = False

If rs!Password <> Nz(Me.txtPassword, "") Then
    Me.lblWrongPass.Visible = True
    Me.txtPassword.SetFocus
    Exit Sub
End If
     Me.lblWrongPass.Visible = False
     DoCmd.OpenForm "frmPersonal Information"
     DoCmd.Close acForm, Me.Name
End Sub

When running the debug it is showing that something is wrong with:
Set rs = CurrentDb.OpenRecordset("tblUser", dbOpenSnapshot, dbReadOnly)

Is there anything that I am doing wrong or do I need to split the database and turn the front end into a ACCDE file?

Orange56
  • 119
  • 7
  • Are you sure it isn't just a folder permissions issue? Does the "someone else" have the same permissions on the folder that you do? – David Cram Jul 22 '16 at 12:13
  • Is `tblUser` a link to a table in another database instead of a local table in the database which contains the running VBA code? – HansUp Jul 22 '16 at 12:15
  • @DavidCram it may be the permissions within the folder, I have to look at it. But I had a previous version of this same database without the login screen (ACCDE file) and it was working fine. – Orange56 Jul 22 '16 at 12:24
  • @HansUp tblUser is a linked table within the back end database. – Orange56 Jul 22 '16 at 12:26
  • Good. When the user gets that error, can they close Access and then open that back end database and then that table? – HansUp Jul 22 '16 at 12:27
  • @HansUp I have the back end database encrypted and only myself and the manager will now the password. – Orange56 Jul 22 '16 at 12:32
  • Please note that we don't have memorized every Runtime error number (well, perhaps HansUp has :p). So include the full error message in your question - should be `Run-time error '3051': The Microsoft Jet database engine cannot open the file 'xxx.mdb'. It is already opened exclusively by another user, or you need permission to view its data` – Andre Jul 22 '16 at 12:34
  • @Andre, will do next time. – Orange56 Jul 22 '16 at 12:35
  • So give **one** of those users the database password. Can they open the backend database in the situation I described earlier? Note you can change the database password again after you fix the problem. – HansUp Jul 22 '16 at 12:35
  • @HansUp Yes, they can successfully log into the back end and view the table. While I am also logged onto the back end. – Orange56 Jul 22 '16 at 12:41
  • When somebody opens any form in design mode (manually or using VBA), it locks the database exclusively. Please check the rest of startup code for opening any forms with acDesign parameter. – Sergey S. Jul 22 '16 at 12:43
  • @Orange56 Did Sergey's suggestion resolve your problem? – HansUp Jul 22 '16 at 12:53
  • Currently working on that now, should I remove the DoCmd.OpenForm "frmPersonal Information". – Orange56 Jul 22 '16 at 12:56
  • I have figured it out, I was splitting the database within my local drive and then moving it to the shared folder and the front end database was not linking to my backend database, which is why my login screen could not find tblUser I suppose. Can I leave the backend database on my local drive, and keep the front end in the shared folder? Thank you, all for your help – Orange56 Jul 22 '16 at 13:04
  • Open the front-end database and use the linked table manager to make the links point to the back-end database which is kept in the shared folder. – HansUp Jul 22 '16 at 13:05
  • 1
    Ultimately you should give each user their own copy of the front-end database. It sounds like currently everyone is opening that one front-end on the share. You shouldn't want to do that long term. – HansUp Jul 22 '16 at 13:10
  • Seems your application has its own custom authentication strategy, separate from Windows authentication. If you can possibly leverage Windows authentication, your application would be simpler. – HansUp Jul 22 '16 at 13:12
  • @HansUp is it possible that I can email the front end to each person and they can use the link table manager? What are the steps to using Windows authentication? – Orange56 Jul 22 '16 at 13:38
  • Why have every user update the links? Do that once in the master copy of the front-end and then distribute that copy to the users. – HansUp Jul 22 '16 at 13:42
  • You can use `CreateObject("WScript.Network").Username` to securely retrieve the name of the current Windows user. See how Andre does it [here](http://stackoverflow.com/a/32565953/77335) – HansUp Jul 22 '16 at 13:54
  • @HansUp where exactly would I place, CreateObject("WScript.Network").Username, would it fine If a place it at the end of the login VBA code? – Orange56 Jul 22 '16 at 13:56
  • I swapped out that comment, @Orange56. Follow the link in the newer comment. – HansUp Jul 22 '16 at 13:57
  • @HansUp alright thank you. – Orange56 Jul 22 '16 at 14:00

0 Answers0