Answered embedded.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users -
http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Rob Geraghty" <th********@yah oo.com> wrote in message
news:b7******** *************** ***@posting.goo gle.com...
I've just spent some time looking through FAQ sites and searching the
google archives of this newsgroup, but I still haven't been able to
find a clear explanation of an issue with multi-user databases.
Essentially I have two questions;
1) Does the system.mdw file have any significance to multi-user
sharing of an Access 97 database other than security?
No.
2) Can any number of users open an Access 97 database using the same
account from the system.mdw without corrupting the database?
Yes. That's normal. If you create a user named say "Level2", and assign the
desired priviliges, several users can all log in as Level2 at the same time.
What I have been doing up until now is trying to ensure that each user
has a unique account in the system.mdw and that any user connecting to
the database uses the same system.mdw. If it's not necessary for each
user to have a unique account, it would be much simpler to have a
single generic user account and a single standard desktop shortcut to
the front end.
Well, CurrentUser() returns the name of the logged in user, which may be
informative if you want to do it that way, but it's not necessary and you
can get the Computer_Name from the Jet User Roster if you need it.
I can't find any resources which clearly explain the locking system in
Access. My impression is that when a user connects to the database,
an LDB file is created which contains the details of the connection.
The information stored in the LDB file as shown by the Microsoft LDB
viewer tool appears to be related to the computer name making the
connection *not* the user account from the system.mdw, so presumably
it shouldn't matter if the same account is used on several different
computers because the locking is unique to the PC name.
Rats: you're using A97. The Jet User Roster is for A2000 and later (uses
ADO), but it shows the Computer_Name, Login_Name, Connected (y/n), and a
Suspect_State code.
What *does* seem to cause problems is failing to use a system.mdw at
all; which means that several users of an unsecured database are
connecting as "admin" from the system.mdw in c:\windows\syst em32. In
terms of rights to objects, security is not of particular importance
in the databases in question.
No, there is no problem with that at all. In fact, it's a very good example
of lots of users conneting with the same log-in name. Personally I find
Access security unnecessary for most installations, and there are literally
millions of Access databases running every day without setting up security,
i.e. using the default system.mdw, so that scenario is probably the most
"tested" database setup on the planet.
Having standard desktop shortcuts which include the login account as
part of the command line would make distribution of the applications
much simpler.
Yes. And of course the system.mdw in the shortcut must be the one on the
server, so you only have one file to manage.
If anyone has in depth knowledge of how the system.mdw file and the
ldb files behave, please let me know whether the setup for these
databases can be simplified...
If you do have access to Access 2000 or later, the function below reads it
into an Access so you can examine its contents. Return value is the number
of distinct users currently connected.
Function UserCount() As Long
Dim cnLocal As ADODB.Connectio n 'Current project connection.
Dim cnBackEnd As New ADODB.Connectio n '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:\Data\Northw ind2003.mdb"
'Open the JET User Roster for the back end.
cnBackEnd.Provi der = "Microsoft.Jet. OLEDB.4.0"
cnBackEnd.Open "Data Source=" & strPath
Set rsBEUserRoster = cnBackEnd.OpenS chema(adSchemaP roviderSpecific , , _
"{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 "tzJetUserRoste r", cnLocal, adOpenDynamic,
adLockOptimisti c
Do While Not rsBEUserRoster. EOF
rsTarget.AddNew
For lngKt = 0 To 3
rsTarget(lngKt) = rsBEUserRoster( lngKt)
rsTarget!Entere dOn = 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.MoveLa st
UserCount = rsTarget.Record Count
End If
rsTarget.Close
'Dereference objects
Set rsTarget = Nothing
Set rsBEUserRoster = Nothing
Set cnLocal = Nothing
Set cnBackEnd = Nothing
End Function
