Options for best MS Access ADO to SQL Server Connection Performance

Question

What provider and driver offer the best performance when connection to SQL Server using ADO?

I'm connecting MS Access 2007 to SQL Server 2008.

Provider Options:

1. OLE DB provider for ODBC (MSDASQL.1) (default provider)
2. OLE DB provider for SQL Server (SQLOLEDB)
3. There may be other options that I'm not aware of

Driver Options:

1. SQL Server (version 2000.85.1132.00 - SQLSRV32.DLL 4/14/2008)
2. SQL Server Native Client 10.0 (version 2007.100.2531.00 SQLNCLI10.DLL 3/30/2009)
3. There may be other options that I'm not aware of.

Answer 1

You should test it in your specific environment to be sure. Whatever the performance differences are, they should be negligible.

Answer 2

Microsoft's Official Statement is here: http://msdn.microsoft.com/de-de/library/ms130978.aspx

In short: For an ADO-Connection, use SQLOLEDB.

MSDASQL is deprecated for a long, long time and does not do well with varchar(max) fields, for example.

When using the Native Client, you will have to specify DataTypeCompatibility=80, which removes many of the new features anyway, so you will gain little. Also, the Native Client will probably not be on your client computers by default, so you will need to install it.