Unable to reuse ADO.Recordset connection using VBA script in Excel 
Author Message
 Unable to reuse ADO.Recordset connection using VBA script in Excel

I am successful in opening and using ADO.Connection and ADO.Recordset
objects in VBA script in Excel. I then close the objects and set them
to Nothing to to be released. I thenn open the ADO.COnnection object
again, but unable, however, to re-open this recordset object with a
different "SELECT" statement. I get a message "ODBC Driver does not
support required properties".

I will appreciate any insights in how to solve it.

Misha Goussev



Tue, 14 Dec 2004 06:21:22 GMT  
 Unable to reuse ADO.Recordset connection using VBA script in Excel

This problem may have something to do with the fact that you are re-using
the same connection from the OLE DB pool. I assume that you are re-opening
the connection within 60 seconds of closing it.

Try disabling connection pooling by adding

        OLE DB Services = -2

to your ADO connection string.

Also, I see you've specified "adCmdText", meaning that the "source"
argument for your Recordset.Open method is a SELECT or EXECUTE statement.
Is "filename" indeed a variable containing a SELECT statement?

Best wishes,

-Doug

This posting is provided "AS IS" with no warranties, and confers no rights.

Are you secure? For information about the Microsoft Strategic Technology
Protection Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.



Wed, 15 Dec 2004 07:19:14 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Problem reusing ADO.Recordset object in VBA script in Excel

2. Unclear on Reuse of Connection & Recordset Objects

3. Reusing ADO connections in HTA applications

4. ADO Connection Reuse

5. Reusing VBA/Access Forms thru DAO/ODBC/ADO

6. Exchange 5.5 Event Scripting - using ADO connections

7. Detecting how many recordsets are using an ADO connection

8. Using a RecordSet without an ADO connection

9. max recordsets using a single ado connection

10. vba script in excel to perform manipulations in excel

11. Excel 2002 hanging when using ADO (vba) + SQL Server 2000

12. Excel VBA using ADO to extract data from SQL Server

 

 
Powered by phpBB® Forum Software