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

L

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
Workbooks("C:\book1.xls").Worksheets("Sheet1").Range("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
 
F

Frank Stone

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 - Tools>Office 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.
 
L

Loane Sharp

Hi Frank -
Thanks for this.
I'll let you know what transpires.
Best regards
Loane
Frank Stone said:
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 - Tools>Office 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").Range ("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



.
Click to expand...
Click to expand...
 
J

Jamie Collins

...
SELECT * INTO [Excel 8.0;Database=C:\book1.xls].
[Sheet1] FROM
Click to expand...
Click to expand...
I don't think the line of inquiry is worth pursuing.
Access already have a function for doing that.
Click to expand...

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/[email protected]

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.

--
 
Top