Multiple queries exported into csv files.

[TJBernard]

I have a database with 300+ queries and I need to export them to seperate csv files. I could go through each single query and export it to a csv file, but this will take a long time, and this appears to be a new process that I will have to perform nearly every day.

So I am sure there is a way to do it through code. Basically I need to set a For Next Loop to loop through each query, then export the query as a csv file (name the csv file the same name as a query) and then move on to the next query until they have all been exported.

I have come up with the following code off the top of my head:

Dim rst as dao.recordset
Set rst = currentdb.openrecordset("queryName")
docmd.TransferText acexportdelim,"SpecName","TableName","C:\Test" & rst & Format(date,"YYYYMMDD") & ".csv"

Now I need a way to loop through each query in the database, and then name the csv file the same name as the query.

Does anyone have any ideas?

Thank you for your help,

T.J. Bernard

 

[sfreeman@co.mer]

Assuming that ALL queries in your db need to be exported, try something like this:

Sub QueryDefX()
Dim dbs As Database
Dim qdfLoop As QueryDef

Set dbs = CurrentDb

With dbs
' Enumerate QueryDefs collection.
For Each qdfLoop In .QueryDefs
Debug.Print " " & qdfLoop.Name 'insert the code you want to export data to csv here
Next qdfLoop
End With

End Sub