Large Database

[Charlie Bamford]

Over the years I have successfully designed an Access database to analyse
sales & margin by product & customer for a number of businesses that we own.
Invariably the raw data is an import from the business software that each
company uses; often MAM AutoPart. The system works well, however, the volume
of data is becoming too large. The database is now about 2GB and some tables
have more than 1 million records. I frequently get error messages and have
to compact & repair the database. I feel that I need fresh design ideas and
maybe need to move to a bigger product than Access 2003 which I currently
use. The database is on my laptop because I work a lot at home. I want to
share it with other users but still need to use it at home.
I am an Accountant, not an IT specialist so there are limits to my skills.
I know nothing about SQL or Visual Basic.
My database has been very successful but needs to move to another level.
Help?

 

[Steve]

You have some options to be able to continue to use Access2003:
1. Before you do anything else, try compacting the database. Tools -
Database Utiliyies - Compact and Repair
2. If your database is a single file, you can split the database into a
backend file (just tables) and a front end file (Everything else)
3. Your database needs to be split to do this option. You could divvy up
your tables into several mdb files and link to each from your frontend using
the built in utility in Access Link Table Manager

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[jahoobob via AccessMonster.com]

Just though I'd check #3. I created a new db and linked the table from
another and copied all queries, forms etc. to the new db. I compacted both
and the new db with the linked table is larger than the old one (508 vs 280
kb.) Apparently there is overhead in linking tables. As a matter of fact
the old db got smaller on compacting while the new one grew!

You have some options to be able to continue to use Access2003:
1. Before you do anything else, try compacting the database. Tools -
Database Utiliyies - Compact and Repair
2. If your database is a single file, you can split the database into a
backend file (just tables) and a front end file (Everything else)
3. Your database needs to be split to do this option. You could divvy up
your tables into several mdb files and link to each from your frontend using
the built in utility in Access Link Table Manager

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

Over the years I have successfully designed an Access database to analyse
sales & margin by product & customer for a number of businesses that we

[quoted text clipped - 16 lines]

My database has been very successful but needs to move to another level.
Help?

 

[Chris2]

Over the years I have successfully designed an Access database to analyse
sales & margin by product & customer for a number of businesses that we own.
Invariably the raw data is an import from the business software that each
company uses; often MAM AutoPart. The system works well, however, the volume
of data is becoming too large. The database is now about 2GB and some tables
have more than 1 million records. I frequently get error messages and have
to compact & repair the database. I feel that I need fresh design ideas and
maybe need to move to a bigger product than Access 2003 which I currently
use. The database is on my laptop because I work a lot at home. I want to
share it with other users but still need to use it at home.
I am an Accountant, not an IT specialist so there are limits to my skills.
I know nothing about SQL or Visual Basic.
My database has been very successful but needs to move to another level.
Help?

Charlie Bamford,

FILE SIZE REDUCTION:

Have you turned off Name Autocorrect in all your MS Access databases?

If you haven't, then try:

Create a *new copy* of one your databases, and note the file size.

Open the *new copy*.

Menus: Tools > Options > General Tab

Look at the Name AutoCorrect frame.

Make sure all three options are unchecked and click ok.

Compact the database.

See what size is now, there should be a reduction, especially if its been operating for a
while. (I'm not sure how much of a savings it would be in a db in the 2GB range, but
there should be something.)


DATABASE UPSIZING:

Your most direct upgrade path is to MS SQL Server. There are other choices, especially
MySQL.

Both require more knowledge of SQL, and more importantly, database design and
administration.

Both require that you learn considerable new product-specific information. Both require
separate front-end software for end-users. MS Access can serve as that front end, but so
can Visual Basic, Visual C++, or various other "visual" products. MS Access would be
easiest for you, especially if you pick MS SQL Server.

The question on whether to upgrade then becomes, how much are you willing to expand your
skillset? How much reconstruction of the front-end (even if only for yourself), how much
redevelopment of reports (or will MS Access continue to produce reports)?

If you are will to expand your skillset, you will have many new options available. You
can teach yourself how to do these things, though it will take time and effort and an open
mind.

If you choose not to expand your skillset, your choices are more limited.

You could see if any of your accumulated data could be moved into a separate "archive"
database.



Sincerely,

Chris O.

 

[Steve]

This is very misleading! You are looking at kb and the OP is talking
gigabytes. If you create a couple of new databases and import into each some
tables containing millions of records from an existing database, all your
new databases are going to be significantly smaller than your existing
database.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

Just though I'd check #3. I created a new db and linked the table from
another and copied all queries, forms etc. to the new db. I compacted
both
and the new db with the linked table is larger than the old one (508 vs
280
kb.) Apparently there is overhead in linking tables. As a matter of fact
the old db got smaller on compacting while the new one grew!

You have some options to be able to continue to use Access2003:
1. Before you do anything else, try compacting the database. Tools -
Database Utiliyies - Compact and Repair
2. If your database is a single file, you can split the database into a
backend file (just tables) and a front end file (Everything else)
3. Your database needs to be split to do this option. You could divvy up
your tables into several mdb files and link to each from your frontend
using
the built in utility in Access Link Table Manager

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

Over the years I have successfully designed an Access database to
analyse
sales & margin by product & customer for a number of businesses that we

[quoted text clipped - 16 lines]

My database has been very successful but needs to move to another level.
Help?