SetOption "Default Database Directory"

[john]

In using the following on the Open event of a form:
SetOption "Default Database Directory", "c:\Program Files\Operations"

I get errors from list and combo boxes that fire before this event and
cannot find the Databases they are set to, unless I Code the rowsource after
this event, but even beyond that I get other error's too, unless I close the
database and reopen it with that as the now Default Directory.

Question is, am I putting the SetOption in the wrong place, can I make it
set the Default on the opening of the Database, instead of the Open form
event?

Thanks John

 

[Douglas J. Steele]

I'm having a hard time imagining why the rowsource for a list or combo box
would care about the default database directory.

Are you trying to refer to an external database in the rowsource? If so,
what does your SQL look like?

 

[john]

Yes it is External, here is the SQL:
SELECT DISTINCTROW ExternalBook.ExternalBook, ExternalBook.Status,
ExternalBook.Default
FROM [ExternalDbLogs.mdb].ExternalBook
WHERE ((Not (ExternalBook.ExternalBook) Is Null))
ORDER BY ExternalBook.ExternalBook;

As I have said, the SQL works Okay once I close and reopen the DataBase,
Just not the first time I open it.

 

[Douglas J. Steele]

You should specify the complete path rather than simply the file name:

SELECT DISTINCTROW ExternalBook.ExternalBook, ExternalBook.Status,
ExternalBook.Default
FROM ExternalBook IN "c:\Program Files\Operations\ExternalDbLogs.mdb"
WHERE ((Not (ExternalBook.ExternalBook) Is Null))
ORDER BY ExternalBook.ExternalBook;


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Yes it is External, here is the SQL:
SELECT DISTINCTROW ExternalBook.ExternalBook, ExternalBook.Status,
ExternalBook.Default
FROM [ExternalDbLogs.mdb].ExternalBook
WHERE ((Not (ExternalBook.ExternalBook) Is Null))
ORDER BY ExternalBook.ExternalBook;

As I have said, the SQL works Okay once I close and reopen the DataBase,
Just not the first time I open it.

I'm having a hard time imagining why the rowsource for a list or combo
box would care about the default database directory.

Are you trying to refer to an external database in the rowsource? If so,
what does your SQL look like?

 

[john]

I understand what you mean, but with about 30 queries, that is alot of
changing and eliminates the chance to distribute and allowing them to set
their own network directory.

Acutally that is not a key problem because I can code the rowsource to set
after the Default Directory Change and that works the first time I open the
form.

As I said it works except for the first time the database opens and is set
to a new Directory, What I can't figure out, is why dosent it work the first
time, is the form Open event the right place to put it?

I get a Compile error the first time I open the Form and it is set to a new
Directory, even with the rowsource problem solved. Then when I close the
Database and reopen it and the form, everything is fine. Even the rowsource
can be preset in the Properties rather than by code.

John

You should specify the complete path rather than simply the file name:

SELECT DISTINCTROW ExternalBook.ExternalBook, ExternalBook.Status,
ExternalBook.Default
FROM ExternalBook IN "c:\Program Files\Operations\ExternalDbLogs.mdb"
WHERE ((Not (ExternalBook.ExternalBook) Is Null))
ORDER BY ExternalBook.ExternalBook;


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Yes it is External, here is the SQL:
SELECT DISTINCTROW ExternalBook.ExternalBook, ExternalBook.Status,
ExternalBook.Default
FROM [ExternalDbLogs.mdb].ExternalBook
WHERE ((Not (ExternalBook.ExternalBook) Is Null))
ORDER BY ExternalBook.ExternalBook;

As I have said, the SQL works Okay once I close and reopen the DataBase,
Just not the first time I open it.

I'm having a hard time imagining why the rowsource for a list or combo
box would care about the default database directory.

Are you trying to refer to an external database in the rowsource? If so,
what does your SQL look like?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



In using the following on the Open event of a form:
SetOption "Default Database Directory", "c:\Program Files\Operations"

I get errors from list and combo boxes that fire before this event and
cannot find the Databases they are set to, unless I Code the rowsource
after this event, but even beyond that I get other error's too, unless
I close the database and reopen it with that as the now Default
Directory.

Question is, am I putting the SetOption in the wrong place, can I make
it set the Default on the opening of the Database, instead of the Open
form event?

Thanks John

 

[Douglas J. Steele]

Not really sure why it doesn't work, but you could try simply using ChDir to
change the current directory.

As well, you might find it better to create linked tables, and give them a
means to change the location of the backend database (using code such as is
illustrated at http://www.mvps.org/access/tables/tbl0009.htm at "The Access
Web")

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I understand what you mean, but with about 30 queries, that is alot of
changing and eliminates the chance to distribute and allowing them to set
their own network directory.

Acutally that is not a key problem because I can code the rowsource to set
after the Default Directory Change and that works the first time I open
the form.

As I said it works except for the first time the database opens and is set
to a new Directory, What I can't figure out, is why dosent it work the
first time, is the form Open event the right place to put it?

I get a Compile error the first time I open the Form and it is set to a
new Directory, even with the rowsource problem solved. Then when I close
the Database and reopen it and the form, everything is fine. Even the
rowsource can be preset in the Properties rather than by code.

John

You should specify the complete path rather than simply the file name:

SELECT DISTINCTROW ExternalBook.ExternalBook, ExternalBook.Status,
ExternalBook.Default
FROM ExternalBook IN "c:\Program Files\Operations\ExternalDbLogs.mdb"
WHERE ((Not (ExternalBook.ExternalBook) Is Null))
ORDER BY ExternalBook.ExternalBook;


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Yes it is External, here is the SQL:
SELECT DISTINCTROW ExternalBook.ExternalBook, ExternalBook.Status,
ExternalBook.Default
FROM [ExternalDbLogs.mdb].ExternalBook
WHERE ((Not (ExternalBook.ExternalBook) Is Null))
ORDER BY ExternalBook.ExternalBook;

As I have said, the SQL works Okay once I close and reopen the DataBase,
Just not the first time I open it.


I'm having a hard time imagining why the rowsource for a list or combo
box would care about the default database directory.

Are you trying to refer to an external database in the rowsource? If
so, what does your SQL look like?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



In using the following on the Open event of a form:
SetOption "Default Database Directory", "c:\Program Files\Operations"

I get errors from list and combo boxes that fire before this event and
cannot find the Databases they are set to, unless I Code the rowsource
after this event, but even beyond that I get other error's too, unless
I close the database and reopen it with that as the now Default
Directory.

Question is, am I putting the SetOption in the wrong place, can I make
it set the Default on the opening of the Database, instead of the Open
form event?

Thanks John