11-11-2004, 06:55 AM
I am creating an Access 97 database where a certain table will be populated with around 720 rows per day. This will amount to aproximatly 187200 in one year.
Are there limits on how many rows a table can contain? What are the performance issues on having a table such as this and what strategies can i look into to avoid this.
Thanks for your time,
11-11-2004, 07:07 AM
I believe that there is no row limit. However, this is limited by the size of your database (for Access 97 I believe it's 1GB). I guess you're going to face performance problems, but it all depends from the structure of your code and the schema of your database. For example there are some limitations for the number of records a query can return etc. but to be honest with you I miss the numbers. Maybe doing a search on the net will provide you with the information you need.
11-11-2004, 09:38 AM
The file size (not table size) limit is 2 GB for Access 97.
The problem will be one of performance, as Demon Cleaner indicated. in about a year, your performance will be suffering.
You can help if you add pertinent indexes to your data definition.
IMO, you would be much better off, though, if you just abandon Access for this project, and install MSDE. Although the database size limits are the same, MSDE is a FAR superior database technology than is Access. SInce the database is going to be updated every two minutes or so, on average, and you will need to retrieve data, you are looking at a mutli-user application. When you retrieve data from an Access database with a query from another application, the ENTIRE table(s) gets copied from the database to the client. Every time the query is run. This can lead to a severe performance bottleneck. If you wanted to look at the data from a single hour, you would have to copy the entire years worth of data.
MSDE, on the other hand, is a true database server. It's NOT simply a file. So, it runs as a service, listening for data requests. When it gets a request, it handles it, and returns ONLY the data that you've specified. Plus, it's not subject to file corruption and file bloat, like Acess is. Trust me on this - You will be MUCH happier if you use MSDE for this app. And, it's free. Plus, if the database ever looks like it will outgrow the file size or connection limits of MSDE, you can upgrade to a full-blown copy of SQL server, copy the database file, and your apps will work exactly as before. It's scaleable.