Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
access/excel ado via vba
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
access/excel ado via vba
"Loane Sharp" wrote ...
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. -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Query from Access into Excel cause Access to go to read only | Excel Discussion (Misc queries) | |||
Can Excel access data from Access?! | Excel Discussion (Misc queries) | |||
export access to excel. change access & update excel at same time | Excel Discussion (Misc queries) | |||
access--excel--access | Excel Programming | |||
How to access ACCESS from Excel! | Excel Programming |