Function return recordset

[almoes]

I am trying to return a recordset from a function, but it doesn't work. Is it anyway possible? I also tried disconnecting it but nothing. Any ideas? Thanks.

Cheers,
alej

 

[paulbent]

Yes it's perfectly possible. If the rs is passed in as a parameter (because objects are passed by reference) when the function returns, the calling proc will have a reference to the modified rs.

Or the function can return the rs with this statement:

Set MyFunction = MyRS

Paul Bent
Northwind IT Systems

http://www.northwindit.co.uk

 

[almoes]

but does it have to be a disconnected recordset if I close the connection to the db in the function?

 

[paulbent]

Something like this should work:

Function fGetDisconnectedRS(ByVal strCon As String, _
ByVal strSQL As String) As ADODB.Recordset

 Set fGetDisconnectedRS = New ADODB.Recordset

 With fGetDisconnectedRS
  .ActiveConnection = strCon
  .CursorLocation = adUseClient
  .CursorType = adOpenStatic
  .LockType = adLockBatchOptimistic
  .Source = strSQL
  .Open
   Set .ActiveConnection = Nothing
 End With

End Function

Paul Bent
Northwind IT Systems

http://www.northwindit.co.uk

 

[almoes]

I set this properties in the function, but get the folllowing error:
ADODB.Recordset (0x800A0BB9)
Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.
on the line i set the active connection to nothing

alej

 

[Swi]

Dim rs as DAO.Recordset

Set rs = ReadExcel(lblInputFile.Caption, Replace(ExcelSheetName, "'", "&quot)

Private Function ReadExcel(ByVal sFile As String, ByVal sSheetName As String) As DAO.Recordset
On Error GoTo fix_err
Dim Db As DAO.Database
Dim Temprs As DAO.Recordset
Set Db = OpenDatabase(sFile, False, True, "Excel 8.0; HDR=YES; IMEX=1;&quot
Set Temprs = Db.OpenRecordset("SELECT * FROM [" & sSheetName & "]&quot
Temprs.MoveFirst
Set ReadExcel = Temprs
Set Temprs = Nothing
Exit Function
fix_err:
MsgBox Err.Description + " " + _
Err.Source, vbCritical, "Import"
Err.Clear
End Function

Swi

 

[almoes]

this means it doesn't work with adodb?

 

[Swi]

It will work with ADO. My example just uses DAO.

Swi

 

[almoes]

Thanxs a lot, could not get it to work, so I simply dump it to an array, no time for more debugging...

cheers,
alej