Multiple recordset in single function in access

[lokeshsu]

Hi all,

can we open multiple recordset in a single function. i have a code where i have to create folder and subfolders according to the table. so i want to use multiple recordset and created folders using queries with in the do while loop. below is the code, but i am unable to open multiple record set in a single function

 sql1 = "SELECT Tbl_Location.Region_Num FROM Tbl_Location GROUP BY Tbl_Location.Region_Num;"    AdoRS1.Open sql1, cn1
        Do Until AdoRS1.EOF Or AdoRS1.BOF
        reg = AdoRS1!Region_Num
        dirname = "C:\pdf_Docs\company\R" & reg
        MkDir dirname
        
            sql2 = "SELECT Tbl_Location.Reg_Num FROM Tbl_Location WHERE (((Tbl_Location.Region_Num)=" & reg & ")) GROUP BY Tbl_Location.Reg_Num;"
            AdoRS2.Open sql2, cn2
            
                Do Until AdoRS2.EOF Or AdoRS2.BOF
                gro = AdoRS2!Reg_Num
                dirname = "C:\pdf_Docs\company\R" & reg & "\G" & gro
                MkDir dirname
                
                        sql3 = "SELECT Tbl_Location.Dist_Num FROM Tbl_Location WHERE (((Tbl_Location.Region_Num)=" & reg & ") AND ((Tbl_Location.Reg_Num)=" & gro & ")) GROUP BY Tbl_Location.Dist_Num;"
                        AdoRS3.Open sql3, cn3
                        
                            Do Until AdoRS3.EOF Or AdoRS3.BOF
                            dis = AdoRS3!Dist_Num
                            dirname = "C:\pdf_Docs\company\R" & reg & "\G" & gro & "\D" & dis
                            MkDir dirname
                            
                                    sql4 = "SELECT Tbl_Location.Store FROM Tbl_Location WHERE (((Tbl_Location.Region_Num)=" & reg & ") AND ((Tbl_Location.Reg_Num)=" & gro & ") AND  (Tbl_Location.Dist_Num)=" & dis & ")) GROUP BY Tbl_Location.Store;"
                                    AdoRS4.Open sql4, cn4
                                    
                                        Do Until AdoRS4.EOF Or AdoRS4.BOF
                                        sto = AdoRS4!Dist_Num
                                        sto = Format(sto, "0000")
                                        dirname = "C:\pdf_Docs\company\R" & reg & "\G" & gro & "\D" & dis & "\T" & sto
                                        MkDir dirname
                                        
                                        AdoRS1.MoveNext
                                        Loop
                        AdoRS1.MoveNext
                        Loop
                AdoRS1.MoveNext
                Loop
        AdoRS1.MoveNext
        Loop

 

[CharlesChuckieCharles]

Not sure what the problem is, you've already opened four record sets in the code, ( which is obviously not the complete code )....

so i don't understand what you mean?

 

[SydneyGeek]

If you have multiple hierarchies, you could try using shaped recordsets -- see: How To Use the ADO SHAPE Command

And here's an article with a link to a Wizard for building shaped recordsets from a subdatasheet:
http://msdn.microsoft.com/en-us/library/office/aa155727(v=office.10).aspx

Denis