One of the most critical elements of a multiuser application is efficient use of the network. After you convert to client/server architecture, all processing is no longer performed on a local computer with Microsoft® Access. The fastest client/server applications optimize for fewer network roundtrips.
Think of the network as a bottleneck that takes a performance hit every time it is used. Instead of updating record #1 and then updating record #2, send one message to the server that updates both records at the same time. For example, you need to change the discount rate of these three customers because of a new contract. Instead of:
UPDATE customer SET discount = 10 WHERE CustomerID = 5
and then
UPDATE customer SET discount = 10 WHERE CustomerID = 15
and then
UPDATE customer SET discount = 10 WHERE CustomerID = 72
look for opportunities to batch updates together. Use:
UPDATE customer SET discount = 10 WHERE CustomerID = 5 OR 15 OR 72
Now you have only one statement and one network hit. This query is three times faster than the first three queries.
If you always update two records as a set, batch them together. Instead of:
INSERT orders (ID, qty) VALUES ('AAA', 17)
and then
INSERT RunningTotal(ID,qty) VALUES ('AAA', qty + 17)
use a stored procedure to do both at the same time. Use logic in the stored procedure on the server to update both tables. Here is a sample stored procedure:
CREATE PROC sp_NewOrder
(@CustID Char(3), @QtyIn INT)
AS
INSERT orders (ID, qty)
VALUES (@CustID, @QtyIn)
INSERT RunningTotal(ID,qty)
VALUES (@CustID, @QtyIn)
When it is time to send the record, use a pass-through query:
sp_NewOrder('AAA',17)
Now you are sending 21 characters on one network trip instead of 79 characters on two network trips.