VBA File Operations: Read, Write, and Manage Files from Access
Access VBA can read and write text files, check if files exist, copy and move files, and automate file management tasks. Here is the complete guide.
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.
Work With Access Evolved
If you found this article useful, Access Evolved specializes in exactly this kind of work — from hands-on Microsoft Access support to full database modernization projects.
Automating file operations in Access VBA? Schedule a free call and get help building reliable, maintainable file handling code.
Thinking about migrating?
Ready to move beyond Microsoft Access?
Access is a great starting point, but growing businesses often hit its limits. Anthony Passeri specializes in migrating Access databases to modern platforms — Power Platform, Power Apps, SQL Server, Azure, and more — so you keep your data and workflows without the headaches.
Written by
Anthony Passeri, MBA
Access Expert and Editor
Bridging the gap between Access and the Future
Explore Topics
Keep reading
More on VBA & Macros
Automating Excel from Access VBA: Export, Format, and Send
Access VBA can create, populate, format, and save Excel workbooks automatically. Learn how to build powerful Excel automation workflows from your database.
Sending Emails from Access with VBA: Outlook and SMTP
Automate email notifications, reports, and alerts directly from Access using VBA. Learn both the Outlook automation approach and direct SMTP sending.
VBA Arrays and Collections: Managing Lists of Data in Code
Learn how to use arrays, Collections, and Dictionaries in Access VBA to manage lists of data efficiently without hitting the database for every operation.