Results 1 to 4 of 4

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

  1. #1

    Thread Starter
    Member
    Join Date
    Jul 2016
    Posts
    53

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

    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

  2. #2
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,610

    Re: Access 2013 Automate Queries Output To Separate CSV Files

    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.
    Please remember next time...elections matter!

  3. #3

    Thread Starter
    Member
    Join Date
    Jul 2016
    Posts
    53

    Re: Access 2013 Automate Queries Output To Separate CSV Files

    Thanks for your suggestion.

  4. #4

    Thread Starter
    Member
    Join Date
    Jul 2016
    Posts
    53

    Re: Access 2013 Automate Queries Output To Separate CSV Files

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

    Code:
    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

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width