Optimizing Microsoft Office Access Applications Linked to SQL Server. Andy Baron. November 2. Applies to Microsoft SQL Server 2. Summary One way to create applications that use Microsoft Office Access for creating user interfaces and that use Microsoft SQL Server for data storage is to link Office Access tables to SQL Server tables. This is the type of application created by using the SQL Server Migration Assistant for Office Access. This white paper presents techniques for improving performance and updatability in Office Access applications that use tables linked to SQL Server. Click here for the Word version of this article. Contents. Introduction. GqfK3.png' alt='Microsoft Access Definition Primary Key In Sql' title='Microsoft Access Definition Primary Key In Sql' />Understanding and Addressing Performance Issues. Understanding and Addressing Updatability Issues. Addressing Application Logic and Coding Issues. Creating Unbound Office Access Applications. Conclusion. Introduction. Microsoft Office Access supports three primary options for connecting to data stored in Microsoft SQL Server databases. Use the Office Access database engineoriginally called the Jet database engineto communicate with SQL Server over ODBC connections. Primary domain controller PDC and backup domain controller BDC are roles that can be assigned to a server in a network of computers that use the Windows NT. Microsoft SQL Server is a SQLbased relational database management system designed for use in corporate applications, both on premises and in the cloud. This definition explains the meaning of foreign key, which is a key in a database table from another table that refers to a primary key. Create Office Access Project applications that use an OLE DB connection to communicate with SQL Server. Write Microsoft Visual Basic for Applications VBA code that uses DAO, Active. X Data Objects ADO, or middle tier objects to connect and manipulate SQL Server data. Microsoft Access Definition Primary Key In Sql' title='Microsoft Access Definition Primary Key In Sql' />This paper focuses on the challenges encountered by Office Access developers who rely on the Office Access Jet database engine to connect to SQL Server over ODBC. The most common way this is done is by creating linked tables in Office Access that use the SQL Server ODBC driver to connect to tables in SQL Server databases. The SQL Server Migration Assistant SSMA for Office Access enables you to convert an Office Access database to this type of application by moving your Office Access data to new SQL Server tables and linking to these tables. Any forms, reports, queries, or code that previously worked with the original Office Access tables are automatically connected to the new SQL Server tables. In an application that uses linked SQL Server tables, two different database engines are at work the Office AccessJet database engine that runs on the Office Access client and the SQL Server database engine. The interaction of these two engines can sometimes yield results that are inferior to those obtained by using only the Jet database engine with native Office Access tables. This white paper discusses several of these issues and presents strategies for resolving them. Most of these issues relate to performance or updatability. Understanding and Addressing Performance Issues. Developers often migrate data to SQL Server expecting an improvement in application performance. Although performance does often improve, there are many cases where it remains the same or even degrades. In some cases, performance of certain queries degrades to an unacceptable level. The major cause of query performance degradation is when a query involving very large tables requires that all of the data from one or more tables be downloaded to the client. This can happen even when joins or criteria appear to limit the result set to a small number of records. This occurs because sometimes the Office Access database engine determines that it cannot submit an entire query to SQL Server. Instead, it submits multiple queries, often including queries that request all of the rows in a table, and then it combines or filters the data on the client. If the criteria require local processing, even queries that should return only selected rows from a single table can require that all the rows in the table be returned. The primary strategy for improving performance is to minimize the amount of data returned to the Office Access client and maximize the amount of processing that occurs on the server. To accomplish this, you need to be able to analyze the SQL commands that Office Access is submitting. Diagnostic Tools. There are two tools that you can use to see how Office Access is communicating with SQL Server. To listen in on the conversation from the server side, you can open the SQL Server Profiler and create a new trace. Select a template that shows TSQL to see all the statements being processed by the server. From the client side, you can edit a Microsoft Windows registry setting that allows you to see the commands that the Office Access database engine is submitting to ODBC. As always, be very careful when editing the Windows registry. For more information on backing up and editing the registry, see How to Modify the Windows Registry. To enable tracing of all ODBC commands from the Jet database engine. From the Windows Start menu, select Run. Network Problems Are Preventing Connection To The Microsoft Exchange Server. Type Regedit to open the Registry Editor. If you are using a version of Office Access prior to Office Access 2. Registry Editor. HKEYLOCALMACHINESOFTWAREMicrosoftJet4. EnginesODBC. Office Access 2. Jet database engine, named the Office Access Connectivity Engine ACE, which is not shared with other Windows applications. If you are using Office Access 2. HKEYLOCALMACHINESOFTWAREMicrosoftOffice1. Access Connectivity EngineODBC. Double click the Trace. SQLMode setting, change the value from 0 to 1, and click OK. If Office Access is open when you make this change, you must close and reopen Office Access for the change to take effect. After making this change in the registry, queries submitted to any ODBC data source are logged in a text file named Sqlout. Unless you delete this file or its contents, it continues to grow as new queries are executed and the tracing activity degrades performance. It is very important to return to the Registry Editor and turn the feature off by changing the Trace. SQLMode setting back to 0 when you are done testing. Running SQL Profiler also has a negative impact on performance, so try to avoid using it on a production server and close your Profiler traces when you are done testing. Before you can make productive use of these diagnostic tools, you must understand how Office Access interacts with SQL Server. Without that understanding, the SQL statements that you see in Profiler traces and in Sqlout logs can be quite puzzling. Understanding Dynasets. When you observe how Office Access communicates with SQL Server over ODBC, you will notice that most queries are executed very differently from the way you would expect. For example, if you migrate the Northwind data to SQL Server, link to the tables, and open the Shippers table in datasheet view while tracing is enabled, you probably expect to see a simple query such as SELECT FROM Suppliers, or perhaps a query that includes the schema name with the table, dbo. Suppliers, and that explicitly names the three columns in the table. Instead, both the Sqlout. SQL Profiler trace show that three statements are executed. The following is what is written to Sqlout. SQLExec. Direct SELECT dbo. Shippers. Shipper. ID FROM dbo. Shippers. SQLPrepare SELECT Shipper. ID,Company. Name,Phone. FROM dbo. Shippers. WHERE Shipper. ID OR Shipper. ID OR Shipper. ID OR Shipper. ID OR Shipper. ID OR Shipper. ID OR. Shipper. ID OR Shipper. ID OR Shipper. ID OR Shipper. ID SQLExecute MULTI ROW FETCH. SQLExec. Direct indicates execution of a non parameterized query.