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

On 23 Jan 2006 at 20:10, Dan Waters wrote:

> 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!
> 

One possible gotcha:
http://support.microsoft.com/?kbid=903002
A "Failed to reserve contiguous memory" error message may be logged in the 
SQL Server error log when users connect to an instance of SQL Server by 
using a network packet size that is larger than 8,060 bytes-- 
Stuart