Help I have a Form that is opening too slowly

[RayMilhon]

This is kind of a complicated issue that will take some explaining.

I have an Access 2010 database with 1 local table and about 25 Tables located in 2 separate SQL Server Databases. The Navigation form has 3 subforms 1 for data entry and 2 for 2 different types of reports. The problem I'm having is on the Data entry form. Opening the form and changing records takes 2-3 minutes per record. The local table has 2 fields that link to 1 of the sql server databases to get the names, address and other demographic information for patients and their doctors. These 2 textboxes runs a query to get that data and each query takes up to a minute to run. If the doctor record is changed then the list of patient records also changes to only include that doctor's patients. I tried modifying the form to look at the current records id and only select the provider and member that match but if it's a new record I get all kind of errors. Anybody have an idea on how I can speed this up?

[June7]

Try this test.

Make a copy of the frontend, import the SQL Server tables, run forms on those imported tables. Still slow?

[RayMilhon]

Not sure that will give me valid results the SQL Server database is 25 GB which far exceeds Access capabilities. I'll have to look at the tables required for this form and check the size of just those tables. I could bring in a smaller chunk to fit into the Access Size limit but that would skew my results as there are fewer records to look at.