-
Jun 15th, 2018, 08:21 AM
#1
Thread Starter
Addicted Member
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?
-
Jun 15th, 2018, 09:57 AM
#2
Re: How to export query result to xls file with ADODB ?
Set the command text before execute ?
Feeling like a fly on the inside of a closed window (Thunk!)
If I post a lot, it is because I am bored at work! ;D Or stuck...
* Anything I post can be only my opinion. Advice etc is up to you to persue...
-
Jun 15th, 2018, 10:15 AM
#3
Thread Starter
Addicted Member
Re: How to export query result to xls file with ADODB ?
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
-
Jun 15th, 2018, 10:58 PM
#4
Re: How to export query result to xls file with ADODB ?
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 do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Jun 16th, 2018, 03:54 AM
#5
Thread Starter
Addicted Member
Re: How to export query result to xls file with ADODB ?
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.
-
Jun 16th, 2018, 09:25 AM
#6
Re: How to export query result to xls file with ADODB ?
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
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Jun 16th, 2018, 11:43 AM
#7
Thread Starter
Addicted Member
Re: How to export query result to xls file with ADODB ?
Did you use DAO.Database or ADODB.Connection?
-
Jun 16th, 2018, 05:53 PM
#8
Re: How to export query result to xls file with ADODB ?
ado
note: i do not have access installed, i only use access databases from vb(a)
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Jun 17th, 2018, 02:35 PM
#9
Thread Starter
Addicted Member
Re: How to export query result to xls file with ADODB ?
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.
-
Jun 17th, 2018, 04:13 PM
#10
Re: How to export query result to xls file with ADODB ?
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
Last edited by westconn1; Jun 17th, 2018 at 04:30 PM.
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Jun 18th, 2018, 04:11 AM
#11
Re: How to export query result to xls file with ADODB ?
also, could your table contain too many fields or records?
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Jun 18th, 2018, 05:33 AM
#12
Re: How to export query result to xls file with ADODB ?
Excel 8.0=Excel 97 --> 256 Columns, 65K Rows max!
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
-
Jun 18th, 2018, 09:14 AM
#13
Thread Starter
Addicted Member
Re: How to export query result to xls file with ADODB ?
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.
-
Jun 18th, 2018, 04:18 PM
#14
Re: How to export query result to xls file with ADODB ?
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
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Jun 20th, 2018, 02:01 AM
#15
Thread Starter
Addicted Member
Re: How to export query result to xls file with ADODB ?
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?
Last edited by kutlesh; Jun 20th, 2018 at 02:26 AM.
-
Jun 20th, 2018, 03:14 AM
#16
Re: How to export query result to xls file with ADODB ?
probably could refresh the application screen at each loop using a call to doevents
If VBA has some asynchronous capabilities,
i am not sure if a timer process would do this while the code is running
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|