ACCESS 2003 - Use VBA to Set Default Output Folder

[Mike Thomas]

We have an app in which we frequently use the Tools > Office Links > Analyze
it with Excell button when we run queries.

The "Analyze" button creates an Excel spreadsheet somewhere. We run these
apps on different servers and need to specify where Excel should create
these files. I think we need to manipulate the Tools > Options > General >
Default Database folder setting.

I'd like to do this when the app starts in a VBA module which initializes
other elements.

How can I change this folder in a VB module?

Many thanks
Mike Thomas

 

[TC]

I don't have Access here to check, so here's a round-about way to find
out.

Go to that option and manually set a folder location that you will
recognize in the next step; eg. c:\blah. Then run the following code
from within a standard module of that database, to list the custom
property names and their values:

(untested)

dim prp as property
for each prp in dbengine(0)(0).properties
debug.print prp.name; vbtab;
on error resume next
debug.print prp.value;
if err.number <> 0 then debug.print err.description;
on error goto 0
debug.print
next

Say you see the relevant property is called "StartupFolder". You could
set that property, through code, with the following statement:

dbengine(0)(0).properties![StartupFolder] =
"c:here\there\everywhere"

That statement assumes that (a) there /is/ a custom database property
for this purpose, and (b) you have created that property by setting a
value into it, once, through the normal Access UI.

As I said, I'd give you a simpler answer, except that I can't remembe
the details & I don't have Access here to check!

HTH,
TC [MVP Access]