Prevent user from opening multiple access instances 1

[fotis]

In an access database how can I block a user to open multiple instances of the same database?

Thanks
Fotis

 

[KyleS]

Just off the top of my head and probably not the most elegant way, but I think I would put a hidden table in the dB, let's call it "tblLog" for the moment. Create a new form - let's call it "frmLog" and hide it as well. Now when your dB opens up, you should use your autoexec macro (or other startup form if you prefer) to open frmLog in hidden mode. The OnOpen of the new form will put an entry into your dB for the person logging on by use of this code:

Private Sub Form_Open()
If Len(Nz(DLookup("[UserName]","[tblLog]","[tblLog].[UserName] = '" & Environ$("UserName&quot & "'", "&quot) = 0 Then
DoCmd.RunSQL "INSERT INTO tblLog( UserName ) SELECT '" & Environ$("UserName&quot & "' As UserName_ FROM
tblLog;"
Else
Msgbox "You are already logged into this application!",vbCritical+vbOKOnly,"Multiple Log-ins"
Application.Quit
End If
End Sub

Now, for the form close, put this:

Private Sub Form_Close()
DoCmd.RunSQL "DELETE * FROM tblLog WHERE tblLog.UserName = '" & Environ$("UserName&quot & "'"
Exit Sub


Sorry for any type-O's as I'm writing this free-hand... Kyle

 

[KyleS]

Wow, only 2 minutes and I found an error:

Replace:

Private Sub Form_Open()
If Len(Nz(DLookup("[UserName]","[tblLog]","[tblLog].[UserName] = '" & Environ$("UserName&quot & "'", "&quot) = 0 Then
DoCmd.RunSQL "INSERT INTO tblLog( UserName ) SELECT '" & Environ$("UserName&quot & "' As UserName_ FROM
tblLog;"
Else
Msgbox "You are already logged into this application!",vbCritical+vbOKOnly,"Multiple Log-ins"
Application.Quit
End If
End Sub

With:

Private Sub Form_Open()
If Len(Nz(DLookup("[UserName]","[tblLog]","[tblLog].[UserName] = '" & Environ$("UserName&quot & "'", "&quot) = 0 Then
DoCmd.RunSQL "INSERT INTO tblLog( UserName ) SELECT TOP 1 '" & Environ$("UserName&quot & "' As UserName_ FROM
tblLog;"
Else
Msgbox "You are already logged into this application!",vbCritical+vbOKOnly,"Multiple Log-ins"
Application.Quit
End If
End Sub Kyle

 

[fotis]

Thanks for the assistance, though it doesn't seem to work as it appends 0 rows. I don't know whether Access version (Access2000) or Windows version (Windows 2000) has to do anything with this.

Thanks
Fotis

 

[KyleS]

Oh yeah, that. In order to run and append query you need to have a table to base it off of, and since the table here doesn't have any records, the query won't give any records. So add an empty entry and then try this. Kyle

 

[StarPassing]

Try using the Preventing multiple instances of a database
code found at:

http://www.mvps.org/access/api/api0041.htm

I have it in all my prgrams.

Tom

 

[fotis]

Thanks both!