Database Options - Any other settings I should adjust?

[ARC]

Hello all,

I have one chance to get this right, as I'm nearing a release of a program.
I've looked at the database settings, and so far, have set the following:

* Unchecked 'Enable design changes for tables in datasheet view'
* Unchecked 'Check for truncated number fields' (I would prefer the user NOT
see #### in fields or columns that they shrink too far)
* Unchecked 'Track Name AutoCorrect Info'
* Set all Tables to 'SubDatasheet = None' (BTW, I read to never use Auto...
What about setting the subdatasheet to the correct table, instead of auto??

Setting's I'm not sure about:

* Remove personal information from file properties on save. ( I would think
I would want my company to show in the file properties, but maybe not other
personal details, etc.)

* Compact on Close. I assume this only works for the program mdb file, and
would not work on the back-end database? Might not be a bad idea to set this
for the program mdb file. Anyone have experience with this setting?

Also, I would imagine that items under the Advanced tab are not for the
current database, things like OLE/DDE timeout, Refresh interval, etc.

Any other items I should set for the current database?

Many Thanks,

Andy

['69 Camaro]

Hi, Andy.

* Set all Tables to 'SubDatasheet = None' (BTW, I read to never use
Auto... What about setting the subdatasheet to the correct table, instead
of auto??

That's not quite as slow as Auto, but it can severely slow your database
application. Don't use subdatasheets.

* Remove personal information from file properties on save. ( I would
think I would want my company to show in the file properties, but maybe
not other personal details, etc.)

Then just leave what you want others to see. Microsoft Office records the
information you gave when you installed it on your hard drive (that's why
you see your company name and other such data in the Database Properties),
and automatically places it in the Database Properties when the file is
created, so it's best to check that information before distributing the
file.

* Compact on Close. I assume this only works for the program mdb file, and
would not work on the back-end database?

It works on the Jet database file that has been opened via the Access GUI
(interface). Any Jet database file that is only opened by merely linking to
the tables it holds cannot make this setting apply to _that_ database. And
it's not a good idea to use "Compact on Close" in a multiuser database,
anyway. Compact it periodically when the users aren't in it.

Also, I would imagine that items under the Advanced tab are not for the
current database, things like OLE/DDE timeout, Refresh interval, etc.

Why not?

Any other items I should set for the current database?

Ensure Option Explicit is set in the Declarations area of every module,
"Require Variable Declaration" is checked, "Compile on Demand" is not
checked, "Allow Design Changes" is set to "Design View Only" in all forms,
and only allow one form view in each form (unless it's really, really
necessary to have more than one form view setting.

It's best to have custom menus instead of the built-in menus so that you
control what the users do when using the application. It's also usually
best to convert to an MDE and distribute the MDE database file, not the MDB
database file (but ensure that you keep a copy of the MDB file to easily
make design changes or upgrades to future versions later).

There are a few more settings to check, but I can't think of them right now.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/ex...ributors2.html for contact
info.

[ARC]

Thanks, Gunny!

Can you think of any pitfalls with allowing the program mdb file to be
compacted on close? This would seem a good idea, in theory, as the program
file wouldn't severely inflate over time.
"'69 Camaro" <Fo************ **************@ Spameater.orgZE RO_SPAMwrote in
message news:nf8Li.3$n9 2.1@trnddc06...

Hi, Andy.

>* Set all Tables to 'SubDatasheet = None' (BTW, I read to never use
Auto... What about setting the subdatasheet to the correct table, instead
of auto??

That's not quite as slow as Auto, but it can severely slow your database
application. Don't use subdatasheets.

>* Remove personal information from file properties on save. ( I would
think I would want my company to show in the file properties, but maybe
not other personal details, etc.)

Then just leave what you want others to see. Microsoft Office records the
information you gave when you installed it on your hard drive (that's why
you see your company name and other such data in the Database Properties),
and automatically places it in the Database Properties when the file is
created, so it's best to check that information before distributing the
file.

>* Compact on Close. I assume this only works for the program mdb file,
and would not work on the back-end database?

It works on the Jet database file that has been opened via the Access GUI
(interface). Any Jet database file that is only opened by merely linking
to the tables it holds cannot make this setting apply to _that_ database.
And it's not a good idea to use "Compact on Close" in a multiuser
database, anyway. Compact it periodically when the users aren't in it.

>Also, I would imagine that items under the Advanced tab are not for the
current database, things like OLE/DDE timeout, Refresh interval, etc.

Why not?

>Any other items I should set for the current database?

Ensure Option Explicit is set in the Declarations area of every module,
"Require Variable Declaration" is checked, "Compile on Demand" is not
checked, "Allow Design Changes" is set to "Design View Only" in all forms,
and only allow one form view in each form (unless it's really, really
necessary to have more than one form view setting.

It's best to have custom menus instead of the built-in menus so that you
control what the users do when using the application. It's also usually
best to convert to an MDE and distribute the MDE database file, not the
MDB database file (but ensure that you keep a copy of the MDB file to
easily make design changes or upgrades to future versions later).

There are a few more settings to check, but I can't think of them right
now.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/ex...ributors2.html for contact
info.

[AnandaSim]

On Sep 29, 1:13 am, "ARC" <PCES...@PCESof t.invalidwrote:

Thanks, Gunny!

Can you think of any pitfalls with allowing the program mdb file to be
compacted on close? This would seem a good idea, in theory, as the program
file wouldn't severely inflate over time.

All kinds of things can happen with end users. Compact on close means
that the mdb is always undergoing maintenance every time you close the
mdb. And if for any reason Windows does not behave itself, your end
user will ring you up asking why there is a file called database1.mdb
and why the thing has stopped working.

Not something I would do.

HTH
Ananda

['69 Camaro]

Hi, Andy.

Can you think of any pitfalls with allowing the program mdb file to be
compacted on close?

Well, the user has to wait every time he closes the database for it to
compact and then quit. That's just a minor inconvenience. But since no
else is sharing the front end in a properly split database, the users won't
be attempting to compact while other users are still using the file, and
thereby creating all those db1.mdb, db2.mdb, db3.mdb FOD in the directory.

This would seem a good idea, in theory, as the program file wouldn't
severely inflate over time.

What have you got in the front end that needs to be compacted every time the
database file is opened? The non-static data should be in the back end,
which should be compacted periodically. The front end's file size should
remain relatively the same size once application development has stopped.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/ex...ributors2.html for contact
info.

[ARC]

Hey Gunny,

I've looked everywhere under the Access options in 2007, and I can't find
the following two:

"Require Variable Declaration" is checked, "Compile on Demand" is not
checked..

Did 2007 do away with these settings?

Thanks!

[ARC]

Ok, you DON'T want to use the option to "remove personal information from
file prop's on save". When you enable this option, exiting the program takes
a very long time...just an fyi

['69 Camaro]

Hi, Andy.

I've looked everywhere under the Access options in 2007, and I can't find
the following two:

"Require Variable Declaration" is checked, "Compile on Demand" is not
checked..

Did 2007 do away with these settings?

Nah. They're in the VB Editor's "Options" side of the house. Press
<ALT><F11to open the VB Editor. Select the Tools -Options... menu. See
the "Editor" and "General" tabs for those two options. (I'm going from
memory here, from nearly 1 1/2 years ago when I last used Access 2007 Beta
2, so please speak up if I'm wrong.)

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/ex...ributors2.html for contact
info.

[ARC]

They're there, many thanks!

Any theory on why not to check teh "Compile on Demand" option?

Thanks, Gunny,

Andy

['69 Camaro]

Hi, Andy.

Any theory on why not to check teh "Compile on Demand" option?

It avoids corruption of the VBA source code.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/ex...ributors2.html for contact
info.