-
Oct 19th, 2021, 09:05 PM
#1
Thread Starter
Member
[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
-
Oct 20th, 2021, 07:05 AM
#2
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!
-
Oct 20th, 2021, 10:46 AM
#3
Thread Starter
Member
Re: Access 2013 Automate Queries Output To Separate CSV Files
Thanks for your suggestion.
-
Oct 27th, 2021, 12:38 PM
#4
Thread Starter
Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|