Problem retrieving recordset from function

[Raydr]

Okay, I'm writing an inventory program that uses a DSN in order to access a MSAccess database.

Before, whenever I needed a recordset, I'd declare all the variables, open the db, open the record, etc in the sub itself.

I wanted to change it so I could just call a function and get the requested recordset. However, now that I've done that, I keep getting an error saying that the dataset is closed.

Here's what I've done (only showing relevant code):

Public rsTemp As New ADODB.Recordset
Public dbTemp As New ADODB.Connection

Public Sub Startup() 'run at startup
dbTemp.Open "dsn=" & DSN
End Sub

Public Function GetRecords(DSN As String, sql As String) As ADODB.Recordset
'ignore the DSN parameter
Dim grRS As New ADODB.Recordset

'Open the recordset
grRS.Open sql, dbTemp, adOpenDynamic, adLockOptimistic

'send it back to the sub that asked for it
Set GetRecords = grRS

'Close the recordset
grRS.Close
  
End Function

Public Sub PROBLEMSUB()

'Getting recordset and putting into rsTemp
Set rsTemp = GetRecords(DSN, "SELECT Value From Environment Where (Setting='CompanyId')")

'Retrieve my data
'THIS IS WHERE THE PROBLEM IS. VB is telling me that this recordset is closed! What am I doing wrong?
WarehouseId = rsTemp("Value")

End Sub

 

[Raydr]

Okay, removed the .close from GetRecords fixes it, but I get the feeling that's not the right way to do it.

I know the memory space isn't being taken up since the variable is destroyed once the function is over, but whatever.

I just don't understand why my recordset is closed when I'm closing the old recordset. If anyone can explain this to me, I'd appreciate it (I can't find any info on it anywhere).

 

[MarkSweetland]

Your GetRecords Function is only returning a reference to the new recordset created within the function and not a "new" ADODB.Recordset. Once you closed the recordset within the Function, it was closed. A better idea would be to pass a reference to the Recordset in ProblemSub to GetRecords and have the Function operate on that:

Public Function GetRecords(ByRef grRS As ADODB.Recordset, sql As String) As Boolean

    ' Check to make sure the recordset has been closed before open
    If grRS.State = adStateopen Then
        grRS.Close
    End If

    'Open the recordset
    grRS.Open sql, cn, adOpenDynamic, adLockOptimistic

    If grRS.EOF And grRS.BOF Then
        GetRecords = False
    Else
        GetRecords = True
    End If
    
End Function


Public Sub PROBLEMSUB()
    Dim blnHasRecords As Boolean
    Dim rsTemp As New ADODB.Recordset
    
    'Getting recordset and putting into rsTemp
    blnHasRecords = GetRecords(rsTemp, "SELECT Value From Environment Where (Setting='CompanyId')")
    
    'Retrieve my data
    If blnHasRecords Then
        WarehouseId = rsTemp("Value")
    End If

End Sub

Mark