Apologies: access/excel ado via vba - forgot a line
 

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
Workbooks("C:\book1.xls").Worksheets("Sheet1").Ran ge("A1").CopyFromRecordSet

(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

Apologies: access/excel ado via vba - forgot a line
 

I don't think the line of inquiry is worth pursuing.
Access already have a function for doing that. I've used
it many times.
on the Access menu - ToolsOffice Links analize it with
excel. you can dump queries results and tables into excel.
I have had to download large amounts of data and this
seems fast enough for me. Unless you just want to write
the code, it is not wise to re-invent the wheel when your
appication already has that wheel and in different colors.

-----Original Message-----
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
Workbooks("C:\book1.xls").Worksheets("Sheet1").Ra nge
("A1").CopyFromRecordSet

(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



.

Apologies: access/excel ado via vba - forgot a line
 

Hi Frank -
Thanks for this.
I'll let you know what transpires.
Best regards
Loane
"Frank Stone" wrote in message
...
I don't think the line of inquiry is worth pursuing.
Access already have a function for doing that. I've used
it many times.
on the Access menu - ToolsOffice Links analize it with
excel. you can dump queries results and tables into excel.
I have had to download large amounts of data and this
seems fast enough for me. Unless you just want to write
the code, it is not wise to re-invent the wheel when your
appication already has that wheel and in different colors.

-----Original Message-----
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
Workbooks("C:\book1.xls").Worksheets("Sheet1").Ra nge
("A1").CopyFromRecordSet

(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



.

Apologies: access/excel ado via vba - forgot a line
 

"Frank Stone" wrote ...

SELECT * INTO [Excel 8.0;Database=C:\book1.xls].
[Sheet1] FROM

I don't think the line of inquiry is worth pursuing.
Access already have a function for doing that.

I disagree. A SELECT..INTO query is the most efficient way I know to
export from a Jet .mdb to Excel.

I assume you are alluding to MS Access's TransferSpreadsheet and
similar functionality. These effectively execute sql for you under the
covers e.g. in some circumstances it does a DROP TABLE before an
SELECT..INTO. Maybe it's just me but I prefer to be in control of
executing queries, especially when it is dropping tables!

Also, the MS Access functionality has limitations that can be
circumvented using queries e.g. tale a look at this thread:

http://groups.google.com/groups?thre...ing.google.com

Anyhow, the OP may prefer to control the whole process from Excel. I
assume you are not suggesting they use Excel VBA to automate the MS
Access app in order to use its TransferSpreadsheet (or similar)
functionality, which merely does a SELECT..INTO but under the covers?

Jamie.

--