VBA File Operations: Read, Write, and Manage Files from Access

Access VBA can do far more than manipulate database records. It can read and write text files, check whether files exist, copy and move files, create folders, and automate complex file management workflows. These capabilities are essential for integrating Access with other systems and automating data exchange.

Checking If a File Exists

The simplest file check uses the Dir() function:

Function FileExists(filePath As String) As Boolean
    FileExists = (Dir(filePath) <> "")
End Function

' Usage:
If FileExists("C:\Reports\monthly.pdf") Then
    MsgBox "File found!"
Else
    MsgBox "File not found."
End If

Dir() returns the filename if it exists, or an empty string if it does not.

Reading a Text File

Use the Open statement to read a text file line by line:

Sub ReadTextFile(filePath As String)
    Dim fileNum As Integer
    Dim line As String
    
    fileNum = FreeFile  ' Get an available file number
    
    Open filePath For Input As #fileNum
    
    Do While Not EOF(fileNum)
        Line Input #fileNum, line
        Debug.Print line  ' Process each line here
    Loop
    
    Close #fileNum
End Sub

To read the entire file at once:

Function ReadEntireFile(filePath As String) As String
    Dim fileNum As Integer
    Dim content As String
    Dim line As String
    
    fileNum = FreeFile
    Open filePath For Input As #fileNum
    
    Do While Not EOF(fileNum)
        Line Input #fileNum, line
        content = content & line & vbCrLf
    Loop
    
    Close #fileNum
    ReadEntireFile = content
End Function

Writing a Text File

Sub WriteTextFile(filePath As String, content As String)
    Dim fileNum As Integer
    fileNum = FreeFile
    
    Open filePath For Output As #fileNum  ' Overwrites existing file
    Print #fileNum, content
    Close #fileNum
End Sub

' Append to an existing file:
Sub AppendToFile(filePath As String, content As String)
    Dim fileNum As Integer
    fileNum = FreeFile
    
    Open filePath For Append As #fileNum
    Print #fileNum, content
    Close #fileNum
End Sub

Exporting a Query to CSV

A common task is exporting query results to a CSV file:

Sub ExportQueryToCSV(queryName As String, outputPath As String)
    Dim db As Database
    Dim rs As Recordset
    Dim fileNum As Integer
    Dim i As Integer
    Dim line As String
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset(queryName)
    
    fileNum = FreeFile
    Open outputPath For Output As #fileNum
    
    ' Write header row
    line = ""
    For i = 0 To rs.Fields.Count - 1
        If i > 0 Then line = line & ","
        line = line & """" & rs.Fields(i).Name & """"
    Next i
    Print #fileNum, line
    
    ' Write data rows
    Do While Not rs.EOF
        line = ""
        For i = 0 To rs.Fields.Count - 1
            If i > 0 Then line = line & ","
            Dim val As String
            val = Nz(rs.Fields(i).Value, "")
            ' Escape quotes and wrap in quotes
            val = Replace(val, """", """""")
            line = line & """" & val & """"
        Next i
        Print #fileNum, line
        rs.MoveNext
    Loop
    
    Close #fileNum
    rs.Close
    
    MsgBox "Exported to " & outputPath
End Sub

The FileSystemObject (FSO)

The FileSystemObject (from the Microsoft Scripting Runtime) provides a more object-oriented approach to file operations with additional capabilities:

' Add reference: Tools → References → Microsoft Scripting Runtime
Dim fso As New Scripting.FileSystemObject

' Check if file exists
If fso.FileExists("C:\data\report.xlsx") Then ...

' Check if folder exists
If fso.FolderExists("C:\Reports") Then ...

' Create a folder
fso.CreateFolder "C:\Reports\2026"

' Copy a file
fso.CopyFile "C:\source\data.accdb", "C:\backup\data_backup.accdb"

' Move a file
fso.MoveFile "C:\temp\export.csv", "C:\processed\export.csv"

' Delete a file
fso.DeleteFile "C:\temp\old_export.csv"

' Get file size
Dim size As Long
size = fso.GetFile("C:\data\database.accdb").Size

' Get all files in a folder
Dim folder As Scripting.Folder
Dim file As Scripting.File
Set folder = fso.GetFolder("C:\Reports")
For Each file In folder.Files
    Debug.Print file.Name & " - " & file.Size & " bytes"
Next file

Automated Backup with VBA

Combine file operations with date formatting for automated backups:

Sub BackupDatabase()
    Dim fso As New Scripting.FileSystemObject
    Dim sourcePath As String
    Dim backupPath As String
    Dim backupName As String
    
    sourcePath = CurrentDb.Name  ' Path to the current database
    backupName = "Database_" & Format(Now(), "yyyy-mm-dd_hhnn") & ".accdb"
    backupPath = "\\server\backups\" & backupName
    
    ' Compact first, then copy
    DBEngine.CompactDatabase sourcePath, "C:\temp\compact_temp.accdb"
    fso.CopyFile "C:\temp\compact_temp.accdb", backupPath
    fso.DeleteFile "C:\temp\compact_temp.accdb"
    
    MsgBox "Backup saved to: " & backupPath
End Sub

Processing Files in a Folder

A common automation pattern is processing all files in a folder — importing CSVs, archiving reports, etc.:

Sub ProcessImportFolder()
    Dim fso As New Scripting.FileSystemObject
    Dim folder As Scripting.Folder
    Dim file As Scripting.File
    Dim importPath As String
    Dim archivePath As String
    
    importPath = "C:\Imports\Pending\"
    archivePath = "C:\Imports\Processed\"
    
    Set folder = fso.GetFolder(importPath)
    
    For Each file In folder.Files
        If LCase(fso.GetExtensionName(file.Name)) = "csv" Then
            ' Import the CSV
            DoCmd.TransferText acImportDelim, , "tblImport", file.Path, True
            
            ' Move to archive
            fso.MoveFile file.Path, archivePath & file.Name
            
            Debug.Print "Processed: " & file.Name
        End If
    Next file
    
    MsgBox "Import complete."
End Sub

Conclusion

VBA file operations open up a world of automation possibilities beyond the database itself. Whether you are exporting data to CSV for other systems, automating backups, or processing incoming data files, the combination of Access VBA and the FileSystemObject gives you everything you need. Build these patterns into your toolkit and you will be able to automate entire data workflows with a single button click.