Microsoft DOS Commands in VBA-2 ~ LEARN MS-ACCESS TIPS AND TRICKS

Sunday, May 6, 2012

Microsoft DOS Commands in VBA-2

Microsoft DOS Commands in VBA-2.

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 a constant Path, and with some 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 preceding examples, we provided the full pathname of the existing location where the new folder is to be created, with the new folder name specified at the end. However, if you are certain that the default Database location (that is, not the current database location on disk), you can issue the MkDir() command using only the new folder name, as shown in the following example:

MkDir "Projects"

The following three things you must be aware of before you run the above Command. MS Access is aware of two locations when a Database is active.

1. The Microsoft Access Default Database Folder Setting: File-> Options -> General -> Default Database Folder -> C:\Access Projects (can be changed)

2. The 'CurrentProject.Path': D:\Databases\Accounting\

3. Command with full location Address: MkDir D:\Databases\Budgeting

a. We will take the 3rd option first. This Command is faultless, specifying the full location address, and the folder will be created at the intended location.

b. MkDir "Budgeting" - the User runs this Command assuming that the folder will be created at the same level as D:\Databases\Accounting

c. This is where the mistake happens. The Folder will be created at C:\Access Projects\Budgeting

However, the above method is not the only way to check the Database location. Select Access Options from the Office Button, select the Popular option group in Access 2007, and locate the Default Database Folder setting. You can change this location if necessary. Refer to the image 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. 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 in the Debug Window):

? CurrentProject.Path

The above VBA statement displays the active database's Path. Let us assume that the current database location 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 (the Startup Screen or Main Switchboard) to change to the active database folder.

LEARN MS-ACCESS TIPS AND TRICKS

Sunday, May 6, 2012