Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles

MS Access

Posted Nov 16, 2007

File Management with Scripting Object

By Danny Lesandrini

Managing files should be really simple in Access, but it's not. Well, once you get familiar with the File Scripting Object (FSO), it's not so bad, but getting started is the painful part. To help you along, this month's download includes sample code for both Access (early bound) and VB Script (late bound).

Here's what the sample code will demonstrate:

  • Use FSO in a VB Script file to backup a file
  • Use FSO from Access to perform the following
    1. Retrieve a list of files from a folder
    2. Show file attribute details
    3. Copy files from one folder to another

That's not too ambitious for one article, but it's functionality that will be returned to time and again. Let's see how it works.

FSO in VB Script

The download includes a file named FileCopy.vbs, the text of which is reproduced below. This is a VB Script file, which may be edited in Notepad. However, if you double click this file, Windows will try to execute the code inside, so be careful with these.

Most of the script relates to creating a prefix for the file, based on today's date. Comments make up the bulk of what's left, and way down there at the bottom are the couple of lines that do the work ... the FileScriptingObject calls.

The FileScriptingObject API has been around long enough that, unless you're still running Windows 95, you needn't worry about installing anything, and even then, it might be there. All you need to do is call CreateObject() to instantiate an instance, and start whacking away with commands. In this example, we create the FSO object, and copy a file:

    Set objFSO= CreateObject("Scripting.FileSystemObject")
    objFSO.CopyFile sDevFile, sArcFile
    Set objFSO= Nothing

This is called Late Binding, because our variable, objFSO, is a variant type and the CreateObject() call is used to load the Scripting.FileSystemObject into it. This is all that's available to us in a VB Script file, but it works, so we don't complain.

The full text of the script is below. Copy it from this web page (watch for word wrap at the arrow) and paste into a text file. Change the file paths ,save it as Something.vbs and you're ready to go. I use a script like this every day to make copies of my dev files to an archive folder on the server. Once you see how we use FSO in the Access application, you'll realize how powerful this object is, and remember, everything can be done in VB Script files, like the one below.

' *********************************************************************************
'   Script To Backup an Access MDB file to a Archive Server  
'     Danny J. Lesandrini
'     October 18, 2007
'     dlesandrini@hotmail.com
'     DataFast Consulting
' *********************************************************************************

    '  Note, if you already didn't realize, that VB Script variable 
    '  are declared without a data type.  While VBA in Access allows 
    '  for them, script does not.
    Dim sPrefix, sMonth, sDay, sYear, sHour, 
    sMinuite, objFSO, sDevFile, sArcFile

    ' ////////////////////////////////////////////////////////////////////////////
    ' Get the file prefix for archive filename  
    '    Format:  YYYY-MM-DD_HH-NN  i.e. 2007-10-18_08-27_
    sYear = DatePart("yyyy",Date())
    sMonth = DatePart("m",Date())
    If Len(sMonth) = 1 Then sMonth = "0" & sMonth
    sDay = DatePart("d",Date())
    If Len(sDay) = 1 Then sDay = "0" & sDay
    sHour= DatePart("h",Now())
    If Len(sHour) = 1 Then sHour = "0" & sHour
    sMinuite = DatePart("n",Now())
    If Len(sMinuite) = 1 Then sMinuite = "0" & sMinuite 
    sPrefix = sYear & "-" & sMonth & "-" & sDay & "_" & sHour & "-" & sMinuite & "_"
    ' ////////////////////////////////////////////////////////////////////////////
    ' Set paths to development and Archive files
    sDevFile = "C:\YourDevelopment\YourFile.mdb"
    sArcFile = "\\YourServer\Public\Archive\" & sPrefix & "YourFile.mdb"
    ' The Scripting Object does the work
    Set objFSO= CreateObject("Scripting.FileSystemObject")
    objFSO.CopyFile sDevFile, sArcFile
    Set objFSO= Nothing

    ' All done.  Report to user.
    MsgBox "Development file has been copied to Archive.", 64,"Finished"

FSO from Access

Interestingly, the code above will work in Microsoft Access as is, provided it's placed in a Function or Sub routine. However, there's a trick that will expose the world of FSO to us through IntelliSense. To enter that world, we need to make one tweak to our Access mdb file: Add a reference to the Windows Scripting Runtime library.

To add a reference, open any module and select References from the Tools menu. Scroll down the list until you find the item highlighted in the screen shot below, the Microsoft Scripting Runtime. Select it and click OK to save the selection. This allows for Early Binding of variables, as we'll see below.

Now that you have reference to FSO, you can create an early bound variable and take advantage of Microsoft IntelliSense. This time, we create an object, objFSO, not as a variant type, but of the type FileSystemObject. Now, Access can provide help with properties and methods that are available to this object, as shown in the image below.

We'll see how this becomes helpful when we review the functionality of the demo application.

The Demo Application

I'm not going to review all the code in the demo app in this article, but you can download it for yourself and step through to see how it works. All the code relating to FSO is behind frmMain.

I will, however, explain the code used to populate the list of files. You'll need to declare some variables as FileSystemObject, Folder and File. Also, because I reuse this function for both listboxes (both left and right), it takes an argument named sType to identify the control to be affected.

Private Function LoadFileList(ByVal sType As String) As Long
    On Error GoTo Err_Handler

    Dim objFSO As FileSystemObject
    Dim objFld As Folder
    Dim objFile As File
    Dim strSQL As String
    Dim intItem As Integer
    Dim ctlList As Access.ListBox
    Dim lngCount As Long
    Dim strFolder As String
    Dim strFile As String
    ' This function works for both list boxes, Left and Right
    ' The function argument, sType, determines which, and a
    ' control, ctlList, is set to the appropriate control.
    If sType = "Left" Then
        Set ctlList = Me!lstLeft
        strFolder = Me!txtLeft
    ElseIf sType = "Right" Then
        Set ctlList = Me!lstRight
        strFolder = Me!txtRight
        ' Bad call.  Can't proceed
        Exit Function
    End If
    ' This is how objFSO is instantiated as an FSO object,
    ' and objFld is set to the selected folder
    Set objFSO = New FileSystemObject
    Set objFld = objFSO.GetFolder(strFolder)
    ' First, I empty the listbox of it's contents
    For intItem = 0 To ctlList.ListCount - 1
        ctlList.RemoveItem 0
    ' Now, loop through the files in folder object and
    ' add the file name to the list.
    For Each objFile In objFld.Files
        ctlList.AddItem objFile.Name
        lngCount = lngCount + 1
    LoadFileList = lngCount
    ' Don't forget to clean up after yourself!
    Set objFSO = Nothing
    Set objFld = Nothing
    Set objFile = Nothing
    Exit Function
    MsgBox Err.Description, vbCritical
    Resume Next
End Function

Similar code is used in a function called when you double-click on an item in the listbox. That function is named ShowFileInfo() and after collecting some of the interesting information about a file, it's dumped into a message box. The code to grab this data is shown below the screen shot, and as you can see, it's pretty much self-documenting.

    Set objFSO = New FileSystemObject
    Set objFile = objFSO.GetFile(strFile)
    strOut = "File:      " & objFile.Name & vbCrLf & _
             "Size:      " & objFile.Size & vbCrLf & _
             "Type:      " & objFile.Type & vbCrLf & _
             "Created:   " & objFile.DateCreated & vbCrLf & _
             "Modified:  " & objFile.DateLastModified & vbCrLf & _
             "Parent:    " & objFile.ParentFolder & vbCrLf
    MsgBox strOut, vbInformation


The FileSystemObject library has been around for a while, so this is nothing new. Still, there are times when you just need to loop through folders and files to perform maintenance, or as in this app, to list and interrogate files. Download the demo application and give it a whirl.

» See All Articles by Columnist Danny J. Lesandrini

MS Access Archives

Latest Forum Threads
MS Access Forum
Topic By Replies Updated
Help With Microsoft Access kasy 0 September 4th, 07:35 PM
Linked table not sorting or filtering - ODBC error Java 1 August 28th, 10:37 AM
Use Parameter in select statement (Sql in Microsoft Access) katty.jonh 1 July 25th, 06:45 AM
Query Issue algebroni 7 July 23rd, 04:22 PM