performance issues on VPN

[harryklein]

Hi All,
I am creating an application in Excel VBA that uses Access tables.
It is in use since a couple of month and works fine even with relatively complicated queries.
Recently I added a functionality that returns 150-200 rows and in case I use it via VPN, it gets uselessly slow.
I don't experience issues with other applications on VPN.
Is there any way to find out where is the bottleneck?
Thank you

[Minty]

Access doesn't work very well over a VPN, unless the connection is lightening fast and more stable than a thing that horses live in.
It's due to the way in holds open the comms using SMB protocol.

If you do a google search for Access over WAN there are a number of articles explaining in more detail, and how to resolve/improve things.

[CJ_London]

not enough information to comment

There are a number of ways to transfer data - so suggest

a) show your vba code
b) show the sql to the query you are using
c) confirm that all fields in the query that are used for criteria or sorting are indexed
d) explain what 'uselessly slow' means. 2 seconds might be 'uselessly slow' in some situations

Other than that, the normal requirement for any app is to return as little information as possible since VPN can be relatively slow compared with a LAN, so your other apps are probably adhering to this requirement whilst 150-200 rows does not. Transferring individual rows is a completely different animal from transferring files