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,