saving to a csv file

[Guest]

Hi. I'm using Access 2000 and I'm having trouble trying to create some vba
code that will allow me to save the data in a query to a csv file (comma
separated text). I used the acCmdImport command to import the data file
originally. However, I don't know how to export it (preferably with a gui
to allow the user to enter a filename to save to). Please provide sample
code.

 

[Alex White MCDBA MCSE]

So you have a listbox or something with a resultset that you want to export
to a text file

dim adoTest as new adodb.recordset
with adoTest
.open me.lstResults.rowsource, currentproject.connection, adOpenKeyset,
adLockReadOnly
if .recordcount > 0 then
Open "c:\MyExported.txt" For Output As #1
do while not .eof
write #1, .fields("myFirstField").value,
..fields("mySecondField").value
.movenext
loop
close #1
end if
end with

one comma seperated file......

 

[Guest]

Thanks. But what I really wanted was to have a listbox that contains a list
of queries that a user selects, and then presses a button and then he is
walked through selecting a filename to save to etc. Is there an easier way?
Is there a way to use Runcommand, like with the import wizard, or something
similar?

 

[Alex White MCDBA MCSE]

Create all the queries with a specific prefix e.g. queryMyUsers1,
queryMyUsers2

use the following as the rowsource for the listbox

SELECT Right(Name,Len(name)-5) AS Expr1
FROM MSysObjects
WHERE (((MSysObjects.Name) Like "query*"))
ORDER BY MSysObjects.Name;

if you called the listbox lstQueries

Private Sub lstQueries_DblClick(Cancel As Integer)
dim adoTest as new adodb.recordset
with adoTest
.open "query" & me.lstQueries.column(0),
currentproject.connection,adOpenKeyset,adLockReadOnly
if .recordcount > 0 then
Open "c:\MyExported.txt" For Output As #1
do while not .eof
write #1,
..fields("myFirstField").value,.fields("mySecondField").value
.movenext
loop
close #1
end if
end with

this code could be modified to allow for multi-selection, I never use the
import wizard as it does not give me the flexability I need most of the
time.

open a file,

http://support.microsoft.com/default.aspx?scid=kb;en-us;303066

save file dialog

http://vbnet.mvps.org/index.html?code/comdlg/filesavedlg.htm

give it ago, hope it is closer to what you need!

 

[Guest]

If I wanted to invoke the export wizard on a query, how would I do that?