Microsoft 365 Access VBA Error

Have you inserted a breakpoint in your code and stepped through it to confirm which line raises the error?

Yes, I used the step through and the error code is initiated when the following line of code runs:

Visual Basic for Applications

 DoCmd.TransferText acExportDelim, strSpecName, strQueryName, strFilePath1 & strFileName, True 

Have you tried manually deleting the blocking .csv file before running your process?

From the original post, "I have deleted the CSV file from the directory and closed and reopened the Access program."

Is there another step in your overall workflow where you open the exported .csv file after creating it?

My program has three distinct macros. The first one clears the import tables. The second macro loads the the tables with the new daily data. The third macro creates the CSV file and appends the current date in yyymmdd format to create a unique file.

In fact, the working version of this vba code has more columns in the csv file. The only difference is the second sql statement is bigger. See below:

Visual Basic for Applications

Public Function CreatePerlReport()
    On Error GoTo Error_Routine

'Declare variables
    Dim dtGetCurrentDate        As Date                 'Use the date function
    Dim intErrCount             As Integer              'Error handling switch
    Dim strReportDate           As String               'Date in YYYYMMDD format to be added to perl report name
    Dim strQuery                As String               'Variable for SQL query statement
    Dim strQueryName            As String               'Variable for query name
    Dim strFilePath1            As String               'Variable for the backup location that the CSV file will be stored in
    Dim strFilePath2            As String               'Variable for the primary location that the CSV file will be stored in
    Dim strFileName             As String               'Variable for the CSV file name
    Dim strSpecName             As String               'Varaiable for the export specification
    Dim LogMessage              As String               'Variable for error message displayed in logfile
    Dim dbs                     As DAO.Database         'Current database
    Dim rst                     As DAO.Recordset        'Current recordset
    Dim qdf                     As DAO.QueryDef         'Current query
    
' --- Configuration ---
    strReportDate = Format(Date, "yyyymmdd")
    intErrCount = 0
' --- End Configuration ---
     
'Select EPICUNK Report query parameters
    strQuery = "SELECT Format(Date(),'yyyymmdd') AS [SPLIT DATE], Original_Results.FILENAME, EPICUNK_Results.ST02, " _
                & "IIf(IsNull([Original_Results]![TIN]),[Original_Results]![NPI],[Original_Results]![TIN]) AS TIN, Original_Results.NPI, " _
                & "Original_Results.PAYERNAME, Original_Results.[PMT TYPE], Original_Results.[CHECK NUMBER], Original_Results.[CHECK DATE], " _
                & "Original_Results.[CHECK AMOUNT], [Original_Results]![CHECK AMOUNT]-[EPICUNK_Results]![CHECK AMOUNT] AS [VARIANCE/UNK POSTED TO EPIC], " _
                & "EPICUNK_Results.[CHECK AMOUNT] AS [UNK PROCEESED IN EPIC FH-IN REMIT WQ] " _
            & "FROM Original_Results INNER JOIN EPICUNK_Results ON Original_Results.[CHECK NUMBER] = EPICUNK_Results.[CHECK NUMBER];"

'Delete old EPICUNK Report query results if the query exists. Then insert the new query results.
    Set dbs = CurrentDb()
    For Each qdf In dbs.QueryDefs
        If qdf.Name = "EPICUNK_Report" Then
            dbs.QueryDefs.Delete "EPICUNK_Report"
            Exit For
        End If
    Next
    Set rst = dbs.OpenRecordset(strQuery, dbOpenSnapshot)
    With dbs
        Set qdf = .CreateQueryDef("EPICUNK_Report", strQuery)
    End With
'Select Perl Report query parameters
    strQuery = "SELECT DISTINCT Format(Date(),'yyyymmdd') AS [SPLIT DATE], Original_Results.FILENAME, Original_Results.ST02, " _
                & "IIf(IsNull([Original_Results]![TIN]),[Original_Results]![NPI],[Original_Results]![TIN]) AS TIN, " _
                & "Original_Results.NPI AS [TIN/NPI],Original_Results.PAYERNAME, Original_Results.[PMT TYPE], Original_Results.[CHECK NUMBER], " _
                & "Original_Results.[CHECK DATE], Original_Results.[CHECK AMOUNT], Null AS VARIANCE, EPICFH_Results.[CHECK AMOUNT] AS [EPIC FH], " _
                & "EPICCSG_Results.[CHECK AMOUNT] AS [EPIC CSG] , IDX_G34_Results.[CHECK AMOUNT] AS [IDX G34], IDX_G7_Results.[CHECK AMOUNT] AS [IDX G7], " _
                & "EPICFHASC_Results.[CHECK AMOUNT] AS [FH/HB ASC], [EPICUNK_Report].[UNK PROCEESED IN EPIC FH-IN REMIT WQ], " _
                & "Null AS [VARIANCE/UNK POSTED TO EPIC FH], Null AS [EPIC FH BATCH#], Null AS [VARIANCE/UNK POSTED TO EPIC CSG], Null AS [EPIC CSG BATCH#], " _
                & "Null AS [VARIANCE/UNK POSTED TO IDX G34], Null AS [IDX G34 BATCH#], Null AS [VARIANCE/UNK POSTED TO IDX G7], Null AS [IDX G7 BATCH#] " _
             & "FROM (((((Original_Results " _
                & "LEFT JOIN EPICFH_Results ON (Original_Results.ST02 = EPICFH_Results.ST02) " _
                    & "AND (Original_Results.[CHECK NUMBER] = EPICFH_Results.[CHECK NUMBER]) AND (Original_Results.[CHECK DATE] = EPICFH_Results.[CHECK DATE]) AND (Original_Results.PAYERNAME = EPICFH_Results.PAYERNAME)) " _
                & "LEFT JOIN EPICCSG_Results ON (Original_Results.ST02 = EPICCSG_Results.ST02) " _
                    & "AND (Original_Results.[CHECK NUMBER] = EPICCSG_Results.[CHECK NUMBER]) AND (Original_Results.[CHECK DATE] = EPICCSG_Results.[CHECK DATE]) AND (Original_Results.PAYERNAME = EPICCSG_Results.PAYERNAME)) " _
                & "LEFT JOIN IDX_G34_Results ON (Original_Results.ST02 = IDX_G34_Results.ST02) " _
                    & "AND (Original_Results.[CHECK NUMBER] = IDX_G34_Results.[CHECK NUMBER]) AND (Original_Results.[CHECK DATE] = IDX_G34_Results.[CHECK DATE]) AND (Original_Results.PAYERNAME = IDX_G34_Results.PAYERNAME)) " _
                & "LEFT JOIN IDX_G7_Results ON (Original_Results.ST02 = IDX_G7_Results.ST02) " _
                    & "AND (Original_Results.[CHECK NUMBER] = IDX_G7_Results.[CHECK NUMBER]) AND (Original_Results.[CHECK DATE] = IDX_G7_Results.[CHECK DATE]) AND (Original_Results.PAYERNAME = IDX_G7_Results.PAYERNAME)) " _
                & "LEFT JOIN EPICFHASC_Results ON (Original_Results.ST02 = EPICFHASC_Results.ST02) " _
                    & "AND (Original_Results.[CHECK NUMBER] = EPICFHASC_Results.[CHECK NUMBER]) AND (Original_Results.[CHECK DATE] = EPICFHASC_Results.[CHECK DATE]) AND (Original_Results.PAYERNAME = EPICFHASC_Results.PAYERNAME)) " _
                & "LEFT JOIN EPICUNK_Report ON (Original_Results.ST02 = [EPICUNK_Report].ST02) " _
                    & "AND (Original_Results.[CHECK NUMBER] = [EPICUNK_Report].[CHECK NUMBER]) AND (Original_Results.[CHECK DATE] = [EPICUNK_Report].[CHECK DATE]) AND (Original_Results.PAYERNAME = [EPICUNK_Report].PAYERNAME);"

'Delete old Perl Report query results if the query exists. Then insert the new query results.
    For Each qdf In dbs.QueryDefs
        If qdf.Name = "Perl_Split" Then
            dbs.QueryDefs.Delete "Perl_Split"
            Exit For
        End If
    Next
    Set rst = dbs.OpenRecordset(strQuery, dbOpenSnapshot)
    With dbs
        Set qdf = .CreateQueryDef("Perl_Split", strQuery)
    End With
    
'Perl Report CSV file and paths configuration
    strQueryName = "Perl_Split"
    strFileName = strQueryName & "_" & strReportDate & ".csv"
    strFilePath1 = "G:\Production\IDX\Perl_Split_835_Files\BALANCER_REPORT\"
    strFilePath2 = "L:\CPS_PB_BillingCollectionsReimbursement\03_Cashiering-Posting-Recon\3_Reconciliation\835 EDI Perl Split Reports\"
    strSpecName = "Perl Split Export Specs"
        
'Send semicolon delimited CSV file to the backup and primary location
    DoCmd.TransferText acExportDelim, strSpecName, strQueryName, strFilePath1 & strFileName, True
    DoCmd.TransferText acExportDelim, strSpecName, strQueryName, strFilePath2 & strFileName, True
    
'Send email to operations and CPS balancers
    MsgBox "Make sure Outlook is open in the rdp session before pressing OK."
    Call SendOutlookEmail(strFileName)
    
'Display end of module message
    MsgBox "The Perl Split Report csv file was created and email sent. Press OK to continue."
           
'Closing database variables
Exit_Routine:
    strQuery = ""
    rst.Close
    qdf.Close
    dbs.Close
    
'Exiting function and returning control to Access
    Set rst = Nothing
    Set qdf = Nothing
    Set dbs = Nothing
    
'Determine ending log statement
    If intErrCount = 0 Then
        LogMessage = "Create Perl Report macro completed successfully."
    Else
        LogMessage = "Create Perl Report macro completed."
    End If
    Call LogToFile(LogMessage)
    
Exit Function

' Save error message to the logfile and display it.
Error_Routine:
    intErrCount = 1
    LogMessage = "  Error in CreatePerlReport " & "Error Number:" & Err.Number & " Description:" & Err.Description
    Call LogToFile(LogMessage)
    MsgBox "CreatePerlReport", Err.Number & ": " & Err.Description, vbCritical
    Resume Exit_Routine
End Function

The second module is the one that is currently working. The first module in my question is the changes that are being requested to replace the second/current module.

I used the pass through and and was able to see all the variables used in the Local window. The error routine is employed when the DoCmd is initiated. Only the header record is created in the CSV file with the semicolon between each field when File Export Specification settings are used. When the File Export Specification settings are removed, the header and detail records are create, but the CSV formatting with the semicolon is lost. I can manually create the file by right-clicking the query name, choose Export, select the file path and file name, select the box to include headers, choose the radio button to include the semicolon and choose save.