Single–>Multi-user (2000)

Topic

New Reply

I developed several databases running on stand-alone PC

Reply | Quote

Replies

The generally accepted view is that the database where the actual tables are stored should be on the network server, and the .mdb file where queries, forms, reports and code are stored should be on the local workstation – that’s especially true with Access 2000, as design changes to the front-end database cannot be made unless you have exclusive use of the database. Also, the performance is generally better in that configuration.

Other issues also play a factor – how many users will you have? And how much data entry will be going on during peak periods? Depending on those answers, you may want to think about a SQL Server (or another robust database) for the back-end rather than using a Access/Jet back-end. You also want to think about locking issues – most people start out with “No Locks” until they have a better handle on the usage. Hope this helps.

Reply | Quote

About 30-50 users at a time.
What do you think the breakpoint is? 100? 200 users?

Not much data entry at peak-hours.

Thanks for your advise till now yet!

rettnuc.

Reply | Quote

If there’s very little data entry or data editing (prehaps no more than an edit an hour per user) and people are simply looking up information, then you should be in decent shape for that number of users. We actually have one client who runs nearly 100 users on an Access database over a 10Mbit LAN and has done so for several years. Their response time is very good for lookups, but they managed to live with it until recently – they now have us creating a SQL Server back-end. Can your situation be made to work – probably; is it optimal – no.

One other issue to be concerned about in this situation is how you deploy maintenance design changes. In 2000 you must open the database in exclusive mode in order to change forms, reports, macros and modules. That’s one of the reasons we finally moved all front-ends to the workstation. However copying changes in the design to 50 or 100 workstations can be a formidable task if you have to do it very often.

Reply | Quote

To maintain front ends you could do the following – I use it in my apps.
Code a startup stub app (VB, C++…)
On the server have a ‘master copy’ of the front end which has a local table with a version number in it.
The user starts the stub app NOT Access/mdb/mde file.
The stub app checks the ‘master’ on the server and the local copy.
If the master has a higher version number it copies it from the server to the local machine.
Then it launches Access with the local front end database.
The user doesn’t have to worry about newer versions of the front end.
You just need to put the newer version in one location and don’t have to worry about the users.
You can even go further and implement security without the user realising it unless they try to open the front end without using the stub app (I do that, I also check if the already have the front end opened and if they do activate that instead of spawning another copy).

Reply | Quote

In addition to what Wendell says, you’ll have to watch out how you will numbering records in tables that needs continuous numbering, things like invoices or orders and so on.
You will have to create a system that take care of making double numbering impossible.

Reply | Quote