Create CSV File from Query

[DavidWE]

I am attempting to create a CSV file from a stored query. I have already created a record set from the query to update a table and create an Excel file.

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim qdf As DAO.QueryDef
Set db = CurrentDb()
Set qdf = db.QueryDefs("qryTotals")
qdf.Parameters("[Forms]![Scan].[txtScanDate]") = txtScanDate.Value
qdf.Parameters("[Forms]![Scan].[txtScanNo]") = txtScanNo.Value
Set rs = qdf.OpenRecordset
rs.MoveFirst

I have tried the TransferText method. I can probably start from scratch with another routine to create the CSV file, but I thought I might be able to use the variable rs since it contains the results of the query. Is there a way?

Thanks

 

[JamesMcS]

Could you use the recordset to make a table, then use transfertext that way?

 

[DavidWE]

Thanks James,
That would probably work. I am already updating a table and could create a new one each time. I might do that if I don't find another way.

 

[JamesMcS]

Yeah, bit clunky but you could easily just delete the table after the transfertext is done, keep it neat on the face of it

 

[DavidWE]

Bump

I thought I would try again, only I won't try to create the csv file from a record set.

Here is my stored query:

PARAMETERS [Forms]![Scan].[txtScanDate] DateTime, [Forms]![Scan].[txtScanNo] Long;
SELECT [BoxScans].[Barcode], [BoxScans].[ScanDate], [BoxScans].[ScanNo], Sum([BoxScans].[Quantity]) AS Total
FROM BoxScans
WHERE ([BoxScans].[ScanDate]=[Forms]![Scan].[txtScanDate] And [BoxScans].[ScanNo]=[Forms]![Scan].[txtScanNo])
GROUP BY [BoxScans].[ScanDate], [BoxScans].[ScanNo], [BoxScans].[Barcode];

Here is the statement that works in my code to save as an Excel file:

XlBook.SaveAs "C:\Tam Scanner\Tam Scans\Tam Scans" & Format(Now(), "mm_dd_yyyy hh mm AMPM") & ".xlsx"

I have tried changing the extension to .csv, but if I open it in Notepad it is just gibberish.

I have tried the transfertext method, but I don't know how to pass the parameters to the stored query with that method. Both the txtScanDate and txtScanNo parameters are on the open form and will have data before the module is performed when the user clicks a command button. I don't mind placing the query in the code if that will work. I just have to know how to give it the parameters.

I only included the code that I thought was relevant. Please let me know if I need to include more.

Does anyone have any other suggestions?

Thanks