Hi Roger,
If you are developing with the Access 2002-2003 file format, you may want to
convert to the Access 2000 file format, unless you have functionality that is
only supported in the later file format.
Try decompiling your VBA code and then perform a compact and repair. You can
use the following *if* you only have one version of Access installed on your
PC: Click on Start > Run, and enter the following command:
msaccess /decompile
The next database you open will have it's code decompiled. If you have
multiple versions of Access, then I recommend creating a shortcut for each
version that includes the undocumented /decompile switch. To read more about
this option, check out Michael Kaplan's page:
http://www.trigeminal.com/usenet/usenet004.asp?1033
Sometimes creating a brand new database will help reduce the size. Here is
my boilerplate advice on the subject for correcting minor corruption; the
same technique often times reduces the file size:
Create a brand new database and immediately disable the NameAutocorrupt
feature (see:
http://allenbrowne.com/bug-03.html for reasons why you want to
do this). Then import all objects from the suspect database into the new
database, one group at a time. In other words, import all tables (but not
linked tables), then import all queries, then all forms, etc. While Access
will allow you to import all objects in one operation, the experts at FMS,
Inc. (a Microsoft Partner), have stated that it is best to import objects one
group at a time (Reference:
http://www.fmsinc.com/ubb/Forum12/HTML/000285.html).
Recreate any linked tables from scratch. Access can cache a lot of
information about linked tables, which may no longer be valid, so it's always
best to recreate the linked tables from scratch. When importing local tables,
make sure to check the option to import relationships, menus and toolbars,
and import/export specs. If any of the local tables in the source DB are
hidden, you'll need to first unhide them. You will need to set the checked
references to match the source database, along with any startup options set
under Tools > Startup. Going through this process often times solves
corruption problems, because you get a new set of the hidden system tables
(the tables whose names start with "MSYS"). These system tables are updated
appropriately as you import objects.
This may sound like a lot of work, but it really isn't. Creating a new
container DB, disabling NameAutocorrect, importing all objects one group at a
time, re-establishing any linked tables, setting startup options, and setting
references to match the source DB is usually a fairly quick procedure. When
you are in the Visual Basic Editor, in order to check that the references
match the source DB, you should do a Debug > Compile ProjectName as well.
If none of the above helps, then your database is likely about as small as
it will be with the current set of objects and data.
Tom Wickerath
Microsoft Access MVP
http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________