Question about using disconnected recordset
I didn't see that this question was addressed specifically, so I'm convinced I'm just doing something wrong and would appreciate any guidance anyone can provide.
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 ...
Public Function OpenRS(ByVal sql, ByVal FilePath)
If Right(FilePath, 4) = "xlsx" Or Right(FilePath, 3) = "xls" Then
Set ExcelApp = CreateObject("Excel.Application")
Set WBook = ExcelApp.Workbooks.Open(FilePath)
Set conn = CreateObject("ADODB.Connection")
conn.Provider = "Microsoft.Jet.OLEDB.4.0"
conn.Mode = adModeRead
conn.CursorLocation = adUseClient
conn.Open = "Data Source=" & FilePath & ";" &_
"Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1;"";"
Set rs = CreateObject("ADODB.Recordset")
rs.CursorType = adOpenStatic
rs.CursorLocation = adUseClient
rs.LockType = adLockReadOnly
rs.Open sql, conn
Set OpenRS = rs
Set rs.ActiveConnection = Nothing
I'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.