MS Access 2000 Mutliuser database

[efty]

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]

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]

Re: MS Access 2000 Mutliuser database

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
people are too stupid!

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

[mdhome]

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]

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

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]

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]

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]

Try and look on the bright side The Win98 will probably crash or drop connections long before the Access gives up!

[mdhome]

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]

@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]

Re: MS Access 2000 Mutliuser database

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]

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]

I will post those links tomorrow when I'm at work.

[KeiFront]

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.