I thought I’d share the solution I provided to a member in a recent UtterAccess thread:

When adding files to Attachment field can I open a dialog box in a predefined location?

... I would like to have the dialog box, which opens when you click "Add" in Attachment, to open that particular vendor's folder to chose from and not to navigate to that folder from some other default location ...

The question was a simple one:

How can we change the directory the Add attachment dialog opens up to by default.

The issue being that this is a built-in function behind a command button that we have no control over.  So what is one to do exactly?

Solution 1 – Default Database Directory

The solution is relatively simple, we need to change the Default Database Directory and to do so via VBA we can use the SetOption method by doing something like:

SetOption "Default Database Directory", "C:\Temp\"

Reverting Your Changes

You can restore the default value back at any time by simply setting its value back to the path to your Documents folder.  Also, remember that the Default Database Directory is always accessible through the GUI via

File -> Options -> General (under the Creating databases heading)

Why Default Database Directory?

With a setting title of Default Database Folder and the quote above, one can easily wonder why this works to change the Attachment dialog’s default folder?

The truth is, I have no clue!  Why we need to modify the Default Database Folder setting to change the path the attachment folder dialog opens up to is baffling to me as well, but it is the reality the Access Dev Team has given us and at least it is manageable and can be automated.

I simply played around with various settings until I found this one which seemed to work.  Never did I find any documentation that mentioned that the Default Database Folder property controlled the default directory for adding attachments.

In reality, the Default Database Folder property title would appear to be incorrect/outdated and should probably be revised to Default Working Directory or Default File Directory as it is far more than the location to which Access saves databases by default!

Solution 2 – ChDir

Another option is available to us, that is to change the ‘working’ directory using the ChDir statement.  What is critical here is not to forget to use the ChDrive statement as well or it will not work!

chdrive "C"
chdir "C:\Temp"

You can use CurDir() to review what the current ‘working’ directory is.

This setting is not permanent and is lost when the database is closed and reverts back to the Default Database Folder value when your database is reopened.  So this is the type of thing you’d want to incorporate into an AutoExec macro, form event, … to apply it when necessary knowing you’re not permanently altering any settings.

Conclusion

Either way, now, when you click the Add command button, it will start in the specified folder (in this case C:\Temp) saving your end-users the hassle of having to navigate to a specific folder.

Don’t underestimate this type of thing.  If you can save a user 2-3-5+ clicks every time they add an attachment and they do this multiple times a day, this can be a huge time savings for them.   These are the seemingly small types of things that can truly have a huge impact on the overall user experience!

Useful References on the Subject

Application.SetOption method (Access)

The SetOption method sets the current value of an option in the Access Options dialog box.

ChDrive statement (VBA)

Changes the current drive.

ChDir statement (VBA)

Changes the current 'working' directory or folder.

CurDir function (Visual Basic for Applications)

Returns a Variant (String) representing the current path.