With SQL Server, specifying the OLE-DB driver you will see a performance gain. Your database queries will go through just three API layers rather than 4: ADO -> OLE-DB -> OLE-DB Driver.

Using OLE-DB to connect to your SQL Server database is as simple as changing your connection string to the following:

Provider = SQLOLEDB
Server = SERVER NAME
User ID = USERID
Password = PASSWORD
Initial Catalog = DBNAME

Replace the bold words with the information specific to your Db.
More info

Problems with SQLOLEDB

When you try to create a ADO recordset based on a #Temp table created within a stored procedure called by ADO using the SQLOLEDB provider, you may encounter one of the following error messages:

1. 3704 - The operation requested by the application is not allowed if the object is closed.
2. Run-time error '3704': Operation is not allowed when the object is closed.

The SQL Server OLEDB provider (SQLOLEDB) has a new behavior designed to provide more accurate information to the caller about what happened in the procedure. Each SQL statement within a stored procedure returns a "result," either a count of rows affected, or a resultset.
The SQL Server ODBC provider (MSDASQL) does not provide information about the results of individual SQL statements within a stored procedure. The only result that comes back from a stored procedure execution is the result of the SELECT statement if it has one. This is why the problem does not manifest with MSDASQL.

To get SQLOLEDB to behave as the SQL ODBC driver did, you must use SET NOCOUNT ON. Put SET NOCOUNT ON in the stored procedure or in the SQL statement used with ADO as shown in the code sample below. Alternatively, you could use MSDASQL.