access file size limits

[Guest]

Hi,

Two related questions.

1) I have a non-normalized Access database which is just a very large data
table, to which I add about 100,000 rows each month. This db is currently
about 1 gb in size. How large can the db become before it becomes unstable?

2) Would taking the time to normalize this db reduce its size?

 

[Kevin Jameson]

you cant do this with microsoft access 2003

you must upgrade to access 20004 by clicking here:
http://www.ammara.com/access_image_faq/access_loading_image_dialog.html

HTH

Kevin

 

[Graham R Seach]

Dave,

Access 97 and earlier had a 1GB file size limit, whereas Access 2000 onwards
have a 2GB limit. You can get some instability, depending on the transaction
rate, beyond about 1.5-1.8GB. You can overcome this by splitting the
database (code and forms etc in one file, and data in another). You can even
split the database so that you have many files containing different tables.

Normalising will help to reduce file size by eliminating redundant data.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

 

[David Cox]

Have you tried compacting the database? Under Tools - Database

Normalising the database will normally reduce its size because it will
eliminate duplicate data. However this is not always the case, as separate
tables and indexes involve overheads too.

 

[Guest]

Thanks, that's what I thought.

I'm just going to create a new db to store new data.

My needs for the historical data are limited at this point.

Thanks,

Dave

 

[Guest]

Hi,

Yes, I've tried compacting it.

I think the solution is just to create a second database with new data.

Sort of two volumes of the same dataset.