Access and multiple concurrent users

abesimpson

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

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

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,137,060
Messages
5,679,376
Members
419,824
Latest member
Mercy kiara

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