Method to speed up the performance of Linked Databases?

[McArthurGDM]

So,

I want multiple person(s) to use the database that I created.

At work, we have a LAN drive structure. I thought that I could originally just put the MS Access database on the LAN drive and have people go in and use it. I was wrong on two fronts 1) multiple people can not open a database at once and 2) running ANY program off the LAN drive slows the program down drastically, to the point where it's practically unusable.




I figured that if I had a database on the LAN drive that was host to just the main tables - and have everyone have a copy of the database on their computers - and link those computer-based databases to the database on the LAN drive, that it would speed things up - but no, it does not.

Things are still really slow. Fortunately, the frequency of the database freezing is lessened in linking. But that's about it.

Is there a method to speed up the performance of the database?

Some people mention SharePoint or some other web-based host application, but I wouldn't even know where to begin.

Any suggestions?

[pbaldy]

Here are a few thoughts:

http://www.granite.ab.ca/access/performancefaq.htm

The most common recommendation is the first, the persistent connection. What you've already done is called splitting, and was certainly required.

[orange]

Here's another list to go with what Paul has said.
http://www.fmsinc.com/microsoftaccess/performance.html

[June7]

A multi-user database should be split design - backend and frontend. BE has tables, FE has links to BE tables, queries, forms, reports, code. Each user runs a copy of the FE installed on their computer.

[pbaldy]

A multi-user database should be split design - backend and frontend. BE has tables, FE has links to BE tables, queries, forms, reports, code. Each user runs a copy of the FE installed on their computer.

Sounds like that was already done:

I figured that if I had a database on the LAN drive that was host to just the main tables - and have everyone have a copy of the database on their computers - and link those computer-based databases to the database on the LAN drive, that it would speed things up - but no, it does not.

[June7]

I wasn't sure what they meant by "copy of the database" - OP refers to "a database" and "the database" and wasn't sure they were describing a truly split design.

Last week data entry in the db was at a snail pace over the network. Today I was doing the same kind of data entry and ran at lightning speed. No idea why the difference.

[McArthurGDM]

Thanks for the links, those are all helpful suggestions.

June7, yes, it seems that some days the network runs faster than others. I akin this to my router at home that kicks out every now and again for no reason.

I am beginning to think that using a web-based service to host the tables would be ideal, but I am not 100% on that. I know SharePoint can offer that - but I wouldn't know where to begin with that.

[June7]

I have heard that web-based and SharePoint can be worse for speed and reliability.

[CJ_London]

Whatever you do, the weakest (i.e. slowest) link is between your pc and the server. If you connect to sharepoint - you get to it via a router, and if the router is on the same link as your server, you'll have the same problem.

You can improve things with good db design to minimise the volume of data traversing the link - both width (columns) and depth (rows). Think about how websites work, they only display the data required, a page at a time, so 50/100 records say, rather than a recordsource of a whole table which is then filtered on a form.

One way to cut down the traffic is to use a terminal server or citrix - you will get speeds virtually the same as having the backend on your local machine - reason, because the only thing that is transmitted is updates to the screen image. The front end and backend reside on the server so all the processing is done there (note, each user still has their own front end)