Karen Schaefer
asked on
Looking Access 2007 version to Identify LDB locks
Looking for code to identify if an LDB is active and If so Send email asking user to exit the database.
K
K
ASKER
thanks for the company will not authorize the purchase. any code available or an particular error message to indicate that the ldb is active hence it is in use.
Karen
Karen
The ldb file will not give you this info.
The ldb file contains the id of users/machines that have opened the database since the ldb file was created. Such users may have long since closed their frontend but their entry remains in the ldb file until it overwritten by another or until the ldb file is closed.
If you are not into 3rd party apps then you can build a 'flag' table into the backend. The front-end needs a timer event on a form that is always open, and in the timer event procedure you test whether the flag is set. If so you can issue a message to the user asking them to close down. You then need a procedure that sets the flag , and one that unsets it. You may put those into a separate frontend 'management' application.
The ldb file contains the id of users/machines that have opened the database since the ldb file was created. Such users may have long since closed their frontend but their entry remains in the ldb file until it overwritten by another or until the ldb file is closed.
If you are not into 3rd party apps then you can build a 'flag' table into the backend. The front-end needs a timer event on a form that is always open, and in the timer event procedure you test whether the flag is set. If so you can issue a message to the user asking them to close down. You then need a procedure that sets the flag , and one that unsets it. You may put those into a separate frontend 'management' application.
"but their entry remains in the ldb file until it overwritten by another or until the ldb file is closed."
But there is a flag in the LDB file that will tell you if user is active or has closed. FMS shows this.
But there is a flag in the LDB file that will tell you if user is active or has closed. FMS shows this.
you can use this codes
Sub ShowUserRosterMultipleUser s()
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim i, j As Long
Set cn = CurrentProject.Connection
' The user roster is exposed as a provider-specific schema rowset
' in the Jet 4.0 OLE DB provider. You have to use a GUID to
' reference the schema, as provider-specific schemas are not
' listed in ADO's type library for schema rowsets
Set rs = cn.OpenSchema(adSchemaProv iderSpecif ic, _
, "{947bb102-5d43-11d1-bdbf- 00c04fb926 75}")
'Output the list of all users in the current database.
Debug.Print rs.Fields(0).Name, "", rs.Fields(1).Name, _
"", rs.Fields(2).Name, rs.Fields(3).Name
While Not rs.EOF
Debug.Print rs.Fields(0), rs.Fields(1), _
rs.Fields(2), rs.Fields(3)
rs.MoveNext
Wend
End Sub
see this link
How to determine who is logged on to a database by using Microsoft Jet UserRoster in Access
http://support.microsoft.com/?kbid=285822
Sub ShowUserRosterMultipleUser
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim i, j As Long
Set cn = CurrentProject.Connection
' The user roster is exposed as a provider-specific schema rowset
' in the Jet 4.0 OLE DB provider. You have to use a GUID to
' reference the schema, as provider-specific schemas are not
' listed in ADO's type library for schema rowsets
Set rs = cn.OpenSchema(adSchemaProv
, "{947bb102-5d43-11d1-bdbf-
'Output the list of all users in the current database.
Debug.Print rs.Fields(0).Name, "", rs.Fields(1).Name, _
"", rs.Fields(2).Name, rs.Fields(3).Name
While Not rs.EOF
Debug.Print rs.Fields(0), rs.Fields(1), _
rs.Fields(2), rs.Fields(3)
rs.MoveNext
Wend
End Sub
see this link
How to determine who is logged on to a database by using Microsoft Jet UserRoster in Access
http://support.microsoft.com/?kbid=285822
I use this method:
Force Shut Down (Click Here)
Force Shut Down (Click Here)
Force Shut Down is an example database that shows how to get multiple Microsoft® Access® application front-ends on a network to automatically close. With Force Shut Down, you simply check a box bound to a field in a linked table, then each application front-end detects this change, displays a warning message to users, then automatically shuts down within 2 minutes. It's a great way to make sure that your database back-end is available for maintenance activities such as backup, or compact and repair.
ASKER
Regarding #39154390
Returning Schema - is there a way to modify this to look at a particular mdb other than the current db?
Unfortuantely there isn't a frontend back end situation - Yes I know that is not the best way to do things, but that is what the customer wants. I am forced to comply. I just want to look at the directory/filepath and verify if there is a ldb existing for the particular mdb. if so update the internal table with an error message.
Thanks for all the input. I remember usins a ChkDir code in the past, could you help with the code needed to review the filepath/direcoty?
K
Returning Schema - is there a way to modify this to look at a particular mdb other than the current db?
Unfortuantely there isn't a frontend back end situation - Yes I know that is not the best way to do things, but that is what the customer wants. I am forced to comply. I just want to look at the directory/filepath and verify if there is a ldb existing for the particular mdb. if so update the internal table with an error message.
Thanks for all the input. I remember usins a ChkDir code in the past, could you help with the code needed to review the filepath/direcoty?
K
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Regarding #39154390
Returning Schema - is there a way to modify this to look at a particular mdb other than the current db?
Returning Schema - is there a way to modify this to look at a particular mdb other than the current db?
try changing this
Set cn = CurrentProject.Connection
with the path to the other db.
Set cn = CurrentProject.Connection
with the path to the other db.
ASKER
ok Drawing a blank - what is the syntax from trimming off the last 4 characters of a string to be replaced with a actual value.
ie.
NFile = Right(gAPFilePath, 3) & ".ldb" I want to strip off the mdb to replace with the ldb of the actual filepath value
gAPFilePath = rs.Fields("apdbms_db").Val ue
' ShowUserRosterMultipleUser s (gAPFilePath)
'
NFile = Right(gAPFilePath, 3) & ".ldb"
' Z:\VH001\Access
If Dir(NFile, NFile) <> "" Then
MsgBox "Yes"
End If
ie.
NFile = Right(gAPFilePath, 3) & ".ldb" I want to strip off the mdb to replace with the ldb of the actual filepath value
gAPFilePath = rs.Fields("apdbms_db").Val
' ShowUserRosterMultipleUser
'
NFile = Right(gAPFilePath, 3) & ".ldb"
' Z:\VH001\Access
If Dir(NFile, NFile) <> "" Then
MsgBox "Yes"
End If
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
"Access 2007"
Just curious, are you using the ACCDB format instead of MDB?
mx
Just curious, are you using the ACCDB format instead of MDB?
mx
ASKER
because these databases where inherited and the customer did not want them converted to accdb, just in case some user were still using 2003
ASKER
thanks that did the trick. Thanks for all the input.
ASKER
could you please revisit my posting https://www.experts-exchange.com/questions/28122214/using-Variable-to-pass-object-type.html
Thanks Still need help
Thanks Still need help
http://fmsinc.com/MicrosoftAccess/monitor.asp
I use it at work daily.
mx