[RESOLVED] Access 2013 Automate Queries Output To Separate CSV Files

[AngelSpeaks]

I am currently using Access 2013. My database has several tables relating to payroll data. Some of the tables are:

tblEmployees
tblJobs - Describes jobs that an employee is assigned to
tblPWBenefits - employee hours worked, wages and benefits info
tblHoursInput - date worked, job, hours worked
tblPWRates - rates for wages and benefits

I have a query that joins tblPWBenefits to tblEmployees (employee ID) , to tblJobs (job number) based on date worked. This is to be uploaded to a US state government portal. The query results are to be separated by job - each job has to have it's own file to upload

What I need: an automated way tor run a separate query for each job and create separate CSV files. I have a MSAccess form that is used to query based on from/to date worked. A command button executes the query.

I'm thinking I can create a query file with distinct job numbers and then loop thru this query file to execute the query and perform a DoCmd TransferText for each job using the job number as a file name. Can anyone help me with this or suggest a better option?

Thanks

[TysonLPrice]

You may be able to do that just from Excel using the external data source option. You would set up the query in each workbook. When you opened the spreadsheet up it would rerun the queries. I've done that a lot of times using SQL for a single workbook. I would think it would work for multiples.

[AngelSpeaks]

Thanks for your suggestion.

[AngelSpeaks]

OK, I figured it out. Here is my code in case anyone is interested.

Private Sub cmdSplit_Click()
    TempVars.RemoveAll
     
    Dim rsJobs As DAO.Recordset                'Output dataset of unique jobs query
    Dim Job As String
    Dim myPath As String                       'Note: have path from tblEmployers
    myPath = "C:\PrevailingWageTesting\"
    
    dteStart = Me.StartDate
    dteEnd = Me.EndDate
    TempVars.Add "StartDate", dteStart
    TempVars.Add "EndDate", dteEnd
    
    Dim strCriteria As String                  'Date criteria to extract unique jobs
    
    'Delete all records from table
    With DoCmd
     .SetWarnings False
     .RunSQL "DELETE * FROM tblCPUpload"
     .SetWarnings True
    End With
    
    msgText = "Cleared Certified Payroll from last upload "
    Response = MsgBox(msgText, vbOKOnly, msgApp)
    
    'Run append query to add Certified Payroll Records
    With DoCmd
     .SetWarnings False
     .OpenQuery "apdqryIllinoisPWExport"
     .SetWarnings True
    End With
         
    msgText = "Certified Payroll extracted "
    Response = MsgBox(msgText, vbOKOnly, msgApp)
 
    'Extract Unique Jobs
    strCriteria = "[Date] Between " & Format(dteStart, strcJetDate) & " And " & Format(dteEnd, strcJetDate)  ' Setup Date Criteria
    Set rsJobs = CurrentDb.OpenRecordset("SELECT DISTINCTROW Job FROM tblPWBenefits WHERE (" & strCriteria & ") GROUP BY Job ORDER BY Job;", dbOpenDynaset)
    msgText = "Extracting Unique Jobs"
    Response = MsgBox(msgText, vbOKOnly, msgApp)
    

    'Check if QueryDefs exists from last run, if yes, delete
    If QueryExists("myExportQueryDef") = True Then
        CurrentDb.QueryDefs.Delete "myExportQueryDef"
    End If


    Do While Not rsJobs.EOF
        Job = rsJobs!Job
        Dim rsExportSQL As String
        Dim sDate As String

        sDate = Format(dteStart, "mmddyyyy")
        
        rsExportSQL = "SELECT * FROM tblCPUpload " _
        & "WHERE (((ProjectNumber)='" & Job & "'))"

        Dim rsExport As DAO.QueryDef

        Set rsExport = CurrentDb.CreateQueryDef("myExportQueryDef", rsExportSQL)

        DoCmd.TransferText acExportDelim, , "myExportQueryDef", myPath & "Job#" & Job & " Start Date " & sDate & " - CP Upload.csv", True
        CurrentDb.QueryDefs.Delete rsExport.Name
        rsJobs.MoveNext
    Loop
     msgText = "Extract Completed, look at folder " & myPath & " for extracted CSV files"
     Response = MsgBox(msgText, vbOKOnly, msgApp)

End Sub