I have created a database in two MDB files. One file ("application") contains all of the queries, forms, reports, etc. It links to a set of tables in the second MDB file where I am storing all of the data. I set this up so that the "application" MDB file could reside on the users local hard drive and access a common database that is shared on the server.
I did this to improve performance and allow mulitple users to access the system. However, performance is very slow across the network.
A friend suggested I move the linked tables into the application and don't access the data as a seperate file. Will this improve performance?
Good design is to split it. Keeping it split will make it easier for you to update the forms and reports. You can make changes on your system, test them using the live data then have your users get a copy of the front end. Much easier than having everyone get out of the application and then importing the objects that changed without losing any data that was changed in the common database.
If you want several people to use the database then it has to be on the server. Regardless of whehter the front end is on the user's system and the backend is on the server, or everyone is accessing the same front end on the server the same problem exists, the network. I would look at optimizing queries and forms.
How slow is slow? How much data do you have? Is it all in one table? Do you have enough indexes on the tables?
The database is a new customer service system and I only have about 20 test records input. To open the main customer screen (contact info, and a sub-form that displays summary of previous requests) takes about 10 seconds to open. But to open the form that has the detail information of the request from the main customer form takes about 1 minute to load.
I spoke to the IT and it turns out the location with the performance problem is accessing the network through a "dual-ISDN" line at about 1 megabit. I don't if any design changes to the system will compensate for slow bandwith.
I was hoping to find out linking to external tables really impacts performance that much.
Try to move look-up tables that have close-to-static content to the client app. And "synch" them with the server "in the middle" between the other clients at an appropriate frequency.
When you save a new version of your client app, I assume that you do a compact and repair before you distribute. A trick to speed up the performance is to make just one tiny change to a form just afterwards, save and then distribute the md(e?). (for example just drag the transparent frame of a label or something, anything that allows a new small save).