Object Memory

[nathan a]

Which object takes up more memory form, report, or table. I have a database
that is 2GB which I am looking to split. I am wondering what is the most
efficient, in terms of memory, to break it into at least 2 other DBs. Any
help would be appreciated.

 

[Rick B]

Well, when you split a database, you put all the data (tables) in the
back-end database, and all the other objects (forms, reports, and queries)
in another front-end database.

Each user gets a copy of the front-end and it is stored on their local
machine. The back-end lives on a server or shared drive. The front-end
databases link to the back-end.

You may also try compacting your database. To hit the 2GB limit, you either
have tons of data or your file needs to be compacted.

You might also make sure your table structure is normalized. Verify that
you don't have any duplicate data in your tables that could be handled
better with related tables. If you need help with that part, you would have
to share your table structure with us and give us an example or two.

hope that helps,

 

[Jerry Whittle]

Memory or do you really mean disk space? They are not the same thing. Access
has a file size limit of 2GB of disk space.

Unless you are putting some extremely big graphics on your forms and
reports, tables take up most of the space. Graphics stored in tables are a
BIG waster of disk space.

Right behind tables are indexes. In fact if someone has gone nuts with
indexing, it's possible for the indexes to be larger than the table!

When splitting you should put all the tables in one .mdb file and everything
else in the other. The are some rare exceptions like putting static lookup
tables in the Front End (FE) and occasionally some code in the Back End (BE).
However 99% of the time just tables should go in the BE and everything else
in the FE.

If the problem is too large of an mdb file, splitting probably isn't the
answer or at least the best answer. Consider:

1. Compacting and repairing the database.
2. Storing any graphics, whether now in tables or embedded in forms/reports,
external to the database.
3. Purging unneeded indexes.
4. Purging unneeded data (my least favorite option).
5. Upgrading to something like SQL Server that can handle much more data.
6. Compiling your code the doing another compact and repair.

Without a doubt your database should be split for many, many good reasons;
however, I don't think it will fix your file size problem for long.

Before doing anything, make a good backup of your database and tuck it
safely away.