Using File System Objects in Microsoft Access

If you want to manipulate files and folders from within Access, here’s how you do it.

First, open a module or create a new one. From the Tools menu, choose References… and the References window opens.

Scroll down to the Microsoft entries and find ‘Microsoft Scripting Runtime’ and you’ll note that it refers to a file ‘scrrun.dll’. Click the box to include this library. Now you have access to file system objects in your code.

File System Object Reference Window

 

From Microsoft:
FileSystemObject was originally created for the Visual Basic Scripting Edition. FileSystemObject is not included in the object library for Visual Basic or Visual Basic for Applications. To use FileSystemObject, you must select the Microsoft Scripting Run-time in the Project References dialog box for your project.

Here’s a pretty simple use of the file system object, once the above reference has been installed. Please note that I heavily relied on the following resource to accomplish this code:
http://technet.microsoft.com/en-us/library/ee198742.aspx

Further note that I’ve created a table named zstbl_Import_Error_Messages for holding error messages that this code might generate. I then open this table for today’s errors if any occurred: If nz(DCount(“ErrorMessage”, “qryImportErrors”)) > 0 Then open query

Further note that I have another table, zstblInformation with a field: TMIDataFolder that holds the default location of files for import and move. The files are imported in the function fncImportTMIData()

 

 

Function fncAutomatedImport()

On Error GoTo AI_err

 

Dim val As Variant

Dim fso As New FileSystemObject

Dim objFolder As folder

Dim objFile As File

Dim colFiles As Variant

Dim folder As folder

Dim path As String

val = False

CurrentDb.Execute “DELETE * FROM zstbl_Import_Error_Messages WHERE ErrorMessageDate=#” & Date & “#”

‘Initialize path.

path = DLookup(“TMIDataFolder”, “zstblInformation”)

‘Get object.

If fso.FolderExists(path) = False Then

MsgBox “TMI Data File Import Folder does not exist.  Please create it: ” & path

Exit Function

ElseIf fso.FolderExists(path & “Backup_TMI_Files\”) Then

MsgBox “TMI Data File Import Backup Folder does not exist.  Please create it: ” & path & “Backup_TMI_Files\”

Exit Function

Else

Set objFolder = fso.GetFolder(path)

End If

Set colFiles = objFolder.Files

For Each objFile In colFiles

Debug.Print ” – fncAutomatedImport: ” & objFile.Name

If fncImportTMIData(path & objFile.Name) = True Then

If fso.FileExists(path & “Backup_TMI_Files\” & objFile.Name) Then

fso.DeleteFile (path & “Backup_TMI_Files\” & objFile.Name)

End If

fso.MoveFile path & objFile.Name, path & “Backup_TMI_Files\”

End If

Next

 

 

If nz(DCount(“ErrorMessage”, “qryImportErrors”)) > 0 Then

DoCmd.OpenQuery “qryImportErrors”

End If

 

val = True

 

AI_exit:

 

fncAutomatedImport = val

Exit Function

AI_err:

CurrentDb.Execute “INSERT INTO zstbl_Import_Error_Messages ( ErrorMessage, ErrorMessageDate, pathandfile ) ” & _

“SELECT ‘Error importing  during fncAutomatedImport’ AS ErrorMessage, #” & Date & “# AS ErrorMessageDate, ‘” & path & objFile.Name & “‘  AS pathandfile”

Resume Next

 

End Function

This entry was posted in Access 2007 problems/solutions. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *