Martin Reid
mwp.reid at qub.ac.uk
Wed Oct 8 15:14:50 CDT 2008
>From that article Access sends SELECT * FROM Supplier SQL Server does SQLExecDirect: SELECT "dbo"."Shippers"."ShipperID" FROM "dbo"."Shippers" SQLPrepare: SELECT "ShipperID","CompanyName","Phone" FROM "dbo"."Shippers" WHERE "ShipperID" = ? OR "ShipperID" = ? OR "ShipperID" = ? OR "ShipperID" = ? OR "ShipperID" = ? OR "ShipperID" = ? OR "ShipperID" = ? OR "ShipperID" = ? OR "ShipperID" = ? OR "ShipperID" = ? SQLExecute: (MULTI-ROW FETCH) What you need to do is to run the SQL Server Profile while executing some statements to SQL Server form Access. This will show you whats happening. See this one http://articles.techrepublic.com.com/5100-10878_11-5054787.html You might know the authors. VBG Its important to understand as well what Access does and dosnt do. This relates to SQL Server 2000 I assume its still the same. When data is queried remotely in Access, all of the data is returned to the client, and any filtering and sorting is done on the client side. Because SQL Server data is usually queried over a network from a client, major network bandwidth issues can occur. Therefore, when moving your back end to SQL Server, it is important to redesign your queries so as to return only the required set of data to the client (rather than the whole data set). For example, a query behind an Access form would be: Martin Martin WP Reid Information Services Queen's University Riddel Hall 185 Stranmillis Road Belfast BT9 5EE Tel : 02890974465 Email : mwp.reid at qub.ac.uk