It runs without any Error message but the files are not loaded in MS access. what could be the reason? I do have a csv file in the directory

After edit

Option Compare Database
Option Explicit

Public Function import_date_files()

    Dim report_path As String, file_name As String
    
    
    
    
    report_path = "C:\Users\gobro7\OneDrive - Levi Strauss & Co\MS access test\weekly_load"
    
    file_name = Dir(report_path & "\*.csv")
    
    
    Do While file_name <> vbNullString
        DoCmd.TransferText acImportDelim, , Trim(Replace(file_name, "\.csv", "")), report_path & file_name, True
        file_name = Dir
    Loop
    
    MsgBox "Data loaded", vbInformation
    
         

End Function

>Solution :

You need to add a backslash between the path and the file name. You can either write

file_name = Dir(report_path & "\*.csv")
Do While file_name <> vbNullString
    DoCmd.TransferText acImportDelim, , Trim(Replace(file_name, ".csv", "")), report_path & "\" & file_name, True
    file_name = Dir
Loop

Or you add the backslash at the end of your path definition:

' Note the trailing backslash!
report_path = "C:\Users\gobro7\OneDrive - Levi Strauss & Co\MS access test\weekly_load\"

file_name = Dir(report_path & "*.csv")
Do While file_name <> vbNullString
    DoCmd.TransferText acImportDelim, , Trim(Replace(file_name, ".csv", "")), report_path & file_name, True
    file_name = Dir
Loop