we are having performance problems with a pure MS Access database across a WAN.
As a test we want to setup a MSSQL server , move the data to the server and run the MS Access clients through a ODBC interface.
The application uses much SQL methods to get the data ( docmd.runsql commands ). Only a few native queries are used.
Will this help the performance ?
If not , what do i need to do to get a better performance ?
It is a lot of work to build a new application so if it is possible whith the existing code it would be very nice .
The best way to get performance from an Access front-end using a SQL Server database is to try and move as much of the data processing onto the server. There are a number of methods available for doing this. If you use a lot of DoCmd.RunSQL queries the quickest and easiest way to achieve this would be to use Pass-Through queries (look up Access help).
Pass-through queries are, as the name suggests, a way to pass a SELECT statement (or any valid SQL statement) straight to the Server for processing, thus missing out the potentially slow Access client. The only downside to this is that Pass-Through queries are returned to Access as read-only recordsets, so the results of these queries can't be changed.
You could also open a recordset in VBA and set the cursor location to use the server.
Also, if your queries are quite complicated you could build a View and access the data that way. A view will also give you read/write access to the data if you so wish.