Creating the equilvalent of a WHILE EXISTS SQL loop

[BenFranske]

Hi, I have the need to create the equivalent of a WHILE EXISTS SQL loop in Access and am thinking there should be a way to do this with some VBA code.

Some background: I'm working on a family tree database so each record (back to the root) has a parent record in the same table creating a hierarchical tree. To make things easier to work with, sort, etc. I have created lineage and generation fields which show the parents back to the root. This is based on the "More Trees & Hierarchies in SQL" article on the SQLTeam website.

Right now I'm trying to write update triggers in VBA so that if changes are made or new records added the lineage is automatically calclulated and entered into the table. Sample code is available at the SQLTeam site but it takes advantage of the WHILE EXISTS SQL command which is unsupported in Access. I need to find an equvalant way of doing things in VBA/Access.

The bottom line: I need to run this query:

UPDATE Households AS T INNER JOIN Households AS P ON T.Relatives_Parents=P.GED_Family_ID SET T.Generation = P.Generation+1,
T.Relatives_Lineage = P.Relatives_Lineage+Ltrim(T.Relatives_Parents)+'/'
WHERE P.Generation>=0
And P.Relatives_Lineage Is Not Null
And T.Generation Is Null;

Until no records are returned by:

SELECT * FROM Households WHERE Households.Generation

in other words until all records have a generation. The code proposed by the SQLTeam article would look like:

WHILE EXISTS (SELECT * FROM Households WHERE Households.Generation Is Null)
UPDATE Households AS T INNER JOIN Households AS P ON T.Relatives_Parents=P.GED_Family_ID SET T.Generation = P.Generation+1,
T.Relatives_Lineage = P.Relatives_Lineage+Ltrim(T.Relatives_Parents)+'/'
WHERE P.Generation>=0
And P.Relatives_Lineage Is Not Null
And T.Generation Is Null;

but this obviously won't work in Access.

Any ideas about how to accomplish the same thing in Access/VBA?

 

[ByteMyzer]

In Visual Basic, you could use something along these lines:

[COLOR="Navy"]Dim[/COLOR] rs [COLOR="navy"]As[/COLOR] ADODB.Recordset

[COLOR="navy"]Set[/COLOR] rs = [COLOR="navy"]New[/COLOR] ADODB.Recordset

rs.Open "SELECT *" _
    & " FROM Households" _
    & " WHERE Households.Generation Is Null", _
    CurrentProject.Connection, adOpenKeyset, adLockReadOnly, adCmdText
[COLOR="navy"]Do[/COLOR]
    CurrentProject.Connection.Execute _
        "UPDATE Households AS T" _
        & " INNER JOIN Households AS P ON T.Relatives_Parents=P.GED_Family_ID" _
        & " SET T.Generation = P.Generation+1," _
        & " T.Relatives_Lineage =" _
        & " P.Relatives_Lineage+Ltrim(T.Relatives_Parents)+'/'" _
        & " WHERE P.Generation>=0" _
        & " And P.Relatives_Lineage Is Not Null" _
        & " And T.Generation Is Null;"
    rs.Requery
[COLOR="navy"]Loop Until[/COLOR] rs.RecordCount = 0

 

[BenFranske]

Thanks! That got me started on the right track. I thought it might be something to do with recordsets but I'm not especially familiar with them and needed a little extra guidance.

As an aside, is there a compelling reason to use ADO instead of DAO? I had a problem with an unknown type when implementing your ADO code so I rewrote it as DAO which seems to work.

 

[ByteMyzer]

I just used ADO for illustrating the Recordset object. You can use DAO or ADO. The main point was to illustrate the VBA methodology.