Access, MSDE and WANs

[Addict]

I presently have four databases running on our network. The front end is on the local machines and the backends are on the server. I'm presently using Jet Engine. For each of the databases, the users range from 5 to 30. I believe there wouldn't be any more then 5 simutaneous users on the largest database. Backend sizes range from 4-11 MB.
I have to provide the database to a few remote sites. Any ideas on the best way to provide access to the database without killing the 1Mbps connection. I believe access pulls the whole database across the wire, not just the requested information.
Would it be worth moving to MSDE, in which all the work is done on the server and only the required data is sent across the wire?
If I were to use MSDE, does the "5 concurrent work loads" apply to the program or to each database?
Any other recommendations for backends? MYSQL? No money for MS SQL 2000!

thanks
Addict

 

[pono1]

It's a common misconception that Access always pulls an entire table across the network. If you set up your forms and queries and indices properly, you can control traffic to some extent. Access does do some things, however, that hog bandwidth: it moves large index files and makes all sorts of hidden, live connections (with bound forms) to the data source.

Another option that may be worth exploring for your WAN problem: Replication. There've been a few posts here on it and there should be some info on MSDN as well as in the Help file.

Regards,
Tim

 

[Kodo]

IMO, the best solution for apps that need to be run in varying, remote locations is a web app.

 

[Pat Hartman]

Would it be worth moving to MSDE, in which all the work is done on the server and only the required data is sent across the wire?

- If your forms/reports are bound to tables or to queries without selection criteria, converting will not help at all.

To make effective use of a conversion to SQL Server (or MSDE), you need to use queries with selection criteria for ALL forms/reports/combos/listboxes. Using filters doesn't help. So, if you're using the where argument of the OpenForm or OpenReport method/Action, you'll need to change those also.

I believe the limit imposed by MSDE on users is for the instance of the database engine rather than the database. So, if all your databases are being accessed by the same instance of the MSDE engine, you would only be able to have aroung 5 users before experiencing slowdowns.