Exporting mutliple queries into csv files

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
Hi TJ,

Off the top of my head,

Dim qdfQ as DAO.QueryDef
Dim strFolder As String
Dim strFileSpec As String
Dim strDate As String

strFolder = "C:\Test\"
strDate = Format(Date(), "YYYYMMDD")

For Each qdfQ In DBEngine(0)(0).QueryDefs
'You need a test here to make sure you only export those
'queries that need to be exported. Perhaps something like this:
If qdfQ.Name Like "qryExport*" Then
strFileSpec = strFolder & qdfQ.Name _
& strDate & ".csv"
DoCmd.TransferText acExportDelim, "SpecName", _
qdfQ.Name, strFileSpec
End If
Next 'qdfQ

Unless all the queries will always have exactly the same fields, you'll
also need to work out some way of selecting the right export spec.
 
Back
Top