4 all Access Guru's - table size

[MaBell]

hi Guys,

I have a size / performance question. I have a FE / BE Access 2000 database
with one main table. I want this table to hold about 200,000 records a month
X 12 months or 2 million plus records a year. I currently have only 100K
recs loaded and I see a slowdown in the performance on queries, etc. Is
Access able ot hold my ideal amount of data or should I upsize to another
database?

All thoughts would be greatly appreciated.

 

[Guest]

hi,
number of records is not a problem.
in access help type specifications
1 gig is suppose to be access limit but i am dealing with
a data base that is pushing 2.4 gigs. we have table (30 to
40) and many have 1.5 to 1.8 million records each.
when was the last time you compacted your data base?

 

[Jeff Boyce]

Thoughts...

A record could be 2 fields wide (keyfield, valuefield) or 200+ (which would
almost certainly be poorly normalized or not at all). Is your data
well-normalized?

Indexing is a two-edged tool -- it significantly speeds up queries, if
appropriately applied, but it can slow down data entry (since the indexes
need to get rebuilt). Are you loading "in bulk", or do you have 100's of
monk... er, users all doing data entry?

Access 2000 has a ?2 Gbyte limit (I may have missed the version by one here,
either 1 or 2 Gbyte). If your data in each row is small (see first point),
say, 100 bytes, each year would hold 10-20% of your total capacity. How
long (many years?) do you plan to use this?

How many folks will be "mining" the data simultaneously? How many will be
doing (simultaneous) data entry?

I guess if it were me, I'd probably look to a db with greater capacity --
SQL Server comes to mind.

 

[MaBell]

Thanks for replying. I found out it's probably the server my BE is residing
on. It isn't a development server, it's slow and I am not allocated enought
space. Hopefully things will speed up when i get my own server. I am pretty
sure my table (only 15 columns) is index smartly and is only around 10MB.

 

[Jeff Boyce]

If you have the 100K records you mentioned earlier, and your db is 10 Mb,
that's pretty close to 100 bytes per record. You still have a potential
issue for growth.

Access is a "file-server" application when it uses the JET db engine as the
back-end. You could also consider creating a "client-server" application by
using SQL-Server (or others) as the back-end.

Regards

Jeff Boyce
<Access MVP>