Current users in database

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

If this is not the correct forum please advise.
I want to check whether it is possible to open a database exclusively by
checking which users currently have the database open. There appears to be
some code for A97 using MSLDBUSR.DLL but this DLL does not appear to register
under Windows XP.
Another solution checks the current users for the current database using the
GUID but I want to check another, user selected, database.
Can anyone put me on track for a solution to this problem please?
Terry
 
How about checking for the existence of an LDB file?
Of course this won't exist if someone else has the database opened
exclusively, but then you'd get a trappable error, wouldn't you?

- Turtle
 
It does not provide enough information. I would like to report the name &
computer of the logged on database user so action can be taken to log the
users out. I believe it is also possible to lock the database from additional
users.
 
What version of Access are you using?
What sort of action are you contemplating to log users out?
Simply disconnecting them could easily lead to database corruption.

I've seen a neat little programming trick where the database is programmed
to check a separate file on start-up. The DBA can alter that file to either
permit or prohibit further logons.

HTH
- Turtle
 
Access version: 2k3. I have some code for A97 but that does not work with
2k3. It relies on a DLL which refuses to register with XP.
Log Out Users: No, just advise the user who is currently using this database
of the name and computer of the logged in users.
Yes! I have seen something in the KB some time ago to allow you to lock the
database prohibiting additional users logging on. I will be scanning the KB
for that article soon. I do believe the security within Access can provide
the information & you do not have to scan an outside file.
 
Have you checked article KB 198755?
It's only listed as applying to Access 2000, but it would be unusual for an
existing, documented feature to be taken out of later versions. (Not that
it never happens.)
On the other hand, it's unusual for the KB not to be updated to show later
versions that the same article applies to.
IOW, I don't know whether it will work in 2K3.
My best advice is to give it a try...
And post your results back, so others can learn, too.

HTH
- Turtle
 
Thanks
IOW?
I was reading KB 198756, the next KB article after the one you referenced as
well as 285822 which refers to A2k2 & A2k3. KB 198756 includes connection
control. Both use ADO which I have not used much, preferring DAO which has
given me far more control over what I have needed to date. Both articles
refer to the CURRENT database/connection where I want another, user selected,
database/connection. I am reading up on ADO to get up to speed and to change
the articles to another database/connection
I have also been looking at the LDB itself and several articles which
describe it. As it is always in the same directory as the database and has
the same name it is easily located & has a simple structure. You can easily
examine it in Word.
Using ADO also allows you to check the status of connections as well as
checking for problem connections &, therefore, is probably worth pursuing

The following code is my first pass:
'=======================================
Public Sub ShowCurrentUsers(Optional sDatabase As String)
'Lists the Current Users in the public array gvUsers()
'For sDatabaseName. If not specified the current database is assumed

Dim Rs As ADODB.Recordset
Dim cn As New ADODB.Connection
Dim Rs As New ADODB.Recordset
Dim i, j As Long

If IsMissing(sDatabaseName) Then
Set cn = CurrentProject.Connection
Else
'This is the code I need
End If

' 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

'I assume the following reference applies to any connection
Set Rs = cn.OpenSchema(adSchemaProviderSpecific, ,
"{947bb102-5d43-11d1-bdbf-00c04fb92675}")

'Output the list of all users in the database to the global array
Rs.MoveFirst
i = 0
While Not Rs.EOF
ReDim Preserve gvUsers(i + 1, 3)
'Computer name
gvUsers(i, 0) = Trim(Left(Rs.Fields(0), InStr(Rs.Fields(0), Chr(0))
- 1))
'User name
gvUsers(i, 1) = Trim(Left(Rs.Fields(1), InStr(Rs.Fields(1), Chr(0))
- 1))
'Connection status
gvUsers(i, 2) = Trim(Str(Rs.Fields(2)))
'Connection State
gvUsers(i, 3) = Trim(Rs.Fields(3) & "")
i = i + 1
Rs.MoveNext
Wend
Set Rs = Nothing
End Sub
'=======================================
Thanks for your help
Terry
 
IOW = In other words
set cn=new ADODB.Connection
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
sDatabase
cn.open
(Assuming sDatabase is the full path and filename for your target database)

I don't recommend opening Access files in Word.
I haven't actually experienced this myself, but I've heard that this can
cause irretrievable corruption.
I'm sure you realize by now that the LDB file lists all users who have used
the MDB since the LDB was created, along with information on their status.
Users who have already logged out are still listed, until the LDB is
destroyed.
Opening the file in Word may let you read the user and machinename, but all
you see for the status is little boxes.

HTH
- Turtle
 
Back
Top