Is my database getting too large? (1 Viewer)

[flemmo]

Hi all,

I have a 3 year old Access 97 database that contains 16 tables, the largest of which (purchase order details) contains 13000 records. The file comes to 36MB.

given that the file is 36MB after 3 years, and one table is already 13000 records long, is there anything I need to do to safeguard for the future?

I'm toying with the idea of somehow splitting the data into financial years.
Perhaps this could be done by appending the year to each table i.e. instead of having the table 'PurchaseOrderDetails' I would have:
PurchaseOrderDetails_2010
PurchaseOrderDetails_2009
PurchaseOrderDetails_2008

Starting the database would load the current financial years tables. Then on the main menu form, I could have a financial year switch to load previous year's data if needed.

What do you think? Or maybe Access will hold enough records for it never to be a problem?

 

[boblarson]

36 MB and Access can hold 2 GB? I think your database is very, very small.

At that rate over 3 years, you will reach the limit in 163 more years.

 

[pbaldy]

I don't think you're even close to having to worry. Are you experiencing some sort of problem, or just being proactive? I've heard of Access db's with millions of records that still perform fine. I would certainly not denormalize the structure like that.

 

[flemmo]

As the database has grown in the 3 years, it has slowed down at certain points.
The main reason for this is the search pages automatically showed all records when unfiltered. I changed this to records in the last year. To see older records, users have to enter 'from' and 'to' dates.

The main reason for my concern is this:
http://database.ittoolbox.com/group...umber-of-records-that-a-table-can-hold-206151

If a table can only hold 65,536 rows, it will only be a couple of years before my purchase order details table (currently on 13000) reaches this limit.

 

[boblarson]

As the database has grown in the 3 years, it has slowed down at certain points.

You might not have it designed properly with correct indexing.

The main reason for my concern is this:
http://database.ittoolbox.com/group...umber-of-records-that-a-table-can-hold-206151

Umm, that is erroneous data. In Access 2000 you can have up to 1 Gb of data in a single table (2 Gb total for the database).

If a table can only hold 65,536 rows, it will only be a couple of years before my purchase order details table (currently on 13000) reaches this limit.

Which version of Access are you using? And again, this 65,536 rows is erroneous data. That 65K is the number of characters that a memo field can accept without using code.

 

[flemmo]

Phew that's good to know. I thought 65k was a bit strange.

The slowdowns I was experiencing have gone since limiting the records returned automatically when the search form is loaded.
Basically the list was thousands of records long, whereas in reality I only need a couple of hundred max. For older records I can search using date filters or my using purchase order numbers, customer names, description etc.

Thanks for the replies

 

[ghudson]

If a table can only hold 65,536 rows

Excel 2003 and older has the limitation of 65,536 rows.

 

[flemmo]

Excel 2003 and older has the limitation of 65,536 rows.

Does this apply to just Excel 2003 and older?
Perhaps the poster on the link I provided meant Excel rather than Access?

I think my client is using Office 2000. The database is Access 97.

 

[ghudson]

Do you mean Access 2003 and older?

I think my client is using Office 2000.

No, Excel 2003 and older has the row limitation of 65K.

Access tables do not have a total number of record limitation. Access tables instead have a limitation in the total size of the table. Access 2000 tables are limited to a total size of 1 gigabyte as previously mentioned.

 

[flemmo]

Thanks for that. This is good news as it means I can carry on with my current database without any headaches