ChDir and IN Clause of Access Query
Last week we have learned how to change the Directory Path control of VBA to the CurrentProject.Path (active database’s location) with the use of 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 executing any of the above commands you may open Access Options from Office Buttons and check the Default Database Folder control value under the Popular options group.
We have already discussed earlier about 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 that Article here to refresh your memory.
If you have Queries in your Databases that references Tables in external databases to Update or Append data into them, like the sample SQL given below, it is time to take a relook at them to avoid unexpected side effects.
INSERT INTO Employees (EmployeeID, LastName ) IN 'C:\Developers\Projects\Northwind.mdb' SELECT 100221 AS EID, "John" AS LN;
If external or backend database is on a common location on Local Area Network (LAN) Server, serviced by several front-end databases from client machines that itself is asking for separate treatment of the whole issue which we will look at them later, probably next Week. I don’t want to mix them up here and confuse you.
Coming back to the IN Clause in the above SQL, if the external database and the current database is on 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 down side is that you have to ensure that the Default Database Folder location changes to 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 "Default Database Directory", CurrentProject.Path
This method permanently changes the Default Database Folder control value in the Access Options area and remains intact till it is changed again. This is a global change to Access Options and may affect other databases when they are open.
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 requires this Function.
One of these methods must be 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.