User count

  • Thread starter Thread starter Josie
  • Start date Start date
J

Josie

We have a Access 2002 database in a FE/BE config for 5 users.

Is there a means of counting how many users a using 1 particular table at
any one time. If it is possible advice on how to do the count would be
appreciated.

TIA
Josie
 
Hi Josie

You can count (or even list) the users who have your BE database open, but
AFAIK there is no way to narrow that down to a particular table.

Sorry!
 
Hi Graham

OK - Plan B - how would you count the users?

TIA
Josie
Graham Mandeno said:
Hi Josie

You can count (or even list) the users who have your BE database open, but
AFAIK there is no way to narrow that down to a particular table.

Sorry!
--
Graham Mandeno [Access MVP]
Auckland, New Zealand

Josie said:
We have a Access 2002 database in a FE/BE config for 5 users.

Is there a means of counting how many users a using 1 particular table at
any one time. If it is possible advice on how to do the count would be
appreciated.

TIA
Josie
Click to expand...
Click to expand...
 
Graham may have something simpler, but this should get you going if you are
using Access 2000 and later, and want to know how many *distinct* users
there are (not merely now many connections):

1. Create a table in the front-end database, with these fields:
Computer_Name Text
Login_Name Text
Connected Yes/No
Suspect_State Number
EnteredOn Date/Time
JetUserRosterID AutoNumber (primary key)
Save the table with the name "tzJetUserRoster".

2. Paste the function below into a standard module.
Modify the strPath string so it points to your back end file.

Function UserCount() As Long
Dim cnLocal As ADODB.Connection 'Current project connection.
Dim cnBackEnd As New ADODB.Connection 'Connection to back end
database.
Dim rsBEUserRoster As New ADODB.Recordset 'JET User Roster for back
end database.
Dim rsTarget As New ADODB.Recordset 'Temp table to record users
and de-dupe.
Dim strPath As String 'Full path to back end.
Dim strSql As String 'SQL string.
Dim lngKt As Long 'Loop controller.
Dim dtEnteredOn As Date 'Current date and time.

'Set this to the full path of your back end database.
strPath = "C:\Northwind2003.mdb"

'Open the JET User Roster for the back end.
cnBackEnd.Provider = "Microsoft.Jet.OLEDB.4.0"
cnBackEnd.Open "Data Source=" & strPath
Set rsBEUserRoster = cnBackEnd.OpenSchema(adSchemaProviderSpecific, , _
"{947bb102-5d43-11d1-bdbf-00c04fb92675}")

'Clear temp table, and copy the user roster in.
dtEnteredOn = Now()
Set cnLocal = CurrentProject.Connection
cnLocal.Execute "DELETE FROM tzJetUserRoster;"
rsTarget.Open "tzJetUserRoster", cnLocal, adOpenDynamic,
adLockOptimistic
Do While Not rsBEUserRoster.EOF
rsTarget.AddNew
For lngKt = 0 To 3
rsTarget(lngKt) = rsBEUserRoster(lngKt)
rsTarget!EnteredOn = dtEnteredOn
Next
rsTarget.Update
rsBEUserRoster.MoveNext
Loop
rsTarget.Close
rsBEUserRoster.Close
cnBackEnd.Close

'Get the count of the number of distinct users who are connected.
strSql = "SELECT DISTINCT Computer_Name FROM tzJetUserRoster WHERE
Connected = True;"
Set rsTarget = New ADODB.Recordset
rsTarget.Open strSql, cnLocal, adOpenKeyset
If Not (rsTarget.BOF And rsTarget.EOF) Then
rsTarget.MoveLast
UserCount = rsTarget.RecordCount
End If
rsTarget.Close

'Dereference objects
Set rsTarget = Nothing
Set rsBEUserRoster = Nothing
Set cnLocal = Nothing
Set cnBackEnd = Nothing
End Function

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Josie said:
Hi Graham

OK - Plan B - how would you count the users?

TIA
Josie
Graham Mandeno said:
Hi Josie

You can count (or even list) the users who have your BE database open,
but AFAIK there is no way to narrow that down to a particular table.

Sorry!
--
Graham Mandeno [Access MVP]
Auckland, New Zealand

Josie said:
We have a Access 2002 database in a FE/BE config for 5 users.

Is there a means of counting how many users a using 1 particular table
at any one time. If it is possible advice on how to do the count would
be appreciated.

TIA
Josie
Click to expand...
Click to expand...
Click to expand...
 
Hi Josie

That's basically the way I would do it, except I would use an array or a
collection instead of a temporary table. You can build a list of distinct
values quite easily by adding each value in turn to a collection and
ignoring any "duplicate key" errors (error 457).

Dim colUsers as New Collection
Dim sCompName as string
With rsBEUserRoster
Do Until .EOF
If !CONNECTED And IsNull(!SUSPECT_STATE) Then
sCompName = TrimZ(!COMPUTER_NAME)
colUsers.Add sCompName, sCompName
End If
.MoveNext
Loop
End With
ComputersConnected = colUsers.Count

Your error handler would simply ignore 457:
If Err = 457 then Resume Next

The COMPUTER_NAME field in the schema contains a string terminated with a
vbNullChar character. My TrimZ function simply trims such a string:

Public Function TrimZ(sString As String, _
Optional sTerminator As String = vbNullChar) As String
Dim iTerminator As Integer
iTerminator = InStr(sString, sTerminator)
If iTerminator Then
TrimZ = Left$(sString, iTerminator - 1)
Else
TrimZ = sString
End If
End Function

It would be good to have a better idea of your intentions here. If your
purpose is to gain exclusive access to the backend so you can compact it ,
or back it up, or something, then take a look at the use of the "Connection
Control" property in http://support.microsoft.com/kb/287655/EN-US/
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand


Allen Browne said:
Graham may have something simpler, but this should get you going if you
are using Access 2000 and later, and want to know how many *distinct*
users there are (not merely now many connections):

1. Create a table in the front-end database, with these fields:
Computer_Name Text
Login_Name Text
Connected Yes/No
Suspect_State Number
EnteredOn Date/Time
JetUserRosterID AutoNumber (primary key)
Save the table with the name "tzJetUserRoster".

2. Paste the function below into a standard module.
Modify the strPath string so it points to your back end file.

Function UserCount() As Long
Dim cnLocal As ADODB.Connection 'Current project
connection.
Dim cnBackEnd As New ADODB.Connection 'Connection to back end
database.
Dim rsBEUserRoster As New ADODB.Recordset 'JET User Roster for back
end database.
Dim rsTarget As New ADODB.Recordset 'Temp table to record users
and de-dupe.
Dim strPath As String 'Full path to back end.
Dim strSql As String 'SQL string.
Dim lngKt As Long 'Loop controller.
Dim dtEnteredOn As Date 'Current date and time.

'Set this to the full path of your back end database.
strPath = "C:\Northwind2003.mdb"

'Open the JET User Roster for the back end.
cnBackEnd.Provider = "Microsoft.Jet.OLEDB.4.0"
cnBackEnd.Open "Data Source=" & strPath
Set rsBEUserRoster = cnBackEnd.OpenSchema(adSchemaProviderSpecific, , _
"{947bb102-5d43-11d1-bdbf-00c04fb92675}")

'Clear temp table, and copy the user roster in.
dtEnteredOn = Now()
Set cnLocal = CurrentProject.Connection
cnLocal.Execute "DELETE FROM tzJetUserRoster;"
rsTarget.Open "tzJetUserRoster", cnLocal, adOpenDynamic,
adLockOptimistic
Do While Not rsBEUserRoster.EOF
rsTarget.AddNew
For lngKt = 0 To 3
rsTarget(lngKt) = rsBEUserRoster(lngKt)
rsTarget!EnteredOn = dtEnteredOn
Next
rsTarget.Update
rsBEUserRoster.MoveNext
Loop
rsTarget.Close
rsBEUserRoster.Close
cnBackEnd.Close

'Get the count of the number of distinct users who are connected.
strSql = "SELECT DISTINCT Computer_Name FROM tzJetUserRoster WHERE
Connected = True;"
Set rsTarget = New ADODB.Recordset
rsTarget.Open strSql, cnLocal, adOpenKeyset
If Not (rsTarget.BOF And rsTarget.EOF) Then
rsTarget.MoveLast
UserCount = rsTarget.RecordCount
End If
rsTarget.Close

'Dereference objects
Set rsTarget = Nothing
Set rsBEUserRoster = Nothing
Set cnLocal = Nothing
Set cnBackEnd = Nothing
End Function

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Josie said:
Hi Graham

OK - Plan B - how would you count the users?

TIA
Josie
Graham Mandeno said:
Hi Josie

You can count (or even list) the users who have your BE database open,
but AFAIK there is no way to narrow that down to a particular table.

Sorry!
--
Graham Mandeno [Access MVP]
Auckland, New Zealand

We have a Access 2002 database in a FE/BE config for 5 users.

Is there a means of counting how many users a using 1 particular table
at any one time. If it is possible advice on how to do the count would
be appreciated.

TIA
Josie
Click to expand...
Click to expand...
Click to expand...
 
Back
Top