Should I use linked tables in a multi-user enviroment?

[egrant]

Hi,

I have a question about linked tables. I have not actually used them before yet I have created a fair few access applications before.

This time I am allowing more then one user to access an Access database on an internal network. I tried to create shortcuts on thier desktops to the one database so everyone could access the one database all at one time, but this seems to slow down the database a fair bit. As in when trying to access a form it's very slow to load. And if someone else is running a query it locks me out of a part of the subform that could possibly be accessing the same data that the query being run is.

I was considering linking the tables to individual copies of the database so each user has thier own copy of the Access database(client) but is only accessing the tables of the main database(server) that are linked and not the same search queries, forms, reports etc.

Can anyone tell me if this will solve my slow performance problem?

Thanks,

EG

 

[Steve101]

Linked tables are ALWAYS a good idea in a multi user environment. Exception to this might be where local tables are required to be resident with the client, in the distributed client version of an application.

Linked tables allow the data to be central, and the clients to be distributed at each workstation - some designers prefer this model, though I frequently use a central client as well.

Irrespective of where the client resides, separation of tables and other objects (via linking) also allows a developer to take a copy of the client, and work on it independently without worrying about having to try and sync the data afterwards.

Probably the greater part of your performance problem lies with the way in which you are sourcing your data to the application; if your recordsource incorporates entire tables or complex, poorly indexed queries, then this is likely to manifest in poor performance, which will be expecially noticable over a (slower) network. Try to ensure that you retrieve to the client, only those records that are required for the user to see/maintain. This may mean re-checking the constraints associated with your forms' recordsource, any indexing associated with joins etc.

If I recall correctly, there are some good guidelines in the online help relating to (multi user) performance; check these out.

Finally; I believe there may be a small penalty associated with linking; but from my experience, this is absolutely minimal, and the benefits always outway this.

Linking incidentally, is also the first step towards connecting your application to a data source other than Access; for example, once an application is connected to a linked Access application, it is faily easy to connect it to another source of data (eg. SQL Server or Oracle), with minimal changes to the application itself.

Hope that some of this helps,

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[JeremyNYC]

EG,

Yes, as Steve say, you'll want to use linked tables. Check out the Deploying Databases pages on my website for a look at how I go about this.

Jeremy ==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Affordable Development, Professionally Done

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.