Re: Excel VBA - MS Query

Appreciate the feedback!
Good Luck and Chrs,
Perry

"JCH" <JCH@xxxxxxxxxxxxxxxxxxxxxxxxx> schreef in bericht
news:1BB74A8E-DF32-493F-A911-6B58B950FC70@xxxxxxxxxxxxxxxx
Thank you for putting me on the right track. I am more of a statistician
than
a programmer. I just muddle through VBA and SQL on a "must do" basis.

I appreciate the discussion group very much because I know I can come here
for direction when my muddleing gets muddled. Back to the book and online
help now.
--
Cordially,
JCH


"Perry" wrote:

Why not use ADO to transmit data from Access to Excel.
With ADO you can use either the ODBC driver type connection from Access
to
Excel
or use OLE DB provider under ADO to do more or less the same.

Here's a link to buildup ADO both connection strings
http://www.connectionstrings.com/?carrier=excel

A couple of advantages:
- your mechanism doesn't need to open MS Query for transmittance
- other than Automation, you don't need an Excel instance to transmit
data
- You can use your native Access SQL to build the datasets

Krgrds,
Perry

"JCH" <JCH@xxxxxxxxxxxxxxxxxxxxxxxxx> schreef in bericht
news:9E99A042-A605-4CCD-A4AA-C26DE47E7D9D@xxxxxxxxxxxxxxxx
Excel 2000, Access 2000, MS Query 6.3

I bring data from Access into an Excel work*** for statisitcal
analysis
using MS Query. Using Excel VBA, I create a new SQL statement that
depends
on
the results of the analysis. Then I copy/paste the new SQL into MS
Query
SQL
view and repeat the process. All this works fine manually. This repeats
126
times and needs to be run each week. Eventually, I get down to the
desired
recordset. To be useful, the procedure will have to be automated
because
it
is too much work by hand.

I don't know how to open MS Query in SQL view using Excel VBA to
perform
the
copy/paste.

I am sure I am missing something very simple. My reference book ("Excel
2000
Power Programming with VBA" by John Walkenbach) doesn't seem to address
this
particular procedure.

Can someone here put me on the right track?
--
Cordially,
JCH





.