Access Form Takes Upto 40 minutes to populate

[ian87]

Hi there,

Having finally finished my database and successfully getting it to work, thanks largely to the posters on this forum, i have run into yet another roadblock and i was hoping you could help.

The database i have created needed to be accessed my multiple people across multiple countries. So i split the access file into forms/qrys and tables and stored the tables on a desktop which is shared via the company VPN. I then created links to the tables and gave all the users a copy of the forms and query - In hindsight using an SQL server would possibly have been a more sensible solution but you live and learn ....

The problem i have run into is that some users have said it can take them up to 40minutes to open one of the forms i have created and forever to save as well whereas others have found the process takes seconds (at present it seems to be the people in the U.S. that have the problem but not in the U.K. - the vpn desktop is located in the U.K.). The tables do come to a total file size of around 23mb but i was wondering if anyone had any suggestions at why this might be happening or anything i might need to change in the file to speed up this process or should i just accept it as a hardware problem and i need to look into their internet/router connections.

Any suggestions would be greatly appreciated

Many thanks

Ian

 

[CBrighton]

You mention an SQL server, if you have one try that first. You should find it massivley faster.

 

[Rabbie]

Is this form based on a query? If not why not because it will make transition to an SQL server much easier. If so how long does it take to open the query on its own and view the data?

How many fields do you have on the form.?

 

[ian87]

Hey Rabbie,

The form is based on a query, sql server is now not an option unfortunately due to deadlines.

The form has 48 input fields plus 13 sub total fields (SUM etc.)

Got any ideas on how to make it faster?

 

[CBrighton]

You should be able to upsize the backend database quickly to SQL.

Or would the extra time required be to arrange access to a server, etc?

 

[Rabbie]

How long does it take to open the query without the form?

I suspect the problem may well be related to the network speed especially as the US seems to be much slower than the UK.

 

[ian87]

Hey Rabbie,

I am agreeing with you that it must be network related. We've been having some problems with our netgear router recently and it could be related to this. The round trip takes 3ms-10ms between UK computers and 140-200ms for U.S. based PC's.

Many thanks Rabbie and CBrighton, we're just going to have to accept that we're getting a bum deal.

 

[Mr Smin]

Could there be any advantage in keeping the queries on the VPN desktop? I'm wondering if having them on the remote machines means all the data has to pass down the VPN link in order to process the query but if the query lived on the VPN desktop then only the results would have to travel.
(This is speculation - someone with experience of this is welcome to correct me)

 

[ian87]

Thanks guys, we have now just created a shortcut and emailed it to people. It makes the form work quicker as everything is located on th evpn server. YOU GUYS ARE THE BOSSES!