Office 2016 CopyFromRecordset takes 5 minutes instead of 2 seconds (1 Viewer)

[Rx_]

Pretty simple really. Some code opens up an Excel Template. Using a start location, it copyfromrecordset to populate the Excel document.
The data is a Linked SQL Server table.
Why is one view so much slower (100 times slower) than the others.

general process: Open a template, populate data, save as, close the workbook, then use a loop to do the same thing 9 times (nine linked tables).

There are 9 tables.

Eight of these take about 8.3 seconds each to create and save.
Except for the linked table with T&D in the name. It takes 6.4 Minutes!
Ok, it is about 3 times as many records a total of 1,500 records.
All of the linked views are the exact same format.
All the Linked Tables are actually just a Table Level View from the same source on SQL Server.

On Error GoTo err_trap
Set rs = DB.OpenRecordset(TableName)
    ObjXL.Workbooks(1).Worksheets(TabName).Select ' 
     ObjXL.Range(StartLocation).Select
        ObjXL.ScreenUpdating = False
        ObjXL.Calculation = xlCalculationManual ' turn Off Excel calculation
        ObjXL.EnableEvents = False
        ObjXL.ActiveSheet.DisplayPageBreaks = False
 
    ObjXL.Workbooks(1).Worksheets(TabName).Range(StartLocation).CopyFromRecordset rs
        
 ObjXL.ScreenUpdating = True
        ObjXL.Calculation = xlCalculationAutomatic 'To turn Off the automatic calculation
        ObjXL.EnableEvents = True
        ObjXL.ActiveSheet.DisplayPageBreaks = True
    ObjXL.Columns("A:" & LastDatacolumn).Select

 

[Rx_]

First workday after New Year's Day 2017, guess nobody wanted (or dared to) school the old man? Or... maybe it was too much celebration? LOL

Here is the answer NOT:
Set rs = DB.OpenRecordset(TableName, dbOpenDynaset, dbReadOnly, dbOptimistic) <---- DONT USE, returns an empty recordset
Was impressed with the speed, but discovered it was because the above returned an empty recordset!

HOWEVER
Set rs = DB.OpenRecordset(TableName, dbOpenDynaset)
ObjXL.Workbooks(1).Worksheets(TabName).Range(StartLocation).CopyFromRecordset rs, 3000, 20

Probably ran 20% faster. And again, the T&P Linked Table takes around 70% of the total time and it is only twice as big as the others.

 

[static]

Why would you want to use Access to copy data from SQL Server to Excel?

Excel has data connections.

 

[CJ_London]

have you tried dbOpenSnapshot instead of dbOpenDynaset?

 

[Rx_]

I will try the snapshot today. In a previous post (couple of years ago) the snapshot was found to be slower despite all we learned in the previous years.
However that was SQL Server 2008 and Office 2010.
Since this site has the latest and greatest (but a slow network), it is a great suggestion.

The reason to use MS Access to create 150 Excel Reports from SQL Server data is to provide a management tool that a mid-level programmer can maintain.
The MSACCESS interface provides several maintenance forms and schedulers.
The MS Access uses Remote Automation to open create a new instance of Excel (not visible), use a template, add data, complete formatting and save the Excel workbook As a specific name in a specific folder based on subscriber tables on SQL Server.

It is designed to be located on a server so that SQL Server can run Access via a Job on a schedule.

Yes, it could be done with Excel. MS Access is a better choice for a user interface with the maintenance forms, the subscriber interface, and the other options.
Eventually, there will be around 400 custom worksheets per workday automatically generated.
The worksheets are highly customized for Budget Forecasting with updates of spending actuals. They are programmed to include the formulas so that managers can conduct what if analysis.

 

[Rx_]

WOW!
Testing two reporting systems. One creates a workbook with 8 tabs (8 views) x 9 Organizations (later around 30 orgs). The DBSnapshot increased its time about 12%.
Given the network and shared SQL Server (virtual machine) it isn't too uncommon to see 15% variance through the day.

On the other report, it is a single view for one single worksheet per organization (9) that will later be around 30 orgs. That was running fairly fast as it has very little formatting. The one view was the hang up. With Openrecordset using the snapshot, the issue went away, gone! And, all the others ran in a fraction of the time too.

Will try this later in the day when the SQL Server is under more use.
The answer is that dbOpenSnapshot like eating Chicken Soup for a cold "couldn't hurt". LOL
Let's count this as a Must Try success!
Just a note on the CopyFromRecordset parameters. Those are still there too.

 

[CJ_London]

depending on what you are doing, the other one to try is dbOpenForwardOnly