Split Database = Poor Performance

[cdogstu99]

So, I split up my database and housed the tables database part of the split on a shared network drive and am experimenting with the front end of the database which i've housed on my local computer. I've tried most of the recommendations: shortened the name of the db, changed fe to mde file, changed link tables to subdatasheet (none), but still my forms (only 2) are taking a while to load up. The forms are pretty substantial, and have a form and a subform on each. Can anyone offer up any recommendations to improve the speed?

 

[GaryPanic]

move the d/base closer to the network drive letter
ie

G:\Datastorage\xxx.mdb

rather than
G:\datastorage\anotherfolder\yetanotherfolder\etc

also any tables that do not have to be on the BE move to the FE
so if you had currencies on it - these are pretty unlikely to change so move them fromt he BE to the FE
compact/compile

hth

 

[RuralGuy]

Have you looked at this link yet?

 

[KeithG]

Do you have any code in the Load or Open Event?

 

[WayneRyan]

CDog,

Along with the other suggestions, I'd check to be sure that the forms do
not have an ENTIRE table as their RecordSource. This will cause the entire
table to be dragged accross the network. Better to base the forms on
a query that restricts the amount of data.

Wayne

 

[JACKSON]

I have split databases that are substantial in size and not had those ypes of problems. I would focus on appropriate indexes of teh underlying tables, especially on fields you filter by
Good Luck

 

[Pauldohert]

If you exhaust all the above and see no real improvement consider moving FE Access and BE SQL server.

You could ask for a pay rise with the introduction of working with the new technology??

 

[KeithG]

Do you have any comboboxes on your form? Sometimes these slow your form down because they have to retrieve the data from the BE. What I do to solve this is make the comboboxes recordsource a local table in the FE.

 

[cdogstu99]

Okay here's my database

okay i'm attaching my database (fe+be_....if you look the forms are frmSearchStrategies and frm SearchFunds...there are a lot of drop downs on my forms, but i think most are based on queries....if someone has a chance to take a quick look...i'd appreciate it...thanks guys...

-chris

 

[cdogstu99]

actually here's backend..wouldn't fit on last file....

 

[KeithG]

I would say your problem with the form is all the comboboxes. Even though they are based on quieries, the queries are base on tables from the BE which is causing your problems. Does the data change for any of the dropdowns or is it pretty static? I would move the tables for the dropdowns to your FE and I bet this will fix your issue.

 

[RuralGuy]

Have you tried turning off Name AutoCorrect? Failures caused by Name AutoCorrect

 

[cdogstu99]

KG...most of the dropdowns are static...except for FIRM ID, which i'm working on trying to change that field to allow for updates, so that will change

RG...i tried that, and it seems to speed things up a tad, but still dragging quite a bit

 

[KeithG]

I am 90% postivie that I have identified your problem. Just move the static data to your FE.

 

[cdogstu99]

KG...actually i lied...the Strategy and Funds tables are the two main tables which are displaying the data on each of the respective forms and which will be housing all of the data input by any user from the form...so these need to be housed on the back end as well...

I tried what you said...with only Firm ID, Strategy and Funds on the back end ..and still hangin up...this is driving me nuts

 

[cdogstu99]

Just following up on this....KG or anyone ...is it possible to have the three tables that are not static...Strategy, Funds and Firm ID on the Back End and have the other tables on the front end...Would it make a difference???

 

[KeithG]

If they data in your tables is not going to change I would put them in the Front End. Everytime you open your form the db was to retrieve the data for the dropdowns from the back end and I believe this is where your performance takes a hit.