ChDir and IN Clause of Access Query ~ LEARN MS-ACCESS TIPS AND TRICKS

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.