Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2004

    Red face Unanswered: performance with Access frontend ?


    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 .

    Thanks in advance.
    Bye Erwin

  2. #2
    Join Date
    Mar 2004
    Glasgow, Scotland
    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.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts