Copy From Recordset with SQL Tables

CPGDeveloper

Board Regular
Joined
Oct 8, 2008
Messages
172
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?
 

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.

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  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.
 

james_lankford

Well-known Member
Joined
Jan 11, 2009
Messages
1,207
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%'
 

CPGDeveloper

Board Regular
Joined
Oct 8, 2008
Messages
172
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...
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,111
Messages
5,835,450
Members
430,357
Latest member
Greshapa

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top