ADODB Recordset (Easy One)

[skald2k]

Private Sub Command1_Click()
    Dim results As ADODB.Recordset
    
    Set results = New ADODB.Recordset           ' Create new Recordset
    If Not (results.BOF Or results.EOF) Then
        MsgBox results.Fields.Count
    End If
End Sub

Why doesn't the above code work? It gives me an run-time error "operation not allowed when object is closed". However if I add a field to the recordset then it works. I need to create an EMPTY recordset for a function. Whats the problem here?

[mendhak]

Originally posted by skald2k

Private Sub Command1_Click()
    Dim results As ADODB.Recordset
    
    Set results = New ADODB.Recordset           ' Create new Recordset
    If Not (results.BOF Or results.EOF) Then
        MsgBox results.Fields.Count
    End If
End Sub

Why doesn't the above code work? It gives me an run-time error "operation not allowed when object is closed". However if I add a field to the recordset then it works. I need to create an EMPTY recordset for a function. Whats the problem here?

That's because you haven't done

results.open SQLSTRING

yet. Have you? When you create your recordset, it is already empty, but .eof and .bof don't count.

here's an example of how to create an empty recordset with 6 columns (from MSDN I think--copy and paste)


with results
.Fields.Append "Column1", adBSTR, 25
.Fields.Append "Column2", adInteger
.Fields.Append "Column3", adBSTR, 25
.Fields.Append "Column4", adVarChar, 25
.Fields.Append "Column5", adBSTR, 25
.Fields.Append "Column6", adDBTimeStamp, 25
end with

[skald2k]

Hmm okay I understand, although VB gives me a stupid "closed" error message.

I need the recordset to contain no columns though because that will indicate to my client that the SQL string has failed or is incorrect. If there ARE columns appended then it will mean that the SQL string worked but there just isn't any records to be found.