Copy From Recordset with SQL Tables


Board Regular
Oct 8, 2008
Hi All~

I'm in the process of moving our Access based application to an Azure SQL Back End. Our application includes a reporting section that has a list of reports that a user can run. When a user selects a report, there is a query associated with that report -- I have VBA code that puts that query in a recordset, exports it to excel and does some minor formatting. With the Access Split Back End, everything was working fine -- but with a SQL Back End, I find I seem to get hung up on this particular bit of code:

oSheet.Range("A2").CopyFromRecordset rs

The code simply hangs as it tries to execute this. rs is the recordset I've previously defined. The particular query that this report is based on runs instantly, and there is no issue setting a recordset to this query. The results of this query are sizable, but again, it runs instantly -- and when I used an MS Access Back End I had no issues.

The only change I've made is to open the recordset as dbOpenDynaset and dbSeeChanges as the type and options -- this needs to be done if using a SQL Back End -- something I learned from this board.

Is there something I'm missing? Perhaps I need to re-conceive how I do this?

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.


MrExcel MVP
Mar 2, 2007
Office Version
  1. 2019
  1. Windows
You might like to try:
Set urs = CurrentDb.OpenRecordset(usql, dbOpenSnapshot)

a read-only recordset generally makes more sense for reporting.

As far as problems with the recordset you should probably start first with a non-sizable query to test the behavior. Something very simple like "select * from tblEmployees where EmployeeID = 1". You want to verify that the copyfromrecordset works at all. If it does, Then get back to your sizeable query and think about it some more., perhaps break it down. Or just for starters see how many records and columns you are working with in that result.

I don't know what you mean by "sizeable". WIth sql server sizeable means to me several million records. In that case results might not be capable of copying to a worksheet. The fact that you say this is a sizeable results and it also runs instantly is a little bit of a head-scratcher - it should be a least a few seconds to run if it really is a large result set.


Well-known Member
Jan 11, 2009
also know that MS Access sql is different from SQL Server sql

access has "select * from table where column_1 like 'CPGD*'
sql server is "select * from table where column_1 like 'CPGD%'


Board Regular
Oct 8, 2008
Thanks for your help! Opening the Recordset as Snapshot did the trick....and yes, 'sizeable' is obviously subjective....For this project, sizeable is about 50k records, which I realize is a blip in many other universes...

Watch MrExcel Video

Forum statistics

Latest member