Go Back   Access World Forums > Microsoft Access Discussion > General

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 10-04-2016, 08:24 AM   #1
sancarn
Newly Registered User
 
Join Date: Oct 2016
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
sancarn is on a distinguished road
Question Run SQL and export query to CSV

Hi there,

I'm new to the forums but I am not new to VBA or SQL.
I'm a pretty advanced in VBA Excel and my SQL skills were honed in SQL Server 2003.

However I'm stuck in a conundrum which I cannot solve.

The problem:

The problem itself is embarrassingly simple. I want to:
  1. Use some SQL to select some records from a table.
  2. Export the produced query as a CSV
I assume that this is an obscenely simple task but... as they say, ignorance is bliss.

My Current code

Code:
Sub test()
    DoCmd.RunSQL "SELECT * FROM TippingBucket WHERE StationNum = 3441 INTO Q"
    DoCmd.TransferText acExportDelim, "Standard Output", "Q", "C:\Users\jwa\Desktop\TBD\export.csv"
End Sub
When I run the code, I simply get an error:

Code:
 
 Run-time error '3075':
 Syntax error (missing operator) in query expression 'StationNum = 3441 INTO Q'.
&Continue
&End
&Debug
&Help
Looking through some posts online I tried restructuring the query a bit:

Code:
Sub test()
    DoCmd.RunSQL "SELECT * INTO Q FROM TippingBucket WHERE StationNum = 3441"
    DoCmd.TransferText acExportDelim, "Standard Output", "Q", "C:\Users\jwa\Desktop\TBD\export.csv"
End Sub
I was surprised that this even had an effect. This time when running the Macro an input box pops up on the screen:

Code:
Enter Parameter Value
 DAL=on
StationNum
OK
Cancel
Now if I type "3441" into this input box Access selects all 2999468 records... And if I type "0" no records are selected???

Not really sure what's going on... I would be very appreciative if someone could help me out!

Thanks in advance!
~Sancarn

sancarn is offline   Reply With Quote
Old 10-04-2016, 02:29 PM   #2
sneuberg
AWF VIP
 
Join Date: Oct 2014
Location: Tucson, Arizona
Posts: 3,506
Thanks: 362
Thanked 978 Times in 945 Posts
sneuberg will become famous soon enough sneuberg will become famous soon enough
Re: Run SQL and export query to CSV

In the attached database the query

Code:
SELECT * INTO Q
FROM TippingBucket
WHERE StationNum = 3441;
Does what you would expect it to. I think we will need to see your table to figure this out. Could you upload the database or a copy of it with the TippingBucket table (if it is a table) with at least some typical records in it.

If TippingBucket is a query joined from different tables then StationNum might have to be qualified with the table name.
Attached Files
File Type: accdb TippingBucket.accdb (400.0 KB, 67 views)
__________________
And now nothing will be restrained from them, which they have imagined to do. Genesis 11:6

Steve
sneuberg is offline   Reply With Quote
Old 10-05-2016, 03:26 AM   #3
gemma-the-husky
Super Moderator
 
gemma-the-husky's Avatar
 
Join Date: Sep 2006
Location: UK
Posts: 13,843
Thanks: 56
Thanked 1,046 Times in 1,008 Posts
gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all
Re: Run SQL and export query to CSV

out of interest - what does "select * into Q" actually do?

__________________
Dave (Male!)
Gemma was my dog

if a poster helps you, please click the scales at the bottom left of this posting, or use the thanks button alongside.
gemma-the-husky is offline   Reply With Quote
Old 10-05-2016, 05:15 AM   #4
sneuberg
AWF VIP
 
Join Date: Oct 2014
Location: Tucson, Arizona
Posts: 3,506
Thanks: 362
Thanked 978 Times in 945 Posts
sneuberg will become famous soon enough sneuberg will become famous soon enough
Re: Run SQL and export query to CSV

Quote:
Originally Posted by gemma-the-husky View Post
out of interest - what does "select * into Q" actually do?
It creates a table named "Q" but I suspect what you are really asking is why is a table being created when you don't need to. I found that if I created a SELECT query with this SQL
Code:
SELECT TippingBucket.*
FROM TippingBucket
WHERE (((TippingBucket.StationNum)=3441));
with the name qryTippingBucket the follow code outputs the result of the query fine.
Code:
Sub test()
    DoCmd.TransferText acExportDelim, , "qryTippingBucket", "C:\Users\sneuberg\Desktop\export.csv"
End Sub
which means the OP propably doesn't need to create this table but this Microsoft WEB page just says "TableName" for the third argument in DoCmd.TransferText so I understand why the OP is doing it.

I didn't try this with a specification so I can't say whether it would still work with one.
__________________
And now nothing will be restrained from them, which they have imagined to do. Genesis 11:6

Steve
sneuberg is offline   Reply With Quote
Old 10-06-2016, 01:06 AM   #5
sancarn
Newly Registered User
 
Join Date: Oct 2016
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
sancarn is on a distinguished road
Re: Run SQL and export query to CSV

Quote:
I found that if I created a SELECT query with this SQL ... with the name qryTippingBucket the follow code outputs the result of the query fine ...
How do you give the SQL the query name 'qryTippingBucket'. In previous versions of SQL I have used 'into <queryName>' doesn't create a new table but creates a query named <queryName>.

Quote:
Does what you would expect it to. I think we will need to see your table to figure this out. Could you upload the database or a copy of it with the TippingBucket table (if it is a table) with at least some typical records in it.
Sadly for me, that is not something I can do! It's good to hear that my syntax was alright though!

It doesn't matter though, I'm going to open the AccessDB in MapInfo instead. Which will be way easier for me to handle! I will keep an eye on these boards though as I'm sure getting used to Access will come in handy at some stage!
sancarn is offline   Reply With Quote
Old 10-06-2016, 07:05 AM   #6
sneuberg
AWF VIP
 
Join Date: Oct 2014
Location: Tucson, Arizona
Posts: 3,506
Thanks: 362
Thanked 978 Times in 945 Posts
sneuberg will become famous soon enough sneuberg will become famous soon enough
Re: Run SQL and export query to CSV

Quote:
Originally Posted by sancarn View Post
How do you give the SQL the query name 'qryTippingBucket'. In previous versions of SQL I have used 'into <queryName>' doesn't create a new table but creates a query named <queryName>.
I created 'qryTippingBucket with the query designer. You can also create a querydef (query with a name) in code.


In Access SELECT INTO creates tables.


Quote:
It doesn't matter though, I'm going to open the AccessDB in MapInfo instead. Which will be way easier for me to handle! I will keep an eye on these boards though as I'm sure getting used to Access will come in handy at some stage!
Good luck with your project.

__________________
And now nothing will be restrained from them, which they have imagined to do. Genesis 11:6

Steve
sneuberg is offline   Reply With Quote
Reply

Tags
csv , export , export access to csv , sql , vba

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] Run Query B with varible input from query A, export to excel > repeat for all query A FrozenMana Modules & VBA 15 08-13-2015 07:56 AM
Microsoft Access (2010) was unable to export data using export query for xml hoychep Modules & VBA 4 02-28-2015 04:41 PM
[SOLVED] VBA Code to run a query and then export the query as a fixed width .txt file keevo Modules & VBA 2 10-15-2014 01:25 AM
Export Query to Excel then Delete Query data from Table cochise Queries 5 11-20-2013 08:45 AM
Export query to CSV & export specification shenty Modules & VBA 14 03-12-2009 09:20 AM




All times are GMT -8. The time now is 10:30 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World