VPN - Process are too slow!

[Maritza]

I developed a sales database that contains around twenty forms, (in addition to tables, queries, reports and 2 modules). In these forms, I'm setting recordsets, select statements and filters. We recently hired a person to set up a Virtual Private Network. After spending a couple of thousands on a server and countless hours reprogramming the database to have multiple users access the database out in the field, (via Verizon broadband network card), we were unsuccessful to use the database since it was taking too long to download the data or perform any calculations. The database is split. The users have a front end.

The person setting the VPN has suggested that I get rid of all the queries that I have in the forms (which is impossible), or use Visual Basic for the front end instead of Access with VBA.

When I set up the recordsets I use the following setting:
rs.CursorLocation = adUseClient
rs.CursorType = adOpenDynamic
rs.LockType = adLockOptimistic

I don't know what to do. This program is very complicated and I don't want to have to reprogram the whole thing with Visual Basic, and I don't know how the front end is going to work if I eliminate all the queries, select statements and filtering.

Any ideas, comments or suggestions would be appreciated.

Maritza

 

[The_Doc_Man]

The fact of the VPN is not an issue. The fact of it being a wireless network is more likely to be an issue.

We have seen many (and I do mean MANY) posts on the problems with wireless networks. If there is a way to get this from your consultant, ask to see the network stats on packet loss. No matter WHAT your network speed, you will get lousy performance if you suffer high packet loss. The thing is, since you are using TCP/IP, there is a packet re-send request that is the secret of how you get good data over bad networks. So your wireless card will blindly send and send its little heart out until the data gets through. Far better than some postal workers these days. (Whoops! Better look over my shoulder now for the USPS agents.)

Also, where it is possible to assert indexes on tables that are used for lots of searching, put an index on a few (VERY FEW) fields involved in that search. Access can search indexes faster than raw tables, and if you are lucky enough to be able to do THAT kind of data split, you will see better performance. Whenever you do searches on tables where it is NOT possible to use an index, you are going to be disappointed in the performance.

The sad fact is, if you are transferring too much data over the network, you could be on a Gigabit direct-fiber connection and still not see performance you might otherwise have expected for such a network, particularly if there are complex joins and lookups involved. For big data sets, Access over a network is limited by its design and you cannot escape that.

You can search this forum for articles on "Query Optimization" and "Network Speed" - many threads have been woven on those topics. Some of them might help you.

 

[Maritza]

Thanks for your reply. I knew that we have a lot of versatile smart people in this forum. I would follow your recommendations.

Maritza