How to export query result to xls file with ADODB ?

I have this code:

Code:

---


Dim WithEvents dbConn As ADODB.Connection
Dim sql as string
sql = "SELECT * INTO [Worksheet1] IN ''[Excel 8.0;Database=" & CurrentProject.Path & "\MyExcel.xls] FROM MyTable"

Set dbConn = New ADODB.Connection
dbConn.Open "Provider=SQLOLEDB;Data Source=myComputerName\SQLEXPRESS;Initial Catalog=MyDatabase;Integrated Security=SSPI;"
dbConn.Execute sql

---

I get error when I run this code:

Code:

---

ADODB conn error: -2147217908, Command text was not set for the command object., Microsoft OLE DB Provider for SQL Server, 1000440, C:\Program Files (x86)\Common Files\Microsoft Shared\VBA\VBA7.1\1033\VbLR6.chm

---

I am guessing the ADODB cannot run a query like that.

How can I export query result to xls file by using ADODB connection?
Is is possible? Or which is a proper way to do it?

Quote:

---

Command text was not set

---

Set the command text before execute ?

Yeah, in my code sql string was Nothing.
Now I set it but I get syntax error:

Code:

---

ADODB conn error: -2147217900, Incorrect syntax near the keyword 'IN'., Microsoft OLE DB Provider for SQL Server, 1000440, C:\Program Files (x86)\Common Files\Microsoft Shared\VBA\VBA7.1\1033\VbLR6.chm

---

debug.print sgl
will show the exact string you are passing to dbconn.execute, make sure it looks correct
do you still have the ' ' in your sql?

I though those two quotes are used to redirect the query result to xls file. Nop, it doesn't work without the quotes. It still prints the same error, even without quotes.

after many attempts i got it to work without error, but i am not at all sure the data is correct

but i can not tell you why yours does not work
on testing, i found that
you can insert into a new or existing workbook, but the worksheet (table) must not exist

this is the sql i used
sql = "SELECT * INTO [sheet7] in '' [Excel 8.0;Database=c:\temp\neww5w.xls] FROM [Sheet1$]"
as you can see i was just working from one excel database to another, i tested with both jet and ace, the results appeared correct with ace, whereas the results from jet i think i may have screwed up

you can test if the problem is in the source or target, by opening a recordset select * from mytable
if that works the problem is in the target

Did you use DAO.Database or ADODB.Connection?

ado
note: i do not have access installed, i only use access databases from vb(a)

Hmm, not sure. I want a data from my table in my MS SQL Express server to be exported as Excel file with ADODB. Or any other query result run on my SQL Express server.

I want to use ADODB since it has events, I can monitor what happens and when.

i doubt there is much difference with the database type of the query to be saved in excel, the best i could try is an access database query as i do no have sql express, but i do not believe that using another excel workbook as the database would make any difference
in addition to my previous post, if the target workbook is already open you will get an error
as i indicated above it took several attempts experimenting before i got it to work without error (work at all)

i also found why i was getting some strange data exported to the excel workbook, strictly my fault, the data was not coming from the source i thought, but some other workbook, hard to check which workbook the data is coming from with my poor coding

ado = adodb = activex data object

also, could your table contain too many fields or records?

Excel 8.0=Excel 97 --> 256 Columns, 65K Rows max!

I made a simple table with only two fields, the first is ID, primary key, the second is ordinary varchar(200).
The table has like 20 rows. Nothing heavy or special. Just wanted to learn how to export data from a query directly into excel worksheet, so that I can create many of those and automate the process.

By the way the production database is not MS SQL. It is IBM DB2 and it is still 32 bit server, meaning it doesn't have 64 bit driver for newer systems.

I will repeat again. DAO.Database does not have a mechanism to monitor when a query executed and VBA wrote the query result into xls file. That is why I want to use ADODB.

So far all works with DAO.Database, and I have like 50 queries running automatically and by choosing which to execute. I made a form with check-boxes and i choose which queries to run, but the problem is, when I run all 50 queries it takes like 20 minutes to finish which displays white screen or "Not responding" message, which of course looks confusing to the ordinary user.

Even I will get confused after a while of not using my Access 2000 application.

;)

I have other duties, so maybe next period I will try ADODB harder. In meanwhile thanks for the info.

Quote:

---

I made a form with check-boxes and i choose which queries to run, but the problem is, when I run all 50 queries it takes like 20 minutes to finish

---

i assume that you must be using some sort of a loop to run the queries, so you could update some status msg on each iteration, so at least you have something happening for the user

Code:

---

msg = "saving query " & queryname & " number " & num " " out of " & queriescount

---

i can make a test later, if you like, using an access database, i would not expect it to be different, but i can not try at all with db2

Yes, I would like it to be so simple, but the Access 2000 GUI interface does not get refreshed aka redrawn so displaying a message is useless while executing 50 queries in sequence.

Hmm. If VBA has some asynchronous capabilities, to start processing at given time, not related to DAO or ADODB?

probably could refresh the application screen at each loop using a call to doevents

Quote:

---

If VBA has some asynchronous capabilities,

---

i am not sure if a timer process would do this while the code is running