export sql results to CSV file via VBA in access(PLEASE HELP)

[stoppal]

I have a sql statement:

"SELECT Field1,field2 FROM table WHERE field1="[FORM]![PARAMETER]"

I need to export this to a CSV file with header information.

How can I do this?

I've tried the below function "save_data()", but I get a "expression
expected 1 error", I think b/c text transfer does not support
parameters during export. In addition, no header information apppears
even though the specifications are set to.

Think I need to pass the parameter in the function, use the SQL to make
a recordset that then gets made into a CSV file. But I need help. I
don't know where to start.

Thanks


**************************************************
Function savedata()
On Error GoTo Err_export

Dim AString As String
Dim query As String

'already made query

queryname = "myquery"

AString = "export_data_"

DoCmd.TransferText acExportDelim, "specification1", queryname,
"C:\Temp\" & AString & Format(Date, "YYYY_MMDD") & ".csv"

Exit_export:
Exit Function

Err_export:
MsgBox Err.Description
Resume Exit_export

End Function

[pietlinden]

Albert Kallal has code that does this. He's an MVP, so you can get his
web site address from www.mvps.org/access

or you could roll one yourself using Freefile and then print
statements... kinda tedious, though. but you could just loop through
the records of your query and then inside that loop through the fields
and output that to csv... tediuos, but not terribly hard.

HTH,
Pieter

[stoppal]

Can you please give me the exact link, I can not seem to find the link.

In addition, I'm not really sure how to use Freefile, do you have
example code?

Thanks!

[pietlinden]

[e-mail] wrote:

Can you please give me the exact link, I can not seem to find the link.
In addition, I'm not really sure how to use Freefile, do you have
example code?

Thanks!

Oh, now I feel stupid. Tried to go to his website, and it appears to
be down... (Has anybody seen Albert around anywhere? I haven't seen
his posts in a while...) but, I do know for one thing, that he has
code that does all this stuff for you. Pass a table or query or (?)
SQL statement, and it outputs to CSV for you... It's all packaged into
a class so you can import it and use it.

Now to actually *find* the thing!

[pietlinden]

Double dumb, I am. Is the message not "Too few parameters, expected
n"? If so, read this article.
http://www.mvps.org/access/queries/qry0013.htm

See if that doesn't solve your problem first... then if more drastic
measures are called for, we'll go from there. No sense in confusing
you further now if it's not necessary.

Pieter
(Hey, this village is *taken*!!!)

[jimfortune]

pi********@hotmail.com wrote:

Oh, now I feel stupid. Tried to go to his website, and it appears to
be down... (Has anybody seen Albert around anywhere? I haven't seen
his posts in a while...) but, I do know for one thing, that he has
code that does all this stuff for you. Pass a table or query or (?)
SQL statement, and it outputs to CSV for you... It's all packaged into a class so you can import it and use it.

Now to actually *find* the thing!

Check out
http://groups-beta.google.com/group/...d76ba87ff7fc0b

James A. Fortune

[Ronald W. Roberts]

st*****@hotmail.com wrote:

I have a sql statement:

"SELECT Field1,field2 FROM table WHERE field1="[FORM]![PARAMETER]"

I need to export this to a CSV file with header information.

How can I do this?

I've tried the below function "save_data()", but I get a "expression
expected 1 error", I think b/c text transfer does not support
parameters during export. In addition, no header information apppears
even though the specifications are set to.

Think I need to pass the parameter in the function, use the SQL to make
a recordset that then gets made into a CSV file. But I need help. I
don't know where to start.

Thanks


************************************************* *
Function savedata()
On Error GoTo Err_export

Dim AString As String
Dim query As String

'already made query

queryname = "myquery"

AString = "export_data_"

DoCmd.TransferText acExportDelim, "specification1", queryname,
"C:\Temp\" & AString & Format(Date, "YYYY_MMDD") & ".csv"

Exit_export:
Exit Function

Err_export:
MsgBox Err.Description
Resume Exit_export

End Function

This

"SELECT Field1,field2 FROM table WHERE field1="[FORM]![PARAMETER]"

should look like... if Field1 and parameter are numeric

"SELECT Field1, field2 FROM table WHERE field1=" & [FORM]![PARAMETER] &";"

or like this if text
"SELECT Field1, field2 FROM table WHERE field1='" & [FORM]![PARAMETER] & "';"
--
Ronald W. Roberts
Roberts Communication
rw*@robcom.com

[stoppal]

Thanks for the page, but I still can not find the item you are
referring to.

Any more help is apprecaited.

[stoppal]

I read this article, but I think the "&" is there when using it within,
VBA, is this correct?

I'm trying to run the SQL direct for the query itself. The issue is
when I call the query direct form within Access, the query works great.
But when I use VBA's docmd.transfertext,
I get the error, I read somewhere that the transfer text feature can
not handle parameters. So I thought maybe using VBA to loop though the
recordset and build the CSV file, line by line would work, only I don't
know how to code it, as I've not written to a text file with VBA.

Thanks

I apprecaite all the help