Slow VPN -- DB too slow when RecordSource loaded

[wrldruler]

Hi All,

I've got a database that I am trying to house on the East Coast, and
run on the West Coast. When users on the East Cost open the database
on the East Coast server, it takes them 5 seconds to get it open. When
the West Coast folks try to open on the East Coast server, it takes
about 60 seconds. And each individual form takes another 50 seconds or
so to load every time.

If I try the inverse, and store on the West Coast, the problem
switches. East Coast takes 50 seconds, and West Coast takes 5. So I
know it isn't a server issue. It is a slow VPN connection. It is slow
for other apps and dataa transfers, not just Access. I am just trying
to figure out how to limit the data transfers to speed things up.

I have tried all of the normal recomendations. Subdatasheet to None,
AutoCorrect off, and establishing peristant connections on a hidden
from. These were great help for the East Coast to East Coast
connection, but did little to help West Cost to East Coast.

I split the database and it din't help much. I removed the DCOUNT and
it didn't help much. I got rid of the RecordSource in my combo boxes
and load by VBA, and it didn't help much.

This is a brand new database with like 20 records. I am using bound
forms (not recordsets) on all my forms.

I put breaks in my code on my first form. It takes a split-second for
most of the code to run, but this particular line takes 15 seconds to
load:

Me.RecordSource = "qry_Master_Users"

Everytime it calls a table or query, it stalls for like 15-30 seconds.

I have attempted to "pre-load" this query by calling the same query on
my hidden form. It didn't work.

Can someone recommend a way to limit the data transfers that are going
back and forth. Or a way to load all of my data at start-up?

I would be happy if it took like 30-60 seconds to load the database
initially, but I just can't stand when it takes 15-30 seconds to load
each individual page.

Thanks,

Chris

 

[wrldruler]

Second question: If I have already loaded the query via a hidden form,
why does it keep going back roundtrip to retrieve it again. Is it
because I am using Bound forms, rather than Recordsets?

Thanks

 

[Albert D. Kallal]

As you said...it is slow...

You can read my article here for a solution:

http://www.members.shaw.ca/AlbertKallal//Wan/Wans.html