access/excel ado via vba

[Loane Sharp]

Hi there

Are the following pieces of code equivalent, especially in terms of
performance? I'm trying to summarize a huge Access database for further
analysis in Excel and need to improve efficiency of data transfer between
the two apps as far as possible ... I'd appreciate any ideas

(1)

strSQL = "SELECT * FROM Customers"
rsData.Open strSQL, cnSrc

(2)

strSQL = "SELECT * INTO [Excel 8.0;Database=C:\book1.xls].[Sheet1] FROM
Customers"
cnSrc.Execute strSQL

I can't judge this for myself (yet), since the second procedure isn't
returning any records ... Is this line of enquiry worth pursuing?

Best regards
Loane

 

[Jamie Collins]

Are the following pieces of code equivalent, especially in terms of
performance?
No.

I'm trying to summarize a huge Access database for further
analysis in Excel and need to improve efficiency of data transfer between
the two apps as far as possible ... I'd appreciate any ideas

(1)

strSQL = "SELECT * FROM Customers"
rsData.Open strSQL, cnSrc

(2)

strSQL = "SELECT * INTO [Excel 8.0;Database=C:\book1.xls].[Sheet1] FROM
Customers"
cnSrc.Execute strSQL

(1) creates a in-memory ADO recordset and (2) creates an Excel defined
Name (and, unless book1.xls is closed, a memory leak <g>). (1) is more
efficient then (2) but it's not a fair comparison. Amemd (1) to create
a populated defined Name (e.g. add a CopyFromRecordset line) and (2)
will prove the more efficient.

Jamie.

--