Access updating using linked tables

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.

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. A Profiler trace shows the three corresponding Transact-SQL statements that are processed on the server.

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. SELECT "dbo"."Shippers"."Shipper ID" FROM "dbo"."Shippers" declare @p1 int set @p1=-1 exec sp_prepexec @p1 output, N'@P1 int,@P2 int,@P3 int,@P4 int,@P5 int,@P6 int,@P7 int,@P8 int,@P9 int,@P10 int', N'SELECT "Shipper ID","Company Name","Phone" FROM "dbo"."Shippers" WHERE "Shipper ID" = @P1 OR "Shipper ID" = @P2 OR "Shipper ID" = @P3 OR "Shipper ID" = @P4 OR "Shipper ID" = @P5 OR "Shipper ID" = @P6 OR "Shipper ID" = @P7 OR "Shipper ID" = @P8 OR "Shipper ID" = @P9 OR "Shipper ID" = @P10',1,2,3,3,3,3,3,3,3,3 select @p1 exec sp_execute 6,1,2,3,3,3,3,3,3,3,3 This example shows the typical behavior for processing a dynaset, which is the type of recordset Office Access opens when you open a datasheet or any bound form.

Before you can make productive use of these diagnostic tools, you must understand how Office Access interacts with SQL Server. The question marks are placeholders for parameters.

(22 printed pages) Click here for the Word version of this article.

This white paper discusses several of these issues and presents strategies for resolving them.

Most of these issues relate to performance or updatability.

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. All the quotation marks that you see around object names are comparable to the brackets that Office Access uses (and that also can be used in SQL Server) to handle spaces or other illegal characters in names.

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. SQLPrepare is used to define a parameterized query that is then executed with SQLExecute.

Leave a Reply