Access and multiple concurrent users

[abesimpson]

I believe it is possible for a small number (<10) of multiple concurrent users to input data into a server mounted Access2k database from their workstations without risk of corruting the database.

? Is this true?
? Is there a prefered database structure to accomodate this, i.e., should the various users input indvidual tables?
? Any suggestions as to things to "watch out" for in doing this?

I think I know that this is less of an issue with SQLserver or similar but the Access database is already in place.

As always thanks in advance.

a

 

[SydneyGeek]

From the MS archive:

The Microsoft Access Multiuser Model (parentheses mine) --

To make your application available to multiple users, do one of the following:

Place the entire application on a network server and allow multiple users to open the database. With this strategy, users share all the database objects that make up your application, including the tables it uses to store data.

Split your application into two databases: a back-end database that contains your applications tables, and a front-end database that contains your applications other objectsits queries, forms, reports, macros, and modules. Then, place the back-end database on a network server and distribute (shortcuts to) the front-end database to your users. Base the objects in your front-end database on linked tables from the back-end database. This strategy keeps the data separate from the other objects in your application and reduces network traffic. (It's also much easier to maintain with this setup. You can keep a development copy away from the users, make and test changes, and then update by replacing the current production front end with the new model).

(Use the Database Splitter to create a split database if you don't already have one).

And some other ideas:

Never let your users open an unfiltered form. It will slow performance to a crawl. Present them with an unbound form and whatever search capability they need, then refresh the subform / list box based on their selections.

If you need to make multiple appends / deletes / updates, use SQL instead of looping through recordsets. It's quicker.

If you do use recordsets, make sure you clean up references in each routine. Things can get unstable if you forget, because you chew up available memory.

Multi-user design is a big topic. MS has a newsgroup devoted specifically to it -- could be worth a trawl through.

Denis

 

[abesimpson]

Many thanks for the information.

Regards,


a