Jim Lawrence
accessd at shaw.ca
Mon Jan 23 23:12:16 CST 2006
Hi Doris: Are you really sure of those limitations? Do they exist only when you use an ODBC connection? Are there other circumstances? I have tested and recorded, with an Access FE using ADO-OLE, 50,000 plus records downloaded in less than 2 seconds. That seems to be a lot more than only a 4096 byte packet. Comments please Jim -----Original Message----- From: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Mike & Doris Manning Sent: January 23, 2006 4:38 PM To: dba-sqlserver at databaseadvisors.com Subject: Re: [dba-SQLServer] Access Connection to SQL Server (was: Upsizewizard and named queries) Access 2003 and SQL Server 2000. Impact on the developer = none Impact on the user = HUGE if you have a lot of data coming over at once... In my case, my main screen contains a list of all open work orders. Even with a stored procedure targeting a specific set of work orders, the load time for a list box bound to the stored procedure is 6 seconds at 4096 packet. When I moved to VB.Net and changed the packet size to 32767, the same amount of data took 1 second. I know that doesn't sound like much of a savings, but my end users reported a huge difference in response time throughout the app. An Access ADP automatically sets up 3 connections to the SQL BE so I found it more economical to use AccessConnection because that allowed me to use one of the existing 3 connections instead of launching another one. Doris Manning mikedorism at verizon.net _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com