export to any db code does not work

[Paylo]

This is the code I've been trying to use from smith voice and VBworld...

Dim db as database
Set db = Workspaces(0).OpenDatabase(app.path & "iblio.mdb")
'commented out syntax followed by working example
'db.execute "SELECT tbl.fields INTO [dbms type;DATABASE=path].[unqualified 'filename] FROM [table or tables]
db.execute "SELECT * INTO [dBase III;DATABASE="C:My Documents].[testa] FROM [authors]"

but it doesn't seem to work with VB6 ADO (Sp4)!!!!
I'm trying to take stuff from my SQL server and exporting it to an excel file.
The statement I use is

export_command.commandtext = select * into [excel 8.0;database=c:\vbcode].[test] from [mytable]
export_command.execute

it keeps on giving me an error about there not existing a database of this name within the SQL server. I don't think it understands I want to get the data out of the SQL server and not put in. Anyone can help me with this one????

[JHausmann]

You're trying to use a Jet extension of SQL on SQL Server (select into in SQL server, at least in 6.5, will only select into a SQL server table). Your best bet is to create an access database that has a linked table and run your SQL against that.

[Paylo]

Yes that's what I thought too, some variations of the code I posted will create the table, but only inside the SQL server DB, but you know why smithvoice's code works?

So I should create a linked table and use a built in convert to spread sheet function to change the MDB to excel?

[JHausmann]

That's what I'd do (assuming you're talking about the TransferSpreadSheet function).