Help Opening Multiple Recordsets.

[Robeen]

Hi!

VB 6.0 - MS Access 2000.

I'm trying to use two recordsets simultaneously as follows:

Set rstFolio = New ADODB.Recordset
Set rstCode = New ADODB.Recordset

strSQLText = "Select [Folio] from [StudentDataTest] order by [Folio]"
Set rstFolio.DataSource = ExecuteSQL(strSQLText, strMsgText)
rstFolio.MoveFirst

Do While Not rstFolio.EOF '1. There is another Folio.
strNewFolio = rstFolio!Folio
strSQLText = "Select [Amount],

 from [Receipts]" _
 & " Where [Folio] = '" & strNewFolio & "' order by [Code]"

Set rstCode.DataSource = ExecuteSQL(strSQLText, strMsgText)
    rstCode.MoveFirst
    strCode = ""
    strPreviousCode = ""
    curAmount = 0
    Do While Not rstCode.EOF   '2. There is another Payment.
        strCode = rstCode!Code
        curAmount = rstCode!Amount
    Loop	 '2. Do While Not rstCode.EOF
    Set rstCode = Nothing
    rstFolio.MoveNext
    strNewFolio = ""
Loop              '1. Do While Not rstReceipts.EOF
Set rstFolio = Nothing

The code in "ExecuteSQL(strSQLText, strMsgText)" - declared as follows:
Public Function ExecuteSQL(ByVal SQL _
    As String, MsgString As String) _
    As ADODB.Recordset

declares and opens a connection object & then - after returning a recordset - closes the connection.

I get:
"Run-Time Error 3704
Operation is not allowed when the object is closed."
as soon as I try & do a Move First on the second Recordset -"rstCode.MoveFirst".

I have been reading in the VB 6.0 Help files about opening multiple recordsets but I'm not understanding what I need to do to allow me to have two or more recordsets open & running simultaneously.

I would appreciate it if I could be pointed in the right direction.

Thanks!!

Robin

 

[CCLINT]

Well, the best part you've left out....

Post the code in ExecuteSQL.
You probably didn't disconnect the recordset first (ActiveConnection = Nothing) or didn't use a Client Side Cursor (rs.CursorLocation = adUseClient)

 

[Robeen]

Hi CClint!

Here's the ExecuteSQL Module:

Public Function ExecuteSQL(ByVal SQL _
As String, MsgString As String) _
As ADODB.Recordset

'Executes SQL and returns Recordset & SQL Message.

Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim sTokens() As String

On Error GoTo ExecuteSQL_Error

sTokens = Split(SQL)
Set cnn = New ADODB.Connection
cnn.Open ConnectString

If InStr("INSERT,DELETE,UPDATE", _
UCase$(sTokens(0))) Then
cnn.Execute SQL
MsgString = sTokens(0) & _
" query successful"
Else
Set rst = New ADODB.Recordset
rst.Open Trim$(SQL), cnn, _
adOpenKeyset, _
adLockOptimistic
rst.MoveLast 'get RecordCount
Set ExecuteSQL = rst
MsgString = rst.RecordCount & _
" records found from SQL"
End If
ExecuteSQL_Exit:
Set rst = Nothing
'Set cnn = Nothing
Exit Function

ExecuteSQL_Error:
MsgString = "ExecuteSQL Error: " & _
Err.Description
Resume ExecuteSQL_Exit
End Function
__________________________________________________________

Public Function ConnectString() _
As String
ConnectString = "Provider=" & _
"Microsoft.Jet.OLEDB." & _
"4.0;Data Source=" & _
App.Path & "\bpsacdat-3-14-03.mdb"

End Function

Thanks!

Robin

 

[Robeen]

Actually -
This:
'Set cnn = Nothing
Is Active:
Set cnn = Nothing
Sorry - I tried commenting it out to see if the fact that the conection was 'Nothinged' was what was causing the '3074'.

Robin

 

[CCLINT]

After:
Set rst = New ADODB.Recordset

Add:

rst.CursorLocation = adUseClient

And replace this:
Set rst = Nothing
'Set cnn = Nothing

with:

Set rst.ActiveConnection = Nothing
If cnn.state=adStateOpen then cnn.Close
Set cnn = Nothing

 

[Robeen]

Thanks CCLINT,

I am still getting this message:
"Run-Time Error 3704
Operation is not allowed when the object is closed."
as soon as I try & do a Move First on the second Recordset -"rstCode.MoveFirst".

The odd thing is that I was able to run this successfully before making your last suggested changes.
Then - again, before I instituted your recommendations, I started getting this error again on the rstCode.MoveFirst !

It appeared to be working ok for a while there.

Do you have any example code that uses multiple recordsets like this - or should I be trying this another way?

Thanks!

Robeen

 

[CCLINT]

Two other tries would be to
Test this with the db locally, if it is currently on a server, and see what happens,
and,
to open the connection prior to going into the loops, and then pass the connection to the function which opens the recordset, and, in doing so, preventing the connection from having to open and close multiple times with-in a short period.

If this doesn't help, then there must be something else in your code which has been overlooked (step through the recordset proceedure and see if it is correctly branching into sections of code which you think it should.

Otherwise, I would need to ask you to post again the reordset proceedure code with the changes which I asked you to make.

 

[Robeen]

Thanks CCLINT!

You know - it's been working again.
I commented these two lines out:

'Set rstFolio = Nothing
'Set rstCode = Nothing
Trouble is - now I'm not closing these recordsets at all. I'm trying to find out what the consequences are of leaving the recordsets open like that.

I do have a question:
Am I trying something that should be done another way - or is this a common way of achieving what I'm trying to do?

I think you make a good suggestion when you recommend opening and closing the connection once instead of multiple times. I'm going to try that. I can't have it be temperamental like that!
I'm also going to have to step through the recordset procedures to see what exactly is happening at different points.

Thanks again CCLINT! I'll post here when I've tried a few things.

Robeen

 

[CCLINT]

>I commented these two lines out...

Actually, you should then change then to:
rstCode.Close
Set rstCode = Nothing

for both, IN ADDITION to the exact changes I supplied.

Yes, there is probalby a better method such as using an INNER JOIN and a GROUP BY with a SUM() function in a Select statement to get the totals for all records in the first recordset

 

[Robeen]

Hi CCLINT,

I took your suggestion about re-writing my select statement to get all my totals in the first recordset.

Here's what it looks like:

SELECT StudentDataTest.FOLIO, Receipts.Code, SUM([Receipts.Amount]) AS SubTotal
FROM StudentDataTest INNER JOIN Receipts ON StudentDataTest.FOLIO = Receipts.Folio
GROUP BY StudentDataTest.FOLIO, Receipts.Code;

This is a lot better than what I was trying to do in VB as it gives me all I need - in the order I need it in - all in one sweep.

I was so focused on trying to make those recordsets work in tandem that I wasn't thinking of reworking the SQL. Part of the reason for that is that I'm still in 'Learn VB' mode.

I appreciate your help. I'm still real interested in getting to the bottom of using multiple recordsets - just so I know I CAN if I ever need to.

Many thanks again.

Robeen

 

[CCLINT]

Yes, you can do the multiple rs. But I think your code isn't the same as I'm thinking it should be after the changes.
Important thing is to close the rs each time before openning it again, and opening the connection just once.

 

[Robeen]

Hi CCLINT.

I'm going to work on this a little more.

May I email you after I've made some progress - maybe next week or later? If so - please email me at rsengupta@lifetouch.com. I don't know about pasting long code here . . .

Robeen

 

[CCLINT]

Ok. But the final results, at least the structure, need to be posted afterwards.

 

[Robeen]

I agree - I just felt uncomfortable putting the code up repeatedly. Once it is figured out the working structure can be posted.
Please email me - so I'll have an email address for you.
Thanks,
Robeen