Solved

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

Posted on 2008-10-30
18
781 Views
Last Modified: 2011-10-19
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
Teller-Trans-Report-Example.pdf
0
Comment
Question by:Patrick Matthews
  • 6
  • 3
  • 3
  • +3
18 Comments
 
LVL 59

Accepted Solution

by:
Saurabh Singh Teotia earned 100 total points
ID: 22841000
Patrick...

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

    

Sheets("Import").Select

Range("AST").Select

Selection.ClearContents
 
 

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**********

Sheets("Import").Select

Range("AST").Select

Selection.CopyFromRecordset MyTemp

MyAccess.QueryDefs.Delete ("Temp1")

Open in new window

0
 
LVL 119

Assisted Solution

by:Rey Obrero
Rey Obrero earned 100 total points
ID: 22841048
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....
0
 
LVL 27

Assisted Solution

by:jjafferr
jjafferr earned 100 total points
ID: 22841157
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).

jaffer
0
 
LVL 38

Assisted Solution

by:Jim P.
Jim P. earned 100 total points
ID: 22841210
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.
0
 
LVL 92

Author Comment

by:Patrick Matthews
ID: 22841470
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
0
 
LVL 92

Author Comment

by:Patrick Matthews
ID: 22841548
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

Patrick
0
 
LVL 49

Assisted Solution

by:Gustav Brock
Gustav Brock earned 100 total points
ID: 22842847
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.

/gustav
0
 
LVL 92

Author Comment

by:Patrick Matthews
ID: 22850455
All,

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.

Thoughts?

Patrick
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 22850634
> 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.

jaffer
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 49

Expert Comment

by:Gustav Brock
ID: 22850709
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.

/gustav
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 22850875
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.
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 22851324
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.
0
 
LVL 92

Author Comment

by:Patrick Matthews
ID: 22851708
All,

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.

Cheers,

Patrick
0
 
LVL 92

Author Comment

by:Patrick Matthews
ID: 22986900
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
below:

>>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
0
 
LVL 92

Author Comment

by:Patrick Matthews
ID: 22994962
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
            Else
                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
            Else
                DV01_Increment = rs!AvgVol
                If DU_Type = "Back to Back" Then
                    TV01_Increment = -DV01_Increment
                Else
                    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
                            .Copy
                            Set xlWbDist = xlApp.ActiveWorkbook
                        Else
                            .Copy After:=xlWbDist.Worksheets(xlWbDist.Worksheets.Count)
                        End If
                    End If
                End With



And that's how I did it :)
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 22995120
That sounds like a great solution. Thanks for posting it.

You did all the work though.
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 22995424
Thanks Patrick. Nice!

/gustav
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 23001900
Hey Patrick,
Very nice of you to share this knowledge with us,
very much appreciated.

jaffer
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now