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

Hi Doris,

I did some looking and found this on MSDN:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/
mdrefsqlprovspec.asp

It describes using a service provider called SQLOLEDB, and from what I can
read it does allow changing the Packet Size from the default of 4096 up to
32767.

Something like:  

	Set CN = New ADODB.Connection
	CN.Provider = "SQLOLEDB"
	CN.Properties("Packet Size").Value = 32767

I think it should be possible to use this service provider when creating a
connection to SQL Server from an Access FE.  Although to use bound forms in
an Access FE and SQL Server BE you must use the service provider of
Microsoft.Access.OLEDB.10.0, which is unique to AccessConnection.

I wanted to find out if you have tried using SQLOLEDB as the service
provider, and if so what was your experience.  

My plan is to use an Access .mdb FE and instantiate the connection when the
FE is opened.

And, if anyone else can add to this, please speak up!


Thanks!
Dan

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Mike &
Doris Manning
Sent: Monday, January 23, 2006 6: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