We didn't have this issue before but we have been told that the network will not be upgraded and figure out a workaround.
Typical boss-speak for "I'm too cheap to consider this." If the boss knew how many person-hours were wasted waiting for your network, s/he would not be so sure that the network won't be upgraded. If lost labor were coming directly out of the boss's pocket, the answer would be different.
It's just not an option we have to develop a workaround.
OK, let's work with what you have now. But this will go only so far before it becomes intolerable. Your boss might have to yell a lot but the answer isn't going to change past a certain point. Be prepared for that.
Your best solution is to abandon that multi-staged approach. The fewer touches you have to make to your tables, the better off you are. Learn how to make your back end (BE) shared optimally. Having a "local" lookup table works only for relatively static tables. If the tables have to be re-synchronized often enough, they get in the way.
One strategy is to assure that all SELECT queries be tuned to NO LOCKS and all action queries (insert, update, delete, make table) be tuned to OPTIMISTIC LOCKS. NEVER EVER use pessimistic locks. NEVER try to lock things yourself. Let Access manage all locking for you and don't give it too many locks to manage.
Always use queries that do any lookups via JOINs to translation tables (as opposed to having some kind of DLookup or equivalent in the query).
Always use pre-defined / named queries, with parameters where necessary, rather than building ALL queries on-the-fly. The queries that appear by name in the AllQueries list have all been pre-analyzed and pre-optimized. The stuff on-the-fly has not
Assure that whatever you are using as the selector variable for each user (maybe their login ID?), that in ANY table where it is relevant, it is indexed even as a foreign key (FK) and even if marked DUPS ALLOWED.
If your record IDs are short enough, a natural key is OK. If you have a key that is much longer than four bytes, consider whether a synthetic key (such as an autonumber) would be better for the primary key (PK). (Long keys take longer to process on a slow network because more data needs to be fetched.)
Remember that all parent-child relationships require the PK of the parent to be indexed with NO DUPS. (That's why it's called a PRIME key).
It IS possible to have too many indexes - Access limit is 10 per table including whatever is the PK. But having one key for every field on which you
commonly do searches or selections or JOINs is not unreasonable.
Do AS MUCH WORK AS POSSIBLE in queries and AS LITTLE WORK AS POSSIBLE in recordset operations and VBA. This is because with extremely few exceptions, VBA is slower than SQL. That's because VBA is implemented as an emulator for a virtual machine that doesn't exist. SQL, however, IS compiled and the setup for the query is quick. For recordsets, you have to emulate your code for every record. For SQL, once the query gets rolling, that's pure compiled code that is running the show.
To safeguard yourself, look up the DAO .Execute method on-line and learn to use it for all SQL action queries if you are not already doing so. Using DoCmd.RunSQL works but can be slower. The down side to.Execute is a bit more strenuous care to the setup of the SQL string that you want to execute. The up side is that the .Execute when used with the option dbFailOnError will AUTOMATICALLY roll back the action query (as though you had used a one-query BEGIN TRANSACTION/COMMIT pair.) AND you can check for the DAO object's .RecordsAffected property to see how many records were updated, inserted, or deleted. If there is an error under .Execute with dbFailOnError, the whole action sequence is rolled back and you can predict where you are. Also, the error that gets signalled is usually at least as good and often is BETTER than the errors you get for .RunSQL cases.
The reason .Execute is faster than .RunSQL is that .Execute goes directly to the DB Engine whether we are talking Jet (old versions) or Ace (new versions), whereas .RunSQL is run from inside the Access environment. There IS a small difference in execution speed, but this small speed difference is magnified by having a slow network. TRUST me on this fine point - I have been there and done that. No fun, I assure you. But .Execute IS faster on a slow network.
Using temporary tables can be workable but only if you take great care in the data re-synchronization phase of what you are done. You want to design your temps to use the MINIMUM number of records possible to do what needs to be done.
This should get you started in re-thinking what you have been doing. If you have more specific questions, post them. We are literally world-wide. I'm in USA Central Time Zone which at the moment is 5 hours behind GMT.
!
