Access and multiple concurrent users

abesimpson

Active Member
Joined
May 3, 2003
Messages
435
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
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
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
 

Forum statistics

Threads
1,171,463
Messages
5,875,656
Members
433,145
Latest member
nzltrippa

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top