of course the "trick" in this case was simple in hindsight:
you set the Source of the connectstring to the
(non existing) target file, which solved a lot of the syntax hassle.

This trick (i.e. opening a connection to a non-existent .xls) can also
be used when you need a Jet connection which doesn't involve Excel at
all e.g. to create a text file using SQL Server data:

SELECT fname, minit, lname
INTO [Text;FMT=Delimited;HDR=Yes;DATABASE=C:\TEMPO\;].[Export#txt]
FROM [ODBC;Driver={SQL
Server};SERVER=(local);DATABASE=pubs;UID=***;Pwd=* **;].employee;

i'm learning (and struggling).. but i wouldn't mind a kickstart.
or can you recommend some definitive guide/book/site
with the down and dirty on all these ado settings?
(i currently use www.able-consulting.com/ADO_Conn.htm as 'bible'

Do you mean the Jet syntax where the connection string is in the sql
text? I agree there are few details out there. I don't even know what
this syntax 'is' - ODBCDirect? I've gleaned details only through hours
of experimentation (plus a little Excel app that archives my sql
queries) but there are still holes in my knowledge e.g. is it possible
to specify the path to the .mdw used to secure an .mdb? There is no
Jet newsgroup and most in the MS Access ngs cannot tell Jet from MS
Access anyhow.

I also agree able-consulting is a good source for general ADO:
invaluable connection string collection and interesting faq.

have you published all that knowledge of yours somewhere?

I'm gradually publishing all my knowledge in the Usenet archive <g. I
guess I could scrape together a few html pages that could be a useful
reference source (for me if no one else) but I don't have the time to
maintain a domain. Others have kindly hosted some stuff I can link to
e.g.

http://www.dicks-blog.com/excel/2004...al_data_m.html

You gotta hand it to people like Dick Kusleika who can write daily on
an interesting and relevant topic. My blog about Excel+sql would dry
up after the first week <g.

Jamie.

--