I have an update query where one large table is updated to match corresponding data on another large table. On a copy of the database resident on my local drive this query takes about fifteen seconds to execute. However, in the original database, located on a network drive, the same query will slow to a halt and hang the system. With all things being identical save for the location of the file, which unfortunately must reside on a network drive, does anyone know of a way to work around this apparent server problem?

Access is a file server rather than a database server. When your backend db is a "real" database server such as Oracle or DB2, the query is sent to the server for processing and only the results (if it was a select query) are returned. Therefore, little data needs to be transferred around the network. Access being a file server, CANNOT do any processing on the server. Therefore, it needs to transfer the entire contents of the table (or multiple tables as the case may be) and process the query on the client machine. So, it is the network traffic that is causing the problem you are experiencing.

There is really no solution short of replacing your Access tables with SQL Server or some other RDBMS that your company supports.

Sometimes I have found when working with large updates, that it is actually quicker to copy the entire db to my hard drive, run the update and put the db back.

Thanks, Pat. Valuable lesson learned.