Disconnected Recordset help - Operation not allowed when object is closed

Question

Hello,I've searched these forums and elsewhere ad nauseum, but cannot figure out where I'm going wrong here. I need to be able to create a recordset of data from an excel file that allows me to then close the connection and continue to use the data. Here is the bit of code that gives the error ...&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Public Function OpenRS(ByVal sql, ByVal FilePath)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; If Right(FilePath, 4) &nbsp; &nbsp; &nbsp;&nbsp;&nbsp; = "xlsx" Or Right(FilePath, 3) = "xls" Then&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Set ExcelApp&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; = CreateObject("Excel.Application")&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Set WBook&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; = ExcelApp.Workbooks.Open(FilePath)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; End If &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Set conn&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; &nbsp;&nbsp; = CreateObject("ADODB.Connection")&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; conn.Provider&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; = "Microsoft.Jet.OLEDB.4.0"&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; conn.Mode&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; = adModeRead&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; conn.CursorLocation&nbsp; = adUseClient&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; conn.Open&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; &nbsp; = "Data Source=" &amp; FilePath &amp; ";" &amp;_&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1;"";"&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Set rs&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; = CreateObject("ADODB.Recordset")&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; rs.CursorType&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; = adOpenStatic&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; rs.CursorLocation &nbsp; &nbsp; &nbsp;&nbsp; = adUseClient&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; rs.LockType&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; = adLockReadOnly&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; rs.Open sql, conn&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Set OpenRS&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; &nbsp; = rs &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Set rs.ActiveConnection&nbsp; = Nothing&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; conn.close&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; rs.close&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; End FunctionI've read the key to creating a disconnected recordset is to first set the 'cursorlocation' of the recordset to 'adUseClient', then Open, then set the 'ActiveConnection' to Nothing, but no matter what order I've tried I continue to get the error ..."Operation is not allowed when the object is open"Any guidance would be greatly appreciated.

tanyayatskovska · Answer

Hi Jeremy, &nbsp; You'd better use the DDTDriver object to get data from an Excel file. Go through the "Using the DDTDriver Object to Retrieve Data From Excel Files" article. This would be easier than using ADO to work with Excel files. &nbsp;

headmusic · Answer

Thank you for your reply, Tanya. I had read up on the DDT driver before making the call to go with ADO a couple of years back. I'll read over it some more, but it doesn't appear it have the functionality I need, namely being able to access data using column names. I could, of course, read and compare first row data programatically, and will look at that as an option.

So, is it not possible to create a disconnected ado recordset? I have an already established keyword driven framework that uses excel tables to specify what tests to run, what actions to take against what object, and what data to specify/verify in a given action. It has been working fine using ADO. Now, however, I'm adding a new piece that iterates through an external data file. The external data portion of the framework is generic and can use any data file, which is why I need to be able to access data by column names.

headmusic · Answer

Okay, I see now that you can specify the column name using the DDTDriver. The problem I'm running into has to do with losing my open connection to the excel file. Initially it works fine. I had a problem early on with multiple excel processes being left open, so I added a line to my 'Private Sub Class_Terminate()' Event so that anytime an object is set to 'nothing' it would also terminate any lingering excel processes. If I didn't do this then before long I'd have multiple processes running and it would get out of sync.

But now that I'm attempting to loop through a recordset of test data that then injects this data into the same framework flow, once one of the lines that sets an object to 'nothing' gets run it terminates my excel process causing my recordset to lose its connection to the file.

So, would using the DDTDriver remedy this? Can I create recordsets, or can I continue to access the data in the excel file without having to maintain an active connection?

Forum Discussion

Disconnected Recordset help - Operation not allowed when object is closed

3 Replies

Recent Discussions

TestComplete freezes during mobile test execution in Legacy mode

Is there any way to get code completion to function when using classes?

Ajay Hinduja - Android Image Touch – "Interface Not Supported" Error

Related Content

The operation cannot be performed, because the user session is disconnected.

The operation cannot be performed, because the user session is disconnected.

ado recordsets wrong with datatype text