Too mnay records?

[Tony Williams]

I have created a database by importing an excel spreadsheet. There are
227,000 records of items and which are in one of the tables. This represents
4 years data. There are about a dozen other tables but they are practically
empty. The main table with the high number of records has 21 fields. Each
field is specific to that item and I am confident that the data base is
normalised, I'm not duplicating fields in any of the tables. However the
database file size is 1.04GB and I'm concerned that I'll quickly reach the
limit of 2GB. I know nothing about sql and neither does the end user so I
need to find a way to manage the database in Access. Can anyone give me any
suggestions as to how I could reduce the file size? There are no images in
the tables.
Thanks
Tony

 

[Allen Browne]

It seems you are using about 4400 bytes per record, which is quite a lot.
There must be some BLOB fields here (memo, OLE Object, Attachment, etc.)
Pesumably you have already used compact/repair.

For Text, Memo, and Hyperlink fields, see if Unicode Compression helps.

For Number fields, use the smallest size practical, e.g. Integer rather than
Long, Long rather than Double.

You might also consider removing any indexes that are not needed.

If you do have lots of memo fields in this table, a workaround might be to
create a related table in another MDB, and put the memo fields in that file.
Attach that table. Now you might (ideally) have 2 files each of 0.5GB
instead of a single 1GB file. (You lose relational integrity when the data
is spread across multiple files, so you are creating extra work for yourself
in manually maintaining them.)

You say there's no pictures in the tables; graphics on forms/reports can
bloat the file too.

A compacted file of 1GB is not something to worray about yet, so another
possibility is retaining only the last 5 years (say) of data in this file,
and archiving the others off into a seprate MDB.

Hope something there is of use.

 

[Tony Williams]

Thanks Allen I'll explore all of those possibilities. I just don't want to
get 12 months down the line and find we've reached the limit.
Thanks again
Tony

 

[Albert D. Kallal]

Have you done a compact and repair on the database?

 

[a a r o n _ k e m p f]

SQL Server is much better at optimizing for size issues.

You've obviously gone past what Access can handle.
Upsize to Client Server architecture (Access Data Project)

 

[Tony Williams]

Hi Albert yes I did that and it has reduced it now exploring some of the
possibilities from Allen
Cheers
Tony

 

[GenlAccess]

You've obviously gone past what Access
can handle.

"Ho, ho, ho!" is just seasonal, numbbrain, not to encourage you to play dumb
to make us laugh.

Upsize to Client Server architecture
(Access Data Project)

Obsolescent; ain't goin' nowhere, just like ol' aaron. Looks hell-bound to
get hisself laughed out of this newsgroup, too, just like he did in the SQL
Server groups. Too bad he can't link to verify that he's really "certified",
not just "certifiable".

Genl Access

 

[Harshad Khandare]

http://www.marathimati.com

"Ho, ho, ho!" is just seasonal, numbbrain, not to encourage you to play
dumb
to make us laugh.


Obsolescent; ain't goin' nowhere, just like ol' aaron. Looks hell-bound to
get hisself laughed out of this newsgroup, too, just like he did in the
SQL
Server groups. Too bad he can't link to verify that he's really
"certified",
not just "certifiable".

Genl Access