Deleting Folders with DOS Command

Introduction.

We have already seen how to create folders with the MkDir() DOS command and how to change the default folder to the active database’s folder using the ChDir() command.

Conversely, if we can create folders, we should also be able to remove them. The RmDir() command serves this purpose, though it is not as commonly used as MkDir(). Typically, folders or sub-folders are created to organize files for easier access when needed. Folder removal becomes necessary only when files are relocated or deleted, and freeing up disk space is required.

To understand its usage, you can try running the RmDir() command directly from the Immediate Window (Debug Window), as shown below:

RmDir "C:\MyFolder"
Validation Checks.

1. The RmDir() command has a built-in safety check. When executed, it first verifies whether the specified folder is completely empty—containing no files or subfolders. If the command runs successfully, it completes silently without displaying any message. However, if the folder is not empty or cannot be removed, one of the following two error messages will appear:

   If the specified folder path does not exist, it will show a 'Path Not Found' Error Message.

2. If the folder is not empty, then the message ‘Path/File access error’ is displayed.

Through Windows Explorer, we can remove a folder with all its sub-folders and files in one clean sweep.  If this is done by mistake, then we can always restore them from the Recycle bin, also before emptying it.

Important Note: Folders or files deleted from a network location using Windows Explorer are not sent to the Recycle Bin. If you have the necessary access rights to delete items on the network, recovery cannot be done locally. In such cases, you must contact your Network Administrator to restore them from the most recent LAN backup. As a safeguard, many organizations allow users to create folders but restrict deletion rights to administrators, reducing the risk of accidental data loss.

A Custom Function.

Let’s create a small function named FolderDeletion() that will execute the RmDir() DOS command with proper validation checks. You can add this function to your common utilities library for reuse.

1. Before physically removing a folder, the function will perform the following validations:

   1. Check if the folder exists – Ensure the specified path is valid.

   2. Confirm the folder is empty – The folder should not contain any subfolders or files.

   3. Validate permissions – Confirm the user has rights to delete the folder.

   4. Error handling – Provide meaningful messages if deletion fails.

The Function VBA Code:

Public Function DeleteFolder(ByVal strFolder As String)
On Error Resume Next

If Len(Dir(strFolder, vbDirectory)) > 0 Then
  'Folder exists, ask for permission to delete the folder
  If (MsgBox("Deleting Folder: '" & strFolder & "', Proceed...?", vbOKCancel + vbDefaultButton2 + vbQuestion, "DeleteFolder()") = vbNo) Then
     'User says not to delete the folder, exit program
     GoTo DeleteFolder_Exit
  Else
     'Delete Folder
     RmDir strFolder
     
     If Err = 75 Then 'folder is not empty, have sub-folders or files
        MsgBox "Folder: '" & strFolder & "' is not empty, cannot be removed."
        GoTo DeleteFolder_Exit
     Else
        MsgBox "Folder: '" & strFolder & "' deleted."
        GoTo DeleteFolder_Exit
     End If
  End If
Else
  MsgBox "Folder: '" & strFolder & "' Not found."
End If

DeleteFolder_Exit:
On Error GoTo 0
End Function

If you want an alternative to the same result, we can use VBScript in Microsoft Access to do that.  VB Script is mostly used in Web Pages for server-side actions.  VB Script uses the FileSystemObject to manage Drives, Folders, & Files.  We have used it for creating Text, Word, and Excel Files before.

You can find those examples in the following links:

• Create Text File Access.
• Create Excel, Word Files.

VBScript Function: FolderCreation()

First, let us write a VBScript Function to create a Folder -  C:\MyProjects.

Public Function FolderCreation(ByVal strFolder As String)
Dim FSysObj, fldr
  
  On Error Resume Next 'arrange to capture the error so that it can be check
  'Create the File System Object
  Set FSysObj = CreateObject("Scripting.FileSystemObject")
  'Call the Create Folder Method of the File System Object with Folder Path as parameter
  Set fldr = FSysObj.CreateFolder(strFolder)
  'if this action ended up with error code 58 then the folder already exists
  If Err = 58 Then
     MsgBox "Folder: '" & strFolder & "' already exists."
     GoTo FolderCreation_Exit
  Else
     MsgBox "Folder: " & strFolder & " created successfully."
  End If
  
FolderCreation_Exit:
On Error GoTo 0
End Function

Copy and paste the above function into the Standard Module of your database.  You can try the function by calling it from the Debug Window with a folder name as shown below:

FolderCreation "C:\MyProjects"

VBScript Function: FolderDeletion().

After the sample run, use Windows Explorer to look for the folder name c:\MyProjects. The following VB Script Function FolderDeletion() can be used for removing a folder:

Public Function FolderDeletion(ByVal strFolder As String)
  Dim FSysObj, fldr
  
  On Error Resume Next
  Set FSysObj = CreateObject("Scripting.FileSystemObject")
  Set fldr = FSysObj.GetFolder(strFolder)
  If Err = 76 Then
     MsgBox "Folder: '" & strFolder & "' No found!"
  Else
     If MsgBox("Delete Folder: '" & strFolder & "' Proceed...?", vbOKCancel + vbDefaultButton2 + vbQuestion, "FolderDeletion()") = vbNo Then
         GoTo FolderDeletion_Exit
     Else
         fldr.Delete 'call the Delete Method of the Folder Object
         MsgBox "Folder: '" & strFolder & "' Deleted."
     End If
  End If
FolderDeletion_Exit:
On Error GoTo 0
End Function

Copy and paste the above code into the Standard Module of your database.  You can run the above code either from the Debug Window or call it from a Command Button Click Event Procedure.

Sample Run from Debug Window:

FolderDeletion "C:\MyProjects"

OR

Private Sub cmdRun_Click()
    FolderDeletion txtFolderPath
End Sub

Earlier Post Link References:

• Users and Groups Listing from Workgroup Information File
• Access Security Key Diagram
• Updating Sub-Form Recordset from Main Form
• Easy Reference: Access2003 Commands in Access2007
• IIF vs Switch function in MS-Access

Read More

ChDir and IN Clause of Access Query

ChDrive() and ChDir() Commands.

Last week, we learned how to change the Directory Path using VBA to the 'CurrentProject.Path' (active database’s location) using ChDrive() and ChDir() Commands, without altering the Default Database Folder settings under Access Options.

If you don’t like to use DOS commands, then you can change the Default Database Folder setting with the following VBA Statement:

Application.SetOption "Default Database Directory", "C:\Developers\Project"

The above statement will change the Default Database Folder to the location specified in the second parameter. The next example changes the Default Database Folder to the active database's location:

Application.SetOption "Default Database Directory", CurrentProject.Path

You can execute the above commands directly in the Debug Window. After running any of the above commands, open the Access Options from the Office Buttons and check the Default Database Folder control value under the Popular options group.

We have already discussed earlier updating/Appending data into external Database Tables (external Tables of Access, dBase, etc., not linked to the active Access Database)  by using the IN Clause in Queries.  You will find the Article here to refresh your memory.

Caution: If you have Queries in your Databases that reference Tables in external databases to update or Append data into them, like the sample SQL given below, it is time to review them to avoid unexpected side effects.

INSERT INTO Employees (EmployeeID, LastName ) IN 'C:\Developers\Projects\Northwind.mdb' 
SELECT 100221 AS EID, "John" AS LN; 

An external or back-end database is stored in a common location, on the Local Area Network (LAN), and it can be accessed by several front-end databases from different client machines.  This situation raises important considerations of issues of its own. We will address those issues separately, probably next week, as I don’t want to mix them in here and cause any confusion.

Coming back to the IN Clause in the above SQL, if the external database and the current database are in the same Folder, then you can omit the lengthy Pathname in the external database reference, like the modified SQL given below:

INSERT INTO Employees (EmployeeID, LastName ) IN 'Northwind.mdb' 
SELECT 100221 AS EID, "John" AS LN;

The main advantage of writing the IN Clause in this way is that you don't have to change the PathName in all SQLs of Queries on location change of your application. The downside is that we need to ensure that the Default Database Folder location is the active database's folder; otherwise, the Queries will look for the external database in the old location for updating/appending data.  You can do this, either using the SetOption method or using the ChDir() Command. Both methods are given below for reference:

SetOption Method:

SetOption "Default Database Directory", CurrentProject.Path

This method permanently changes the Default Database Folder control value in the Access Options area and remains active till it is changed again.  This is a global change in Access Options and may affect other databases when they are open.

ChDir() Method:

Public Function ChangeDir()
Dim vDrive As String * 1, sysPath As String

'get current database Path
  sysPath = CurrentProject.Path

'extract the drive letter alone
'vDrive Variable is dimensioned to hold only one character
  vDrive = sysPath 

'change control to the Drive
  ChDrive vDrive 

'change current location to the database path
  ChDir sysPath 

End Function

This method is harmless because the change is temporary, and the Default Database Folder global setting remains intact. You can use the above Code in databases that require this Function. 

One of these methods must run immediately on opening the database, either through an Autoexec Macro with the RunCode Action or through the Form_Load() Event Procedure of the first Form opened.

Earlier Post Link References:

• Access Security Key Diagram
• Updating Sub-Form Recordset from Main Form
• Easy Reference: Access2003 Commands in Access2007
• IIF vs Switch function in MS-Access
• Preparing Rank List

Read More

Microsoft DOS Commands in VBA-2

Continued from Last Week.

With the MkDir() Command, we were able to create a folder on disk with a small VBA routine that we wrote last week.  We don’t even need a separate program to do this; we can directly execute this command from the Debug Window, like the following example:

MkDir "C:\Developers\Projects"

The only disadvantage of this method is that we cannot perform a validation check before executing this command.  In the VBA program, we have included the validation checks.  That program uses constant values as Path, and with a few modifications, this program can be further improved to accept the Path string as a Parameter to the CreateFolder() Function.  The Code with improvements is given below:

Creating a Folder.

Public Function CreateFolder(ByVal folderPath As String)

Dim msgtxt As String, folderName As String

'extract the new Folder Name from the folderPath Parameter
folderName = Right(folderPath, Len(folderPath) - InStrRev(folderPath, "\"))

'check for the new folder name, if not found proceed to create it
If Dir(folderPath, vbDirectory) = "" Then 
   msgtxt = "Create new Folder: " & folderPath & vbCr & "Proceed ...?"
   If MsgBox(msgtxt, vbYesNo + vbDefaultButton1 + vbQuestion, "CreateFolder()") = vbNo Then
      Exit Function
   End If
   MkDir folderPath 'try to create the new folder

'check whether the folder creation was successful or not
   If Dir(folderPath, vbDirectory) = folderName Then
      msgtxt = folderPath & vbCr & "Created successfully."
      MsgBox msgtxt
   Else
'if the code execution enters here then something went wrong
      msgtxt = "Something went wrong," & vbCr & "Folder creation was not successful."
      MsgBox msgtxt
   End If
Else
  'the validation check detected the presence of the folder
   msgtxt = folderPath & vbCr & "Already exists."
   MsgBox msgtxt
End If

End Function

In all the above and earlier examples, we have provided the full path of the existing location, where the new folder is to be created, with the new folder name at the end.  If you are sure where the current location is (or the active path of the current database on disk), then you can issue the MkDir() command with the new folder name alone, like the following example:

MkDir "Projects"

Finding out the Current Folder.

As far as VBA is concerned, the current location is not what you have selected using Windows Explorer. Or the one selected using DOS Command ChDir(), which runs directly under the DOS Command Prompt.

But, with a small trick, we can find out which is the current folder that VBA is aware of, that is, by running the Shell() command directly from the Debug Window to invoke the DOS Command Prompt from VBA, like the example given below:

Call Shell("cmd.exe")

The above command will open the DOS Command Prompt (if it is minimized on the taskbar, then click on it to make that window current), and the Cursor will be positioned in the current folder. Check the sample image given below:

If you have used MkDir "Projects" like a command without knowing where it is going to be created, then type Dir and press Enter Key to display a list of files and directory names with the label <Dir> to indicate they are folders.

That doesn’t mean the above method is the only option to check the Default Database Folder location.  Select Access Options from the Office Button, and select the Popular Option Group(Access 2007), and the Default Database Folder settings. You can change it if it becomes necessary.  Check the image given below:

Try to open a database from another location on disk, but this setting will not change, and the Default Database Folder will remain active as per this setting.  Without touching the above Access default setting, we can change the active folder to the newly opened database’s parent directory with the use of the following DOS Commands from VBA (you can try this by typing these commands directly on the Debug Window):

? CurrentProject.Path

This is not a DOS Command, but the above VBA statement retrieves the active database's Path. Let us assume that the retrieved location of the current database is: C:\MDBS

Using the above information in the next two DOS Commands, we can change the control to the active database's location, without changing the Default Database Path setting, which we have seen earlier:

ChDrive "C" 'change control to C: Drive.  This is necessary if the control was on a different drive
ChDir CurrentProject.Path 'change control to the active database's folder

ChangeDir() Command.

By combining the above statements, we can write a very useful Function ChangeDir() to change the control to the current Database Folder.  Copy and paste the following Code into a Standard Module of your Database and save it:

Public Function ChangeDir()
Dim vDrive As String * 1, sysPath As String

'get current database Path
  sysPath = CurrentProject.Path

'extract the drive letter alone
'vDrive Variable is dimensioned to hold only one character
  vDrive = sysPath 

'change control to the Drive
  ChDrive vDrive 

'change current location to the database folder
  ChDir sysPath 

End Function

Call the above Function from an Autoexec macro with the RunCode Action or from the Form_Load() Event Procedure of the first Form open (like the Startup Screen or Main Switchboard) to change control to the active database folder.

• Updating Sub-Form Recordset from Main Form
• Easy Reference: Access2003 Commands in Access2007
• IIF vs Switch function in MS- Access
• Preparing Rank List
• Restoring Disabled Full Menus Access 2007

Read More