By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,272 Members | 1,437 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,272 IT Pros & Developers. It's quick & easy.

Access 97 - Multiuser databases and corruption

P: n/a
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?

2) Can any number of users open an Access 97 database using the same
account from the system.mdw without corrupting the database?

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.

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.

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\system32. In
terms of rights to objects, security is not of particular importance
in the databases in question.

Having standard desktop shortcuts which include the login account as
part of the command line would make distribution of the applications
much simpler.

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...

Rob
Nov 13 '05 #1
Share this Question
Share on Google+
16 Replies


P: n/a
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********@yahoo.com> wrote in message
news:b7**************************@posting.google.c om...
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\system32. 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.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:\Data\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
Nov 13 '05 #2

P: n/a
th********@yahoo.com (Rob Geraghty) wrote:
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.


What do you mean by "the front end"? Each user should have their own - if
you have multiple users accessing the same FE then this might cause
corruption.

Regards,
Keith.
www.keithwilby.com
Nov 13 '05 #3

P: n/a
Keith Wilby <ke*********@AwayWithYerCrap.com> wrote in message news:<Xn************************@10.15.188.42>...
th********@yahoo.com (Rob Geraghty) wrote:
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.

What do you mean by "the front end"? Each user should have their own - if
you have multiple users accessing the same FE then this might cause
corruption.


Why would it cause corruption? We have a stack of databases where
users open the same front end file on the server, and they don't get
corrupted all the time. The usual reason they become corrupted is
that an individual PC had a network problem and was rebooted without
exiting the application in a clean way.

Using a single front end on the server eliminates the need to
distribute the files - you only need to distribute the shortcut.
Updating the front end means changing it in one place. Loading the
front end is a little slower but on a 100Mb/s switched network the
speed difference isn't worth worrying about.

When the network was slower, distributing the front end files made
sense, especially if they were large. But with a fast network, I
can't see a reason for it.

Now before people start flaming me and saying "there's your problem",
I'd prefer to hear an explanation *why* opening several copies of the
front end from the server would cause corruption.

Having said that, as I mentioned we've had a number of databases
operating with split front end back end on the server and shortcuts on
the desktop for years - without significant problems of corruption.
The point of my original post was to figure out if the system could be
further simplified by having a single generic user account in the MDW
file, and put the account name and password into the shortcut. Since
the lock file seems to record the details of the computer connecting
to the database, not the account used, it should be OK.

Regards,
Rob

Rob
Nov 13 '05 #4

P: n/a
Once is sufficient, isn't it?

Darryl Kerkeslager

"Rob Geraghty" <th********@yahoo.com> wrote:
they don't get
corrupted all the time. The usual reason they become corrupted is
that an individual PC had a network problem and was rebooted without
exiting the application in a clean way.


Nov 13 '05 #5

P: n/a
Hi Rob. I'm no expert but the people who are say that a frontend on
each user's machine is the way to go. Tony Toews has written an
excellent utility which makes the task of distributing the frontend
child's play. You can get it here:
http://www.granite.ab.ca/access/autofe.htm

HTH.
Nov 13 '05 #6

P: n/a
th********@yahoo.com (Rob Geraghty) wrote:
Using a single front end on the server eliminates the need to
distribute the files - you only need to distribute the shortcut.


Using a single FE for multi-user purposes is asking for trouble, you've
been very lucky so far. The easiest way to give your users the FE is to
have your shortcut execute a batch file which downloads it to their local
drive and then opens it. They each get their own copy of the latest FE
version and will get a performance benefit too.

Regards,
Keith.
www.keithwilby.com
Nov 13 '05 #7

P: n/a
th********@yahoo.com (Rob Geraghty) wrote:
Why would it cause corruption?


Access updates the FE MDB/MDEs while they're in use. Things such as query plans,
filters, where clauses and such. A97 was much stabler in sharing FEs compared to
A2000 and newer.

Also to put a new FE in place on the server requires waiting until everyine is out of
it or kicking them out.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Nov 13 '05 #8

P: n/a
Keith Wilby <ke*********@AwayWithYerCrap.com> wrote in message news:<Xn************************@10.15.188.42>...
th********@yahoo.com (Rob Geraghty) wrote:
Using a single front end on the server eliminates the need to
distribute the files - you only need to distribute the shortcut.

Using a single FE for multi-user purposes is asking for trouble, you've
been very lucky so far.


Hi Keith. OK, so it's "asking for trouble", but it doesn't tell me
is *why* it's a problem. As I mentioned earlier, we've had quite a
few databases running that way over a number of years without
persistent corruption. There's also some databases which are running
in Citrix using the same file. It's essentially the same thing as
sharing the front end file on a server.

The main point of my original post wasn't about sharing a single front
end. It was about sharing a single generic account in the system.mdw
so a shortcut can be distributed to the desktop. Since the lock file
records the computer name, this should be ok. I want to eliminate the
need for the users to "login" to MS Access, which is possible given a
generic account.

Regards,
Rob
Nov 13 '05 #9

P: n/a
"Darryl Kerkeslager" <Ke*********@comcast.net> wrote in message news:<Lr********************@comcast.com>...
Once is sufficient, isn't it? "Rob Geraghty" <th********@yahoo.com> wrote:
they don't get
corrupted all the time. The usual reason they become corrupted is
that an individual PC had a network problem and was rebooted without
exiting the application in a clean way.


There's not much I can do in MS Access to prevent servers abending or
network switches crashing. I've had very few situations where the
database back end couldn't be repaired by MS Access, avoiding
restoring from tape.

If I was running databases on a server without nightly backups, I'd be
asking for trouble.
Nov 13 '05 #10

P: n/a
cq*******@volcanomail.com (Wayne Aprato) wrote in message news:<4d**************************@posting.google. com>...
Hi Rob. I'm no expert but the people who are say that a frontend on
each user's machine is the way to go. Tony Toews has written an
excellent utility which makes the task of distributing the frontend
child's play. You can get it here:
http://www.granite.ab.ca/access/autofe.htm


Thanks for the suggestion Wayne! However, software distribution isn't
a huge problem; we can use Zen for that. The Access 97 databases are
legacy applications, so the simpler they are to distribute, the
better. Putting the front end on the workstations actually causes
headaches with security, so it's easier to handle the security in one
location on the server and just put the shortcuts on the desktop.

Perhaps I'll find out later why everyone is saying "never share the
front end" but at the moment it works for me! If any of these
databases had a significant number of concurrent users, I'd probably
take the distributed approach.

Rob
Nov 13 '05 #11

P: n/a
th********@yahoo.com (Rob Geraghty) wrote:
It was about sharing a single generic account in the system.mdw
so a shortcut can be distributed to the desktop. Since the lock file
records the computer name, this should be ok. I want to eliminate the
need for the users to "login" to MS Access, which is possible given a
generic account.


Hi again Rob,

I use generic accounts and common WIFs all the time and have never
experienced any corruption* and I don't see a problem with including the
username and password in the command line (aside from the obvious security
implications).

*All my users have their own copy of the FE.

Regards,
Keith.
www.keithwilby.com
Nov 13 '05 #12

P: n/a
But I've been running three separate FE/BE mdb setups for about a year,
approx 15 users each LAN, with each user having their own copy of the FE
mdb. One office in particular has had a never-ending (still hasn't ended)
series of electrical and network problems, and yet the BE has *never* been
corrupted or damaged in any way. The FE has been corrupted on the
individual PCs, but never the master copy of the FE on the server (and I use
the term 'server' loosely - Pentium II 128 MB), so restoring a corrupted FE
just involves deleting the individual's copy of the FE, and we're good to go
(I use Tony Toew's Auto FE update utility
http://www.granite.ab.ca/access/autofe.htm).

Really, for me, even one incidence of BE corruption would be too much. I'm
not paid to be a full-time IT person, and I can't necessarily drop my
regular work at a moment's notice to restore a corrupted BE - and yet that
would leave 15 users out in the cold. Lack of problems allows me to do the
Access stuff on my schedule, not the network's.
Darryl Kerkeslager

"Rob Geraghty" <th********@yahoo.com> wrote in message
news:b7**************************@posting.google.c om...
"Darryl Kerkeslager" <Ke*********@comcast.net> wrote in message

news:<Lr********************@comcast.com>...
Once is sufficient, isn't it?

"Rob Geraghty" <th********@yahoo.com> wrote:
they don't get
corrupted all the time. The usual reason they become corrupted is
that an individual PC had a network problem and was rebooted without
exiting the application in a clean way.


There's not much I can do in MS Access to prevent servers abending or
network switches crashing. I've had very few situations where the
database back end couldn't be repaired by MS Access, avoiding
restoring from tape.

If I was running databases on a server without nightly backups, I'd be
asking for trouble.

Nov 13 '05 #13

P: n/a
Tony Toews <tt****@telusplanet.net> wrote in message news:<18********************************@4ax.com>. ..
th********@yahoo.com (Rob Geraghty) wrote:
Why would it cause corruption? Access updates the FE MDB/MDEs while they're in use.
Things such as query plans, filters, where clauses
and such. A97 was much stabler in sharing FEs
compared to A2000 and newer.


OK, except the databases in question don't have any temporary tables
in the front end, and don't use filters. Is there any documentation
anywhere on the internet which describes what dynamic components exist
within the front end of a split database? We only had the runtime kit
for Access 97 so these are legacy applications. Thanks for the
heads-up, because I'll make sure I run a repair and compact on the
front end anytime I repair the back end of a database.
Also to put a new FE in place on the server requires
waiting until everyine is out of it or kicking them out.


Sure, but with the LDBView program, it's easy to find the computers
connected and ask the users to close the database. As mentioned
above, these are static applications, so no much chance of the front
end being replaced.

For what it's worth, of all the databases we're running, the worst
performing application in terms of data corruption is a VB front end
to an Access 97 back end. The only other access database that
regularly gets repaired is one which includes an ODCB connection to a
"mainframe" database. So I haven't seen any evidence of database
corruption in purely Access 97 systems with shared front ends -
provided that everyone opening the database used the same MDW file.

Having said that, it probably depends on how sophisticated the code
and queries are in the front end. None of thse databases are
especially complex, and none would have more than a handful of
concurrent users.

Thanks to everyone who has responded to the thread. I'll pick a
database and try setting it up to share a generic account.

Rob
Nov 13 '05 #14

P: n/a
th********@yahoo.com (Rob Geraghty) wrote:
So I haven't seen any evidence of database
corruption in purely Access 97 systems with shared front ends -
provided that everyone opening the database used the same MDW file.
A97 is much more stable than A2000 in sharing the FE.
Having said that, it probably depends on how sophisticated the code
and queries are in the front end. None of thse databases are
especially complex, and none would have more than a handful of
concurrent users.


This is also a factor.

Migrating to A2000 though will cause a lot more problems.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Nov 13 '05 #15

P: n/a
I am in the process of relocating a 300 mb Access 2000 FE/BE
application from our LAN to a Citrix server. The application has been
used for 3 years with 7 current users. It continues to grow, slow and
I hope to speed it up. Reading the threads has made me aware of the
perils of using a shared front end. Every user has Access on their PC,
but they open the FE on the LAN. Even though the Access version of the
db matches what is on their PC and not necessarily what was installed,
they are still sharing the FE? Looking at Tony's AutoFE Updater I
see how easy it would be to maintain separate FEs on people's PCs.
I'm guessing we haven't had corruption problems because while users
read shared data, they only write to records in their customer group.
Thus no 2 users write to the same record. Our administrator thinks we
need just 1 FE. If I want separate FEs on the citrix, I need to
install Access in 7 user folders. Does this mean I need to buy 6 more
copies of Access even though every user has a local copy on their PC?
If so, I'll have to stick with a shared FE.

Nov 13 '05 #16

P: n/a
"neptune" <bs**********@hotmail.com> wrote in message
news:11*********************@f14g2000cwb.googlegro ups.com...
I am in the process of relocating a 300 mb Access 2000 FE/BE
application from our LAN to a Citrix server. The application has been
used for 3 years with 7 current users. It continues to grow, slow and
I hope to speed it up. Reading the threads has made me aware of the
perils of using a shared front end. Every user has Access on their PC,
but they open the FE on the LAN. Even though the Access version of the
db matches what is on their PC and not necessarily what was installed,
they are still sharing the FE? Looking at Tony's AutoFE Updater I
see how easy it would be to maintain separate FEs on people's PCs.
I'm guessing we haven't had corruption problems because while users
read shared data, they only write to records in their customer group.
Thus no 2 users write to the same record. Our administrator thinks we
need just 1 FE. If I want separate FEs on the citrix, I need to
install Access in 7 user folders. Does this mean I need to buy 6 more
copies of Access even though every user has a local copy on their PC?
If so, I'll have to stick with a shared FE.


On a terminal server you would install Access once, not for every user. They
would each be running their own instance of Access from the common installation
folder.

Yes, every user needs a Citrix client license, a Terminal Server client license,
and a license for Access (or any other software they use on the server), but
their existing local installations of Access will cover them for the Access
license.
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com
Nov 13 '05 #17

This discussion thread is closed

Replies have been disabled for this discussion.