PDA

View Full Version : MS Access 2000 Mutliuser database



efty
27th June 2003, 12:42
I am working on a project for a company in Access 2000. Despite my efforts to convince them to use another more robust database they still insist to use access because they want the ability to change the design themselves once I am gone. Anyway, after discussing my concerns about the possible dangers using access in a multiuser environment they agreed to limit the number of simultaneous users to approximately ten, a number I expect to grow exponentially once the project is done. I expect perhaps even 30 simultaneous users either updating records or running various queries.

Now my question to any MS Accesss experts out there.
Last time I checked MS Access had a lot of problems with multiuser setups. Has the situation changed at all with Ms Access 2000 or 2002? From your own experience what is the typical workload a multiuser access database can handle on a fairly messy local area network. What can I do to further minimize the possibility of corruption. Record locking will not be an issue in this case since the majority of users will be simply reading and not adding or editing data.

cjolley
27th June 2003, 13:14
They asked for advice, & won't follow it.
Write the thing and walk away.
You might be able to negotiate some pretty good terms when they ask you to fix it.
chuck (the meany)

M|x
27th June 2003, 15:59
Originally posted by efty
Despite my efforts to convince them to use another more robust database they still insist to use access because they want the ability to change the design themselves once I am gone. .

lol :D
people are too stupid!

but as cjolley said. if they dont want your advice, but fancy themes, let them have it..

mdhome
28th June 2003, 01:25
I believe that there were big changes in Access 2000 engine that allows it to use a cut down version of the sql server engine. However, I am not sure how this improves multiuser response and performance or if it raises licencing issues.

This might give some guidelines

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odeopg/html/deovrmultiuserdatabasesolutionarchitectures.asp



regards MD

efty
28th June 2003, 05:44
mdhome is right Access 2000+ allows use of a cut down version of the MS SQL database. However the standard condiguration allows only 5 users. Besides the "server" that will host the database will be a win98 machine :D

Anyway, I will do whatever they want and hand it over. I just hope they will not blame me later on. The essence of the problem is that they are just a small department of a big company. Anything IT related should go through the IT department which would take ages to do through the proper channels. So they have to work with what they have due to timing constrains.

Kurt
28th June 2003, 06:55
Sure they'll blame you for it ;)

You might try Filemaker Pro as well. It sure is easy to work with/modify and much less of a resource hog than Access 2000 -and faster when operating over a network.

dbdg
28th June 2003, 07:01
Had a similar thing happen to me, with the company (very large pension company in the UK) deciding not to listen to my warnings.
Take to long for the support group to set up etc. etc..

Since left and according to a few workmates, my name is now mud. :)

sheridan
28th June 2003, 20:32
Try and look on the bright side :laugh: The Win98 will probably crash or drop connections long before the Access gives up!

mdhome
30th June 2003, 01:03
One option might be to try and design the database so that it can easily be ported to SQL server. i.e use as little Access2000 vba code for data generation as possible, create queries using standard SQL that is compatible with SQL server (no crosstab :-(). That way porting should be easier.


Regards MD

Drizzt
30th June 2003, 01:31
@efty

First of all, you can simply use linked tables from an SQL Server.
Or, with the developer edition of Access 2000 you can generate a project that work on a regular SQL Server.

KeiFront
1st July 2003, 11:27
Efty, if your still interested I can provide you with some links to sites that talk about the database stability of access.

From personal experience I can say that it is perfectly possible to create a stable multiuser environment with an access database (access 97 database up to 30 users). Is it advisable? No, because it involves a lot of code work (record locking, transactions, rollbacks, etc).

efty
1st July 2003, 12:11
Efty, if your still interested I can provide you with some links to sites that talk about the database stability of access.

That would be very helpfull.



From personal experience I can say that it is perfectly possible to create a stable multiuser environment with an access database (access 97 database up to 30 users). Is it advisable? No, because it involves a lot of code work (record locking, transactions, rollbacks, etc).
Well I am not sure I have the experience to do what they need. I am almost done with the basics but I have not yet tested the database in a mutliuser environment. There are still some issues I need to resolve/iron out like user access restrictions. I am also following Drizz's advice using as little VBA as possible.

KeiFront
1st July 2003, 12:26
I will post those links tomorrow when I'm at work.

KeiFront
2nd July 2003, 10:10
Here you go:

How to keep a Jet 4.0 & Jet 3.x Database in Top Working condition:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;303519
http://support.microsoft.com/default.aspx?scid=KB;EN-US;q300216

Opportunistic Locking: very interesting and necessary trust me :)
http://www.dataaccess.com/whitepapers/opportunlockingreadcaching.html

Performance issues:
http://www.classicresystems.com/network_performance.htm

Access corruption:
http://www.rv-thereyet.com/Tips/Access/access_corruption_searching_foe_a_cause.htm
http://www.rv-thereyet.com/Tips/Access/access_tips.htm

Some general information:
http://www.superbase.com/tech_support_networking_fact_sheet.htm

Pros and Cons of Multi-User Database Alternatives
http://www.spectrumsedge.com/multiuserprosandcons.html

I hope that these sites will help you, if you got any questions just ask.