Importing file into Access DB already at 2GB max? HELP!

[Guest]

PROBLEM (question from my boss)-
"Your database has multiple tables containing the same information that is
in a master table. You can not delete your individual tables or the master
table but, your database has reached the size limitation of 2 gigabytes.
Each month you will need to import a file into access and append that file to
your master table (note: the additions of these files each month will make
the size of the database increase). What can you do to still perform your
task but not freeze Access?"

MY QUESTIONS-
Can anyone suggest a solution to the problem above? My boss is challenging
me with this question and I want to understand the best way to resolve the
issue.

My current thoughts may be way off, but I'm thinking the 'Compact and
Repair' utility could possibly be used to reduce the database size a bit
under the 2GB threshold? An 'Append Query' would be the best way to handle
the remainder of the task (importing the monthly file to append master table)?

THANKS for any help or advice you can offer.

 

['69 Camaro]

Hi.

My current thoughts may be way off, but I'm thinking the 'Compact and
Repair' utility could possibly be used to reduce the database size a bit
under the 2GB threshold?

Yes. (Back up the file first, in case something goes wrong.) You should
compact the database to see if you can get rid of any bloat. A temporary
fix is to export one or more tables to another database file, delete the
table in the current database (remember to back it up first), then link to
the exported table in the other database using the original table name, then
compact the database again to recover the now unused disk space within the
file. Queries will (nearly always) work with the linked table as if it were
in the same database file as the original was. The downside is that
referential integrity can only be enforced between tables which reside in
the same database file, so related tables (those containing the foreign
keys) should be stored in the same file as the parent (or master) table.

A long term fix would be to split the database into a front end (queries,
forms, reports, et cetera) and back end (tables and relationships), then
upgrade the back end to one of the free express versions of the major
database vendors, such as Microsoft SQL Server 2005 Express or Oracle 10g
Express. They handle up to 4 GB of data, are more robust, can be much
faster (depending upon your design -- a poor database design can be just as
slow regardless of the database engine), and can be made secure. However,
if your data is expected to exceed 4 GB, then you should upgrade the back
end to a database engine with more data capacity.

While Access can handle up to 2 GB of data, when it reaches about 96% of
that milestone, screwy things can happen (such as error messages for no
reason), because the file is running out of space.

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/expert_contributors2.html for contact
info.

 

[Guest]

THANK YOU! I really appreciate the info. I've always used a split database
in the past, so this is a new situation for me.

 

['69 Camaro]

THANK YOU! I really appreciate the info. I've always used a split

database
in the past, so this is a new situation for me.

You're welcome. Glad to help.

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/expert_contributors2.html for contact
info.

 

['69 Camaro]

A long term fix would be to split the database into a front end (queries,

forms, reports, et cetera) and back end (tables and relationships), then
upgrade the back end to one of the free express versions of the major
database vendors, such as Microsoft SQL Server 2005 Express or Oracle 10g
Express. They handle up to 4 GB of data, are more robust, can be much
faster (depending upon your design -- a poor database design can be just
as slow regardless of the database engine), and can be made secure.

Regarding the free client/server database engines, please see the following
Web page for links to these database engine descriptions to compare their
major features and find additional links to each vendor's download page:

http://www.backends.qbuilt.com/

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/expert_contributors2.html for contact
info.

 

[Åí±ë]

Regarding the free client/server database engines, please see the
following Web page for links to these database engine descriptions to
compare their major features and find additional links to each vendor's
download page:

http://www.backends.qbuilt.com/

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/expert_contributors2.html for contact
info.