COM method returning Array/ Variant recordset

[DKL01]

Hello All:

I am wondering how to write and method which returns an array or variant recordset.

I need to write a method which connects to database, process the record set and returns the variant recordset.

Thanks
Dilip

 

[Ruairi]

Here is an example of one way to do it. I have these procedures in an active-x dll server.

Public Sub OpenRecordset(ByRef rs As ADODB.Recordset, ByVal sql As String)
    Set rs = New ADODB.Recordset
    rs.Open sql, m_cn, adOpenKeyset, adLockPessimistic, adCmdText
End Sub

Public Sub CloseRecordset(ByRef rs As ADODB.Recordset)
On Error Resume Next
    rs.Close
On Error GoTo 0
    Set rs = Nothing
End Sub

If takes as parameters a refrence to a recordset and the sql string to generate that recordset (I have removed the code that adjusts that querey string as it was not relevant to your question). So, to call it from a program you would use code like this (assuming the object varable that points to this dll server is SQLUtils).

Dim TestRS As ADODB.Recordset 
Dim sql As String 

sql = &quot;select * from testtable where rec < 5&quot; 

SQLUtils.OpenRecordset TestRS, sql 

While Not TestRS.EOF 
    'work with the returned recordset here 
Wend 

SQLUtils.CloseRecordset TestRS

Since the rs variable is being passed into the procedures in the .dll server ByRef, the server actually just gets a pointer to the memory location of the TestRS variable in the client program. All changes it makes to it's local rs variable are actually being made to the TestRS in your client program.
HTH Ruairi

Could your manufacturing facility benefit from real time process monitoring? Would you like your employees to be able to see up to the minute goal and actual production?
For innovative, low cost solutions check out my website.

http://www.plccontroltechnologies.com