Multi-user database on server with no access

[rob_levy]

Hi all,

My company has 3 access (97) databases (A,B and C) which all have linked tables into a central database (D) containing information that is common to all three.

All the database files are kept in the same folder on a Windows 2003 server which is also running Microsoft Exchange and a Spam filter. The folder is mapped to the Q: drive on each client and all database names are 8.3 compliant.

Since creating the linked tables to the central database I'm suffering really severe speed issues running queries from A,B or C such as the one described below:

SELECT * FROM Candidates WHERE Firstname LIKE "Qu*"

Where Candidates is itself a query joining a table in the A,B or C to D.

The server doesn't have Access installed on it at all and I am thus assuming that the only part the server has to play in all this is just handing Jet the part of the mdb file it requests.

Is this correct? Would things be sped up by putting Access onto the server?

Thanks in advance for your advice.

Rob
Applications Consultant
UK

 

[mdb4me]

Access will pull all the records from all tables referenced in your queries over the network to the individual workstation before it can resolve the query, so if many tables, complex joins and thousands of records then performance will be poor.

Even if Access is installed on the server the processing will still occur on the local pc, you may need to redesign the databases or look at setting up a database server eg. Sql Server, mySQL, Oracle or similar.

 

[The_Doc_Man]

Pretty much the only thing you can do in your current configuration is to assure good indexes on the table/field combos you will query most often. Even in the case you described, if there is a valid index, Access will pull THAT in first to grow the recordset before it feeds you results. Since indexes generally can be searched faster than the base table, you can get SOME speed back - but don't hold your breath on the difference.

I would also consider mechanically merging the three external databases if at all possible, since each file requires extra resources. The fewer files you have to open remotely, the better off you are.

Down the road, putting Access on the server sounds great - but beware of the trap that can befall you.

First, to get that query executed locally, you have to use terminal services to get into the server. TS connections to Access are often balky. Search this forum for "Terminal Services" and "CITRIX" as a topic to see about case studies in doing this.

Second, watch out for your End User License Agreement. The EULA for ordinary Access does not really allow you to share Access among multiple users. In theory you need one copy of Access for each user. If your company's legal department is a stickler for following the letter of the law, using ordinary Access on a shared server is a no-no. The same threads I mentioned for my first point contain some references to a special license you can buy that allows multi-user access to Access via Terminal Services, but that doesn't solve everything.

The true, long-term solution is to get rid of Access as a back-end in favor of SQL Server or any of several other products for which Access has ODBC capability. Then only use Access as the front end because its forms and reports will work Jet or ODBC as needed.

 

[John_W]

There's some useful stuff here worth trying:

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

 

[Simon_MT]

You don't need to put Access on the Server. The porblem is that Access is slow when multiple data stores (MDB) are used. Yes I agree merging the files is the best option.

This is a method, but not very eloquent.
Assuming that you are using front-ends on PCs
Assuming the ability to use a Default Form

I have only done this for two data stores, in each of the data store (MDB) create a Dummy Table (call it what you like I use Companies) in A B C (even D) with one identiical record. Link each of these dummy tables from A B C D into the Front Ends on each PC. In a Query add the tables, link them together, (one record) and bind these to a Default Form with the Record Soruce of the Query you have created.

The idea is that the Default Form's underlying Query keeps each database open i.e. there are ldb (lock files) exist once a user starts the application. Then try your Query. This not the best method! I know Grandpa will not approve.

Things to look into are the AV does not check mdb ldb mde mdw files.

Simon