[dba-SQLServer] Access Connection to SQL Server (was: Upsizewizard and named queries)

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




More information about the dba-SQLServer mailing list