best way to code for multi-user database?

[Mitch]

I come from a VB, SQL Server/Oracle background and am pretty new to Access
developement. I recently developed a small database using A2K that was not
supposed to be used by more than 1-2 users concurrently. Well, it has
turned into a 6-12 user app (depending on the size of the office where it is
deployed) and database corruption has become a major issue. The database
can become corrupt up to 6 times daily, at which point all users must exit
the DB while it is compacted/repaired. I understand that network
instability can play a large part in corruption, but I have no control over
the LAN. So therefore I want to concentrate on making sure the part I do
control (the application) is not contributing to the instability.

I'm pretty sure it was poor application design on my part that is
contributing to the instability

Data access was written using ADO hierarchical (shaped) recordsets and the
4.0 Jet provider. I maintain a module
level connection to the database for each data entry form that is open by
the user. Since the average user keeps 2 forms open at one time, I probably
have 12 to 24 ADO connections established to the database at any given time.
(2 connections per user multiplied by 6-12 users). Obviously, my main
problem I keeping too many persistent connections to Access.

I tried to shorten my connection open time by disconnecting my recordsets,
but this MS knowledge base issue explains that disconnected shaped
recordsets do not release their connection to the database
http://support.microsoft.com/default.aspx?scid=kb;en-us;288409. I tested it
myself in Access 2000 and verified that this holds true.

So short of re-writing my data access code entirely, what are my options for
minimizing database connections?

Thanks for any feedback you can provide!

 

[Tony Toews]

I come from a VB, SQL Server/Oracle background and am pretty new to Access
developement. I recently developed a small database using A2K that was not
supposed to be used by more than 1-2 users concurrently. Well, it has
turned into a 6-12 user app (depending on the size of the office where it is
deployed) and database corruption has become a major issue. The database
can become corrupt up to 6 times daily, at which point all users must exit
the DB while it is compacted/repaired. I understand that network
instability can play a large part in corruption, but I have no control over
the LAN. So therefore I want to concentrate on making sure the part I do
control (the application) is not contributing to the instability.

I'm pretty sure it was poor application design on my part that is
contributing to the instability

Strongly disagree.

6 times per day? Very likely the OpLocks problem. See the Access
Corruptions FAQ at my website.

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

 

[david epsom dot com dot au]

Mitch, it looks like you are using a FE/BE split architecture, but
it's not clear if you are using a shared file for the FE. Don't use
shared files if you can avoid it: there may be problems with the
file server that haven't been fixed yet.

(david)

 

[Albert D. Kallal]

I am sure you have the basic stuff setup like each user having a front end.
They also should get a mde. Often, common corruption occurs when you allow
multiple users into the same front end. You wan to avoid this.

Also, it is not clear why you are using shaped record sets in ms-access? I
don't see how they are of much use? Your classic one to many forms are going
to be forms with sub-forms, and thus the addition of child records is
handled automatically for you. If you are loading things up using a shaped
ado reocrdset, then you likely are wasting resources here that you don't
have to. Not exactly clear here what you are doing with the shaped
recordsets.

Also, I have never heard of having extra connections open to be a problem at
all. If each user has 5, or 30 connections, it should make little, or no
difference.

I understand that network
instability can play a large part in corruption, but I have no control over
the LAN.

Well, if the network is bad, then nothing you do will fix this problem short
of dumping the JET database engine. If users, or the admin is killing pc's,
or re-booting pc's while the application is running, you can have a 100%
perfect network, and still corrupt the heck out of the mdb files if the
users are abusing the system. If any pc's are freezing up, or are being
re-booting while your application is running then you have to fix this
behaviour..as your hardware may be 100% just fine. Changing your code will
not fix those bad users! Same goes for power off switches. None of this kind
of behaviour is acceptable when using a file share.

I maintain a module
level connection to the database for each data entry form that is open by
the user.

Not sure what you mean by the above? There is NOTHING wrong with using bound
forms in ms-access as long as you have a good design.

Obviously, my main
problem I keeping too many persistent connections to Access.

Why? If the above connections are not doing anything..it don't matter.

So, #1, check that each user gets their own front end. This seems to be the
#1 fix, and you can't reliability run multiple users in the same front end.

#2, check your users behaviour and make sure pc's are not being re-started,
or that ms-access is being un-gracefully terminated.

Further, check out Tony's faq on the whys, and how to prevent corruptions:

http://www.granite.ab.ca/access/corruptmdbs.htm