2

I'll try and keep this simple, I have worked on projects in the past whereby we use either Oracle or MS SQL server as the data store with Access as the front-end, rather than linking in the tables I tend to use an ADO connection to the respective database in order to open my recordsets as in most cases this is faster as the query is executed against the server and then the results returned rather than the work been on the local PC.

My question now I've finally got there is, if I place an access .mdb file on a server machine with more processing power than my local PC and then run queries from it using an ADO connection (like Oracle/MS SQL), will it provide better performance due to the .mdb been on the server; or as it's access will the work automatically still be done by the local PC as access is a file type database rather than a database server?

khr055
  • 28,690
  • 16
  • 36
  • 48
Matt Donnan
  • 4,933
  • 3
  • 20
  • 32
  • Is this question about performance with an ADO connection to an MDB on a file share vs. an ADO connection to a client server database? – HansUp Jan 26 '12 at 17:33
  • @HansUp No, it's the perf of an ADO connection to an MDB file stored on say the C: drive of a large server vs having a standard query using linked tables to an MDB file on your standard desktop computers C: drive. – Matt Donnan Jan 26 '12 at 18:38

1 Answers1

4

No it will be slower - the queries will still run client side, and you will have network activity on top.

Access applications always run on the client side. Locking takes place by using Windows filesystem byte range locks on the LDB file, to allow multiple instances of Access to modify the same MDB file.

All the code runs on the client, and you will be having to send the data across the network. The only work the server will be doing with an MDB file is acting as a file server.

Just use SQL Server Express if Access is not fast enough. Since SQL Server is a client-server system, putting it on a fast server will help.

Ben
  • 34,935
  • 6
  • 74
  • 113
  • Cheers for that, I'm ok with using SQL Express etc but was just wondering if using Access in this way would get the Server to execute the query or whether it would still be client-side. – Matt Donnan Jan 26 '12 at 18:40
  • Thanks again, do you have any idea's on one of my older unanswered questions please? (http://stackoverflow.com/questions/8896845/ms-access-cancel-execution-of-pass-thru-query-keyboard-shortcut) – Matt Donnan Jan 26 '12 at 20:16