Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

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 Method:

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.

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 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.

Share:

No comments:

Post a Comment

Translate



PageRank
Your email address:

Delivered by FeedBurner

Search

Infolinks Text Ads

blog.feedspot.com Microsoft Access Blogs

Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Labels

Forms How Tos Functions MS-Access Security Reports msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Queries External Links msaccess reports msaccess tips Accesstips Menus and Toolbars MsaccessLinks Process Controls Art Work Downloads msaccess How Tos Graph Charts msaccessQuery Array List Boxes Command Buttons Emails and Alerts Property Query Combo Boxes Custom Wizards DOS Commands Data Objects VBA ms-access functions msaccess functions msaccess graphs msaccess reporttricks Calculation Class Module Data Type msaccessprocess security advanced Access Security Custom Functions Macros Menus Object Reference Report Top Values Variables msaccess email msaccess menus progressmeter Access2007 Auto-Number Command Button Copy Expression Field Type Fields Form Form Instances Join Methods Microsoft Numbering System Records Security Split SubForm Table Utility Workgroup database msaccess wizards Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Conditional Formatting Controls Data Filtering Defining Pages Diagram Disk Dynamic Lookup Error Handler Excel Export External Filter Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Monitoring Octal Numbers Operating System Paste Primary-Key Product Rank Reading Recordset Rich Text Sequence SetFocus Summary Tab-Page Tables Time Difference Union Query User Users Water-Mark Word automatically commands function hyperlinks iSeries Date iif ms-access msaccess msaccess alerts pdf files reference restore switch text toolbar tutorial updating upload vba code

Featured Post

Base Class and Derived Object Variants

Last week we have tried an example as how to pass a Base Class Object, through the Set Property Procedure,  to become part of the Object in...

Labels

Blog Archive

Recent Posts