• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 794
  • Last Modified:

Need advice on approach: one recordset, or multiple recordsets?

Fellow Experts,

One of my banking clients uses an Access database app I developed for them to facilitate scheduling and reporting in their rather large branch network.  I have to develop a new report for them that basically creates, for each branch, a report that will look similar to the mock-up attached here.  The report will be deployed as Excel files, one for each branch.

Since this project requires integration between Access and Excel, I am asking for Experts in both zones to weigh in.

I will have the data needed to populate the report, and I do not need help with the SQL for the queries.  What I do need is advice on the approach for taking the query data and getting them into the Excel workbooks.  The approaches I am considering are:

1) Open 7 recordsets for each branch, one for each day of the week.  Each recordset would have the Lobby and Drive-Up counts for its particular weekday.  I would then use Excel's CopyFromRecordset method to move the data into Excel

2) Open 1 big recordset for all branches and all days, loop through that recordset, and populate Excel one cell at a time

Method #1 should be faster at populating Excel, but having to open and close 7 recordsets for each branch, when the users could be doing this for several hundred branches at a time, worries me a little.  Method #2 might be faster on the Access side, but then we have a potentially rather large recordset held in memory, plus Excel can be slow when you have to populate one cell at a time.

I would appreciate your opinions before I get started with the work in earnest, most especially if you see other approaches that might make more sense.

Cheers, and thanks,

Patrick Matthews
Patrick Matthews
  • 6
  • 3
  • 3
  • +3
5 Solutions
Saurabh Singh TeotiaCommented:

This might help you....This is what i do if i a large database and lot of values to copy...then what i do...is i create my own select temp query...passing on my varaibles through excel...and copying the complete query into the range rather then one cell as doing calcualtions in access is faster...

for instance here is a part of my temp query for one of the reports that i make...may be this might help you...
Dim A As Object
Dim MyAccess As Database
Dim MyQuery As String
Dim MyNewQuery
Dim MyTemp As DAO.Recordset
Dim xSelect As String
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.StatusBar = "Importing Data From Access"
Sheets("Import").Visible = True
xdate = [dDate]
Smonthdate = Sheet5.Cells(5, 6)
Emonthdate = Sheet5.Cells(5, 7)
With Application
        .Calculation = xlManual
        .MaxChange = 0.001
    End With
    ActiveWorkbook.PrecisionAsDisplayed = False
Set MyAccess = OpenDatabase("database path")
xSelect = " SELECT Ttable.Agent_Name, Ttable.Login_Id, Sum(Ttable.STime) AS SumOfSTime, Sum(Ttable.AXTime) AS SumOfAXTime, Sum(Ttable.[0Time]) AS SumOf0Time, Sum(Ttable.PBTime) AS SumOfPBTime, Sum(Ttable.DBTime) AS SumOfDBTime, Sum(Ttable.MTime) AS SumOfMTime, Sum(Ttable.TTime) AS SumOfTTime, Sum(Ttable.ITTime) AS SumOfITTime, Sum(Ttable.TETime) AS SumOfTETime, Sum(Ttable.PRTime) AS SumOfPRTime, Sum(Ttable.B1Time) AS SumOfB1Time, Sum(Ttable.B2Time) AS SumOfB2Time " & _
            "FROM Ttable " & _
            "WHERE (((Ttable.DATE)>=" & CDbl(Smonthdate) & " And (Ttable.DATE)<=" & CDbl(Emonthdate) & ")) " & _
            "GROUP BY Ttable.Agent_Name, Ttable.Login_Id;"
On Error Resume Next
MyAccess.QueryDefs.Delete ("Temp1")
On Error GoTo 0
Set MyNewQuery = MyAccess.CreateQueryDef("Temp1", xSelect)
Set MyTemp = MyAccess.OpenRecordset("Temp1")
'**********Importing From Access**********
Selection.CopyFromRecordset MyTemp
MyAccess.QueryDefs.Delete ("Temp1")

Open in new window

Rey Obrero (Capricorn1)Commented:
cannot give you a direct advice which way/method to use. why are you worried about the memory, how much memory do you think the recordset will utilize?

I will test first.

1.running the codes in access.
  a. test method 1
  b. test method 2

2.running the codes in excel.
  a. test method 1
  b. test method 2

and let the results be the judged....
Hi Patrick

Option 1:
I would go with method 2, with the following modification to the process:
1. Use a big Recordset, and output to csv file, this should be fast,
2. Close the Recordset, then open the cvs, and start inserting values to Excel.
This way, Access and Excel won't be open at the same time, so the memory issue doesn't exist.

Option 2:
Another option worth thinking about is, which I used in one of my projects is:
1. Have all the info which is in the Excel sheet, have it in Access, then dump the whole thing to a csv file,
2. then Format each Excel file seperately, using Access.

Option 3:
I don't know if this is possible with Excel:
1. Output Access data to csv file,
2. Have your Formated Excel sheets refer to a csv file, and take the data from it.
I know in Excel, make a cell take the data from another cell, either in the same beet, or another sheet (as long as they are in the same book), BUT can a cell get the data from yet another Excel book (which in out case will be the csv file).


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Jim P.Commented:
Is all this data actually in Access? Or is it in a SQL or other large DB system?

If it is, can you create views in the DB? Or a parallel DB? That would take the a large load off Access and then from there it would be about equal in either method.

Maybe look at SQL Server Reporting Services.
Patrick MatthewsAuthor Commented:
Thanks for the replies so far, gang!

All of the data needed for the report calculations resides in Access.  (It started out on a SQL Server, but there is a separate process that aggregates the data and imports it into Access each month.)

If I go with a 1-recordset apprach, I guess the recordset would not get **that** big--basically we would have something like 91 records per branch (7 days/wk x 13 records per day), and each record would probably have fewer than 12 columns, mostly numbers, so even with 1000 branches in one shot the number should be manageable.

As I see it, the tradeoffs are...

For #1, The opening and closing of lots of small recordsets is more expensive than opening one big one, but then again the transfer to Excel side runs faster with CopyFromRecordset than if we populate one cell at a time.

For #2, We get faster in Access, but slower in Excel

On a net basis, I have no context for judging which approach is faster end to end...

Patrick MatthewsAuthor Commented:
Some more info that may be helpful:

1) I plan to use an Excel XLT as the basis for the Excel files, as that makes the workbook creation and formatting a no-brainer.  The template will have formulas that perform all the calculations.  The only cells actually being populated by Access are the lobby and DU transaction counts by weekday and hour, plus eader info like branch name.

2) If I use the CopyFromRecordset approach, all of my recordsets will have but two columns: one for lobby transactions and one for DU transactions

Gustav BrockCIOCommented:
The approach I've used for similar tasks is to make it in one go.

1. Write the data from your query to a temp table preparing the data as closely as possible to what is needed in Excel as this cuts down formatting etc. in Excel.
Previously, you could write directly to a linked Excel table, but these days such a table is read-only from Access. Also, you will have too many rows to be held by a WorkSheet so you will probably need a true table.

This allows you to run the process from either Access (controlling Excel, which is what I prefer) or from Excel only - as you prefer.

2. Open a new Worksheet from your template. Let your six boxes be defined as Ranges.

3. From the temp table, read your data for that branch and write them into a second WorkSheet of the same WorkBook. Clear this WorkSheet first if this is not the first branch. Copy from this (range) to the six ranges of the first WorksSheet.

4. Either via code or by Copy Special Format Only from a predesigned third master WorkSheet, apply all formatting to the six ranges.

5. Save the now filled-in and formatted WorkSheet as the only WorkSheet to a new WorkBook.

Patrick MatthewsAuthor Commented:

I thought of another approach this morning that tries to combine the best aspects of both...

Open one large recordset, with all of the results for all branches.  Then, as I loop through the recordset,
populate 7 arrays (one for each weekday), and then use Excel's array transfer to populate each workbook.
Having one large recordset eliminates the overhead of constantly opening and closing recordsets, and
on the Excel side, array transfer is much, much faster than populating one cell at a time.


> populate 7 arrays (one for each weekday), and then use Excel's array transfer
That is interesting, I didn't know you can transfer arrays between Access and Excel.

Gustav BrockCIOCommented:
Didn't know about that array thing, but in general I use arrays as the last option due to the extensive (preventive) error handling needed.

Jim P.Commented:
That sounds like a good solution.

I generally never have done the population of spreadsheets. I've been lucky that I can just dump the datasheets.
Rey Obrero (Capricorn1)Commented:
i have used Arrays to transfer data from Access to Excel in an application that creates/draw dynamic OrgChart that includes names,position,telno,deptname etc..
it is pretty fast too.
Patrick MatthewsAuthor Commented:

Thanks so much for your comments.  I'm probably going to start the development work next week (need some
sample data from the client before I can get cracking), so I am going to leave this open for now.


Patrick MatthewsAuthor Commented:
Cheers to all, and poinks for everybody!  :)

Seriously, you all helped me organize my thoughts and for that I thank you.  I ended up using the approach

>>Open one large recordset, with all of the results for all branches.  Then, as I loop through the recordset,
>>populate 7 arrays (one for each weekday), and then use Excel's array transfer to populate each workbook.
>>Having one large recordset eliminates the overhead of constantly opening and closing recordsets, and
>>on the Excel side, array transfer is much, much faster than populating one cell at a time.

If anyone is truly fascinated by this (hah!), send me an email to the address in my profile and I will send you
the code.  I don't want to post the code here as it was paid work for a client.

Thanks again,

Patrick MatthewsAuthor Commented:
Actually, I can post this without revealing anything sensitive:

I had the following dynamic arrays:

    Dim SunArr() As Double
    Dim MonArr() As Double
    Dim TueArr() As Double
    Dim WedArr() As Double
    Dim ThuArr() As Double
    Dim FriArr() As Double
    Dim SatArr() As Double

Each time I encountered a new branch in my recordset, I ReDimmed those arrays:

            ReDim SunArr(6 To 20, 1 To 2) As Double
            ReDim MonArr(6 To 20, 1 To 2) As Double
            ReDim TueArr(6 To 20, 1 To 2) As Double
            ReDim WedArr(6 To 20, 1 To 2) As Double
            ReDim ThuArr(6 To 20, 1 To 2) As Double
            ReDim FriArr(6 To 20, 1 To 2) As Double
            ReDim SatArr(6 To 20, 1 To 2) As Double

(The 6 To 20 was a shorthand: each "row" in the array corresponded to an hour, starting at 06:00 and the last at 20:00.)  That initialized each array to zero at each element.

Each row from my recordset would have the results for one branch/day of the week/hour/[lobby or drive-up] volume.  As I looped through the recordset, I populated the relevant arrays:


            ' Volume is grouped hourly in the output.  Volume before 06:00 or after 20:00 are moved forward
            ' or back into those buckets
            If rs!Hr > 5 And rs!Hr < 21 Then
                UseBucket = rs!Hr
            ElseIf rs!Hr < 6 Then
                UseBucket = 6
                UseBucket = 20
            End If
            ' Source is T-V01 for Lobby and D-V01 for DU.  If branch is Back to Back, T-V01 already includes
            ' the drive-up volume, so we have to back out the D-V01 volume from the T-V01 volume.  If the
            ' DU Type is Other, then there is no double counting
            If rs!Source = "T-V01" Then
                TV01_Increment = rs!AvgVol
                DV01_Increment = 0
                DV01_Increment = rs!AvgVol
                If DU_Type = "Back to Back" Then
                    TV01_Increment = -DV01_Increment
                    TV01_Increment = 0
                End If
            End If
            ' Based on weekday, populate the correct array element
            Select Case rs!Wkday
                Case 1
                    SunArr(UseBucket, 1) = SunArr(UseBucket, 1) + TV01_Increment
                    SunArr(UseBucket, 2) = SunArr(UseBucket, 2) + DV01_Increment
                Case 2
                    MonArr(UseBucket, 1) = MonArr(UseBucket, 1) + TV01_Increment
                    MonArr(UseBucket, 2) = MonArr(UseBucket, 2) + DV01_Increment
                Case 3
                    TueArr(UseBucket, 1) = TueArr(UseBucket, 1) + TV01_Increment
                    TueArr(UseBucket, 2) = TueArr(UseBucket, 2) + DV01_Increment
                Case 4
                    WedArr(UseBucket, 1) = WedArr(UseBucket, 1) + TV01_Increment
                    WedArr(UseBucket, 2) = WedArr(UseBucket, 2) + DV01_Increment
                Case 5
                    ThuArr(UseBucket, 1) = ThuArr(UseBucket, 1) + TV01_Increment
                    ThuArr(UseBucket, 2) = ThuArr(UseBucket, 2) + DV01_Increment
                Case 6
                    FriArr(UseBucket, 1) = FriArr(UseBucket, 1) + TV01_Increment
                    FriArr(UseBucket, 2) = FriArr(UseBucket, 2) + DV01_Increment
                Case Else
                    SatArr(UseBucket, 1) = SatArr(UseBucket, 1) + TV01_Increment
                    SatArr(UseBucket, 2) = SatArr(UseBucket, 2) + DV01_Increment
            End Select

By the time that is done, I have my arrays populated with the info I need to dump into the report (see the sample file I posted in the original question).  Each array corresponds to a named range in the Excel template:

                ' populate branch workbook
                Set xlWbBr = xlApp.Workbooks.Add(TemplatePath)
                Set xlWsBr = xlWbBr.Worksheets(1)
                With xlWsBr
                    .Range("District") = DistName
                    .Range("Hogan") = Hogan
                    .Range("DateRange") = DateHeader
                    .Range("Branch") = BrName
                    ' array transfer for volume results
                    .Range("Sunday") = SunArr
                    .Range("Monday") = MonArr
                    .Range("Tuesday") = TueArr
                    .Range("Wednesday") = WedArr
                    .Range("Thursday") = ThuArr
                    .Range("Friday") = FriArr
                    .Range("Saturday") = SatArr
                    .name = "Br" & Hogan
                    ' if applicable, transfer data to district workbook
                    If DoDist Then
                        If xlWbDist Is Nothing Then
                            Set xlWbDist = xlApp.ActiveWorkbook
                            .Copy After:=xlWbDist.Worksheets(xlWbDist.Worksheets.Count)
                        End If
                    End If
                End With

And that's how I did it :)
Jim P.Commented:
That sounds like a great solution. Thanks for posting it.

You did all the work though.
Gustav BrockCIOCommented:
Thanks Patrick. Nice!

Hey Patrick,
Very nice of you to share this knowledge with us,
very much appreciated.


Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 6
  • 3
  • 3
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now