473,561 Members | 3,153 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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


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_dat a_"

DoCmd.TransferT ext 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

Nov 13 '05 #1
8 19729
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

Nov 13 '05 #2
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!

Nov 13 '05 #3

stop...@hotmail .com 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!

Nov 13 '05 #4
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*!!!)

Nov 13 '05 #5
pi********@hotm ail.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

Nov 13 '05 #6
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_dat a_"

DoCmd.Transfer Text 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

Nov 13 '05 #7
Thanks for the page, but I still can not find the item you are
referring to.

Any more help is apprecaited.

Nov 13 '05 #8
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.transfert ext,
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

Nov 13 '05 #9

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
1727
by: Kevin P | last post by:
Hi News Group, In an application written in C# and VS.Net using Dotnet Framework 1.1.4322 on XP and Win 2000 machines, we randomly get the error "Failed to load resources from resource file. Please check your setup." Although it is a random occurrance, it happens at the exact same location in the progran every time. At the time of this...
3
7777
by: Preference | last post by:
Hello, I am developing a .NET application in managed C++. Until now we have executed without problems in a lot of different computers. But a few days ago we detect a problem in some laptops. In all cases the system was XP SP2 and ..NET Framework 1.1. The problem appears initializing the application. A message box with the text 'Failed to...
1
1353
by: donis | last post by:
Hi i am doing a project for making a hotel management system in Microsoft Access I wantto make a hotel with 20 rooms 5 = suite 5= double 5= duobleo view 5 - rooms at single 4 types of room with 4 different prices
2
1500
by: Neo Geshel | last post by:
I have the two subs: Sub UniqueHits() Dim StrSQLQuery As String Dim ObjAdapter as New OleDbDataAdapter() Dim ObjDataSet as DataSet StrSQLQuery = "SELECT * FROM Browser WHERE IsUnique = True" ObjAdapter.SelectCommand = new OleDbCommand(StrSQLQuery, ObjConnection) ObjDataSet = new DataSet()
0
1045
by: Nicolas | last post by:
Hello I can already export my datagrid (not dataset because my datagrid has rowfilters) to excel and word. But I can't seem to manage it to write it to plain/text . Anyone has advise ? Contentype="text/plain" doesn't seems to work. thx in advance
3
1561
by: Martin Ho | last post by:
Can someone help me with this please? I wasn't very clear in my old post. I have a program to copy files from one location to another, now I want to copy only those files which were created on certain date. How do I compare the file (date created) to system date? Something like this doesn't work: if System.IO.File.GetCreationTime(path to...
5
2622
by: Vijaya P Krishna | last post by:
Hi, I have a .NET Windows Forms application, written in VB.NET and C#. I am opening a URL from the application using Process.Start(). The URL points to a java servlet running on apache-tomcat. For some users the application shows an error dialog with following information. Another very interesting observation is that it's sending a lot...
0
1453
by: JayDawg | last post by:
If anyone is willing to provide some consulting pro-bono, or for a very small charge I would really love the asistance. If anyone can fix my problem here online that would be even better. Here is my problem. I spent a great deal of time developing a datbase for my boss creating the tables and the relationships, and queries (to include...
3
2217
by: shapper | last post by:
Hello, In have a class inside a compiled DLL where I have the following code: ' Create site map XML document Dim siteMap As XmlDocument = New XmlDocument ' Load site map XML document from ASP.NET web site map siteMap.Load(HttpContext.Current.Server.MapPath(Me.SiteMapUrl)) Me.SiteMapUrl is a property.
0
7558
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
7851
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8072
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7605
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
6195
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
0
5177
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3591
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2055
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1171
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.