Access DB question about performance

[Duwelon]

I've been developing an access database at work (Access 2000 format for compatibility reasons) to use for logging of certain errors and system admin type functions.

Which of the following scenario's would produce a faster update:

What has to get done:

500 total updates need to be made to an Access 2000 database. The database file is located on a file server hosted over a WAN. The connection speed to this site is not that great, but can easily transfer a 1 GB file in 20 minutes.

Scenario 1:

Open the Access MDB file remotely and use VBA to manually issue SQL commands using ADO. 450 SQL commands are UPDATE commands, updating about 10 fields and 50 are INSERT commands, inserting about 15 fields. All into the same table, no normalization at this point.

Scenario 2:

Open the Access MDB file remotely, do all insert/updates on a RECORDSET and save the recordset, allowing access to take care of all the SQL over the network that it needs to.

 

[KLin]

It's definitely going to be slow. MS Access just wasn't designed to be used over a WAN connection, especially if you'd be opening it remotely over the WAN.

 

[Aberforth]

Updating Access DB remotely is a very risky process. I've handled many such scenarios, the trick is to write a web service that can be accessed remotely and then call it from the client application /script which updates the DB locally. or you can just use SQL.

Make sure you do some authentication stuff for the web service to validate the update process otherwise anyone can call it - like password through a query string. I use Public Key encryption.

 

[JACKDRUID]

from my experience both are essentially the same in access. If you had a SQL Server it'll be much faster for Scenario 1, but not with Access.

best way (if possible), is to copy the mdb file to the local system, copy all linked tables as a local copy in the mdb file, then do your update. the trick is: no linked table, no remote mdb file.