Multiple open Recordsets

[ngreenleaf]

Below is a code segment from one of my forms. I have used this same logic elsewhere and it works fine. This is the only place I think I've done it with 2 recordsets open at the same time. The second loop (on Delete1) only executes once - even when there are three records in the recordset. I have validated the queries... they work fine. I have added a loop on Delete1 that occurs immediately after the recordset is opened and verified that all three records from the query are in the recordset. Can anyone advise me regarding this?

Set Delete1 = Dbs.OpenRecordset(SQLSel1Query)
Set Delete2 = Dbs.OpenRecordset(SQLSel2Query)
DoCmd.RunSQL (SQLDel3Query)
If (Not Delete2.BOF) Or (Not Delete2.EOF) Then
Delete2.MoveFirst
End If
Do While Not Delete2.EOF
SQLDel2Query = "DELETE FROM table2 " & _
"WHERE table2.pri_key = " & Delete2![del2] & ";"
DoCmd.RunSQL (SQLDel2Query)
Delete2.MoveNext
Loop
Delete2.Close
If (Not Delete1.BOF) Or (Not Delete1.EOF) Then
Delete1.MoveFirst
End If
Do While Not Delete1.EOF
SQLDel1Query = "DELETE FROM table1 " & _
"WHERE table1.pri_key = '" & Delete1![del1] & "';"
DoCmd.RunSQL (SQLDel1Query)
Delete1.MoveNext
Loop
DeletePublishers.Close

NOTES: SQLSel1Query and SQLSel2Query are SELECT queries that must be run before data is deleted in table3 (SQLDel3Query). But due to referential integrity it is necessary to delete from table3 first, then table2 and finally table1. The SELECT statements select the primary keys to delete AS del1 and del2, respectively.

The table3 delete (SQLDel3Query) works fine. The loop to delete from table2 works fine. But the loop to delete from table1 stops after one iteration. I did verify that the first iteration is looking at the value at the top of the recordset. I have also verified that SQLDel1Query and SQLDel2Query are fine -- if the loop would iterate.

As an FYI... I solved the problem using the following logic:

1) open Delete1 recordset
2) loop through Delete1 recordset building a query like
"Delete from table1 where pri_key in ('val1','val2','...);
3) close Delete1 recordset
4) open Delete2 recordset
5) loop through Delete2 recordset creating a query like
"Delete from table2 where pri_key in (val1,val2,...);
6) close Delete2 recordset
7) run the three delete queries in the proper order

This works fine. Thus I am questioning if my problem is because I had two recordsets open at one time.

 

[JeremyNYC]

I'm not sure what's going on, but I have a couple of thoughts:
Why is
If (Not Delete2.BOF) Or (Not Delete2.EOF) Then
not
If (Not Delete2.BOF) AND (Not Delete2.EOF) Then
?

The latter is the standard way of checking if there are any records in the recordset. Actually, I would put the loop inside the if, as that will be a little clearer. I don't think these ideas will solve your problem, but they would make your code a little cleaner (unless I'm not getting something).

The other thought is that I wonder if it's possible that the deletions that get done in the first round are making it so that there's only one record to be deleted in the second round. Just a guess.

Another thing that won't make your code run any better, but will help out with making it readable, both for yourself and for folks trying to help you out, is that you might want to look into naming conventions, as it's a little tough to hang onto the what your variables are without them. And it'd be nice if Delete1 was used before Delete2!

Jeremy

=============
Jeremy Wallace
AlphaBet City Dataworks

http://www.AlphaBetCityDataworks.com

Take a look at the Developers' section of the site for some helpful fundamentals.

 

[ngreenleaf]

The variable names here are not the actual names... I just made numbers so that things would be blocked together. As for the if (not ...bof OR ...eof) -- you're logic works well for putting the loop inside and that would be a different style of doing it. My way is just the inverse of yours. Yours may be slightly more efficient... saving a line of code to execute (the while test).

The deletion order is specific and important. But my assumption was that after I ran the openrecordset with a query it had run that query and the recordset was permanent until you changed it. Are you indicating that the query will requery as the underlying data in the tables changes without me telling it to do that? If it requeries then this could effect my results.