How can a collection of objects be returned from a function 3

[benmillus]

What I am trying to do is return the objects 'cn' and 'rs' from the function below. This is what I have:

Function OpenRecordSet(cn_str, uid, pwd, sql)
Dim cn, rs, dbCollection()
CONST adOpenStatic = 3, adLockReadOnly = 1, adCmdText = &H0001
Set cn = CreateObject("ADODB.Connection")
cn.Open cn_str, uid, pwd
dbCollection(0) = cn
Set rs = CreateObject("ADODB.Recordset")
rs.Open sql, cn, adOpenStatic, adLockReadOnly, adCmdText
dbCollection9(1) = rs
OpenRecordSet = dbCollection
End Function

Thanks.

 

[EBGreen]

Try calling the function like this:

Set dbSomeVariable = OpenRecordSet(cn_str, uid, pwd, sql)

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[PHV]

At least you have to modify this:
Set dbCollection(0) = cn
Set dbCollection(1) = rs

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[tsuji]

Should make it like this.
[tt]
Function OpenRecordSet(cn_str, uid, pwd, sql)
Dim cn, rs
Dim dbCollection() [red]: redim dbCollection(1)[/red] 'or directly dim dbCollection(1) or dynamically change ubound
CONST adOpenStatic = 3, adLockReadOnly = 1, adCmdText = &H0001
Set cn = CreateObject("ADODB.Connection")
cn.Open cn_str, uid, pwd
[red]set[/set] dbCollection(0) = cn
Set rs = CreateObject("ADODB.Recordset")
rs.Open sql, cn, adOpenStatic, adLockReadOnly, adCmdText
[red]set[/red] dbCollection9(1) = rs
OpenRecordSet = dbCollection
[green]set rs=nothing
set cn=nothing[/green]
End Function

[green]'call it simply like this[/green]
dim cn_str,suid,spwd,ssql,dbSomeVariable
dbSomeVariable = OpenRecordSet(cn_str, suid, spwd, ssql)
'[green]etc until discarding them
set dbSomeVariable(0)=nothing
set dbSomeVariable(1)=nothing[/green]
[/tt]

 

[tsuji]

Also should correct a typo propagated.
[tt] set dbCollection[highlight]([/highlight]1) = rs[/tt]
and my own typo on tagging.
[tt] [red]set[/red] dbCollection(0) = cn[/tt]

 

[benmillus]

Thanks all for your thoughtful inputs to helping me find the solution. Special thanks to "tsuji" for putting everything together in the context of the function in question.

Ben