In my ongoing saga, I've now got a working Access database for my users. It's written in 2000, because that's what I've got, but they've got 2003 (don't ask). I'm still having speed issues when they use the import process. (Exporting doesn't seem to be an issue, though.) I've tried modelling the import and export process as they've set it up (.mdb sits on one network drive; import files sit on another), and it worked acceptably for me. For them, it's still taking way too long (over an hour for the import process to get past one stage). I believe this is because I'm based at our employer's head office, and it's got the faster network.
I've been looking at creating a replication set of the database, so that the design master can sit on the network and each user can have their own local replica, but it's not something that I've ever done before. I've also thought about upsizing the SQL Server, as the users' site has its own dedicated server for SQL databases, but while I've supported applications with Access front ends and SQL back ends, I've never created one from scratch. My question (eventually) is this: Which of the these proposed solutions is the most practical? For this questions, "practical" means "fastest for users and easiest to set up and maintain"! :P
If anyone needs any more information, please let me know.
It looks like the way to go will be uploading. Going to a replica set would merely move the pain from the import/export process to the synchronisation process, due to the network architecture (the network server is located in a different building to the office - several hundred miles away >.<) If I upsize to SQL Server, I can automate the imports and the important exports, so that the users don't have to do anything. This would please them.