Question Run SQL and export query to CSV

[sancarn]

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

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:

 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:

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:

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

 

[sneuberg]

In the attached database the query

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.

 

[gemma-the-husky]

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

 

[sneuberg]

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

SELECT TippingBucket.*
FROM TippingBucket
WHERE (((TippingBucket.StationNum)=3441));

with the name qryTippingBucket the follow code outputs the result of the query fine.

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.

 

[sancarn]

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>.

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!

 

[sneuberg]

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.

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.