Copy From Recordset with SQL Tables

[CPGDeveloper]

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?

 

[xenou]

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]

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]

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...