0

I am running Office 365 on a Windows 11 machine.

I created an Access database that loads tables from csv files, runs two queries and creates a CSV formatted report file through the use of modules, macros and import and export file specification. In its current format it works correctly.

A change to the csv report file has been requested. The change consists of removing unnecessary columns from the CSV file. I have tried several iterations of database changes to accomplish the request, but run into an MS Access error. The latest change was only to the query that creates the report by removing the unnecessary columns and modifying the export specs. I have deleted the CSV file from the directory and closed and reopened the Access program. If I right click the query and select export, I can create the csv file. However, I only get the the header record written to the csv file and the following error if I run the module:

CreatePerlReport 3051: The Microsoft access database engine cannot open or write to the file 'Perl_Split_20251031.csv'. It is already opened exclusively by another user, or you need permission to view and write its data.

This code is in a test environment so there will be extra comment lines.

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 LEFT 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 AMOUNT], Null AS VARIANT, Original_Results.[CHECK AMOUNT] AS [EPIC FH], " _
                & "EPICUNK_Report.[UNK PROCEESED IN EPIC FH-IN REMIT WQ], Null AS [VARIANCE/UNK POSTED TO EPIC FH], Null AS [EPIC FH BATCH NUM] " _
             & "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])) " _
                & "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]);"

'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 = "Z:\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
Improve this question
10
  • 1
    Do you get the same error with a different file name? Commented Nov 3 at 20:56
  • Most likely a permission issue. Try writing to a local folder like C:\Users\Public. Commented Nov 4 at 7:44
  • Tim, the current/active version of this database uses the same file name without any issues, I have copy of that database in the same testing environment and runs the module successfully. The new version of the module that is being requested just removes some columns from the CSV file that will no longer be needed. The naming convention for the file is being requested by the department that uses the file to produce cashiering reports. Commented Nov 4 at 13:23
  • Gustav, the current/active version of this database uses the same file name and writes to the same location without any issues. It is only when the requested columns are removed from the query to create the new CSV file that the error occurs. What I have discovered, is that I have to close the database program to actually view the file that was created. The file only contains the header record. No detail records exist. Commented Nov 4 at 13:37
  • 1
    Does the "Perl Split Export Specs" still work with the new query having columns removed? Commented Nov 4 at 13:58

1 Answer 1

Reset to default
1

The issue turned out to be a failure of the Export File Specification setting. The setting for a semicolon delimited CSV output file would not work when the SQL query statement was changed. The module kept failing even after the specification setting were change to match the new query results. I learned of a way to include the new setting through the use of a schema.ini file with the following three lines in the file:

[New_Perl_Split_20251106.csv] 
Format=Delimited(;)  
ColNameHeader=True

By added VBA code to create the schema.ini file on the fly with a subroutine, the Export File Specification settings were no longer necessary. The schema.ini file must be saved in the same directory that the CSV file is written to. The working code now looks like this:

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 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], " _
                & "[EPICUNK_Report].[UNK PROCEESED IN EPIC FH-IN REMIT WQ], Null AS [VARIANCE/UNK POSTED TO EPIC FH], Null AS [EPIC FH BATCH NUM] " _
             & "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 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 = "Z:\Production\IDX\Perl_Split_835_Files\BALANCER_REPORT\"
    'strFilePath2 = "L:\CPS_PB_BillingCollectionsReimbursement\03_Cashiering-Posting-Recon\3_Reconciliation\835 EDI Perl Split Reports\"
        
'Send semicolon delimited CSV file to the backup location
    Call CreateSchemaIniFile(strFilePath1, strFileName)
    DoEvents
    DoCmd.TransferText acExportDelim, , strQueryName, strFilePath1 & strFileName, True
    
'Send semicolon delimited CSV file to the primary location
    'Call CreateSchemaIniFile(strFilePath2, strFileName)
    'DoEvents
    'DoCmd.TransferText acExportDelim, , 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

'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

Public Sub CreateSchemaIniFile(Folderpath As String, FileName As String)
    Dim fso As Object
    Dim ts As Object
    
    ' Create FileSystemObject
    Set fso = CreateObject("Scripting.FileSystemObject")
    
    ' Open schema.ini for writing (overwrite if exists)
    Set ts = fso.CreateTextFile(Folderpath & "schema.ini", True)
    
    ' Write schema.ini contents
    ts.WriteLine "[" & FileName & "]"
    ts.WriteLine "Format=Delimited(;)"          ' semicolon delimiter
    ts.WriteLine "ColNameHeader=True"           ' First row has headers
    
    ts.Close
    
    Set ts = Nothing
    Set fso = Nothing
    
    MsgBox "schema.ini created in " & Folderpath, vbInformation
    
End Function
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.