Connecting Your Applications

Many Access applications were written by using Microsoft Visual Basic for Applications or the Visual Basic for Applications Access user interface.

• Applications that use Visual Basic for Applications as the development environment can run against SQL Server, using the Jet ODBC driver.
• Applications that use the forms and reports found in the Access user interface can access SQL Server using linked tables. If your application will use linked tables, make sure that all Access tables get moved to SQL Server to increase performance. Creating queries against a mix of Access (Jet) and SQL Server using linked tables can be very resource-intensive.

The first step in migrating your file-server application to a client/server model is to ensure that the application works against the new database. The next step is to optimize the application for the client/server environment by:

• Monitoring Transact-SQL statements being sent to the server.

  SQL Server Profiler is a useful tool for monitoring how Transact-SQL statements are sent to the database. If you run an unmodified Access application on SQL Server, you might send suboptimal Transact-SQL to the database by using Data Access Objects (DAO) with the Jet/ODBC driver. For example, a DELETE statement that uses the Jet/ODBC driver to delete 1,000 rows makes 1,000 calls to the database, negatively impacting the performance of a production database. In this example, SQL Server Profiler displays 1,000 DELETE statements, allowing you to modify the application to use Microsoft ActiveX Data Objects (ADO) with the Microsoft OLE DB Provider for SQL Server, and thereby improve the application’s efficiency.

• Implementing efficient indexes.

  After you determine that the Transact-SQL statements being sent to the database are efficient, you can fine-tune those statements by using indexes more effectively. The Index Tuning Wizard allows you to find bottlenecks, and it makes recommendations. Your Transact-SQL statements are not modified, but their performance improves with the correct use of indexes.