Solved

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

Posted on 2008-10-30
18
786 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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 50

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
 
LVL 50

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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 50

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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

732 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