Maximum records in a table(access 2007)

[MarkGardner]

I have a series of files that I am converting from dBase and I would like to merge them into a single table.

They are history(for 2008), hist2007, hist2006, etc. back to and includiing hist2000.

Each file contains about 1200+ records & 12 fields of varying lenths.

The immediate goal is to import and append and merge them into a single table named history. I can append without problem, but...

The ultimate goal is to increment at the end of each year the current year data of about 1200 records into history and thus grow astronomically each year into the future. I would rather not have to add table names outside of the forms/menus that I have. This could go on well into the future.

I am trying to discover ahead of time what kind of an easter egg/time bomb that I am setting myself up for in the future.

The questions are:
1) What is max size for a table?
2) What problems occur when I hit the max?
3) Am I just blocked from adding or does ACCESS drop the old data?

Thanks.

Mark Gardner

 

[pbaldy]

You can look in Help for "specifications" to find the size limit for your version, but I think it's 2Gb overall. At 1200/year, your grandchildren will still be using the database and it still won't be anywhere near full.

 

[MarkGardner]

Thanks.
Mark

 

[aroth]

If I may ask a related question:

I want to build a table that will ultimately contain over 1M records, each with about 30 columns. I was planning on splitting the database to a front-end/back-end, but would a table that large be too much for Access to handle efficiently?

At what point does Access start to labor over simple tasks?

 

[DevastatioN]

If that 1M records is over a long period of time, I would recommend splitting older records off into a separate table/backend for archiving.

And if someone needs to see all of the records you can use UNION queries to combine them together again.

 

[aroth]

Thanks.

The 1M records is actually a stacked table of multiple clients' data for which I would run a select query to pull the client data I'm looking for. If it would run smoother to have each client have its own data table then what would be the easiest (automated) way to retrieve this data from a front-end/back-end system?

 

[DevastatioN]

I am not completely sure, I believe it depends on your structure. But I don't believe each client should have their own data table, otherwise you'd have 1M tables. (Since to have 1M records in a truly normalized DB, there would technically be 1M "users" if I understand your request correctly).

If you mean that you have 1000 records in a parent table, and each of them has 1000 records in an attached one-to-many relationship, I don't think you would have an issue.

 

[aroth]

My structure would be more like the second one, but instead of 1000 records in a parent table, it would be closer to 30,000. I plan on having 40 parent table (client tables) with on average 30,000 records. Each will grow on a monthly basis. Is this still going to make the back-end too heavy?

 

[DevastatioN]

I have no experience with working with unnormalized databases except that they are usually a complete hassle.

With that many records you might be better off using a higher end back-end database system than Access, which can handle that many records. And using an access frontend.

Maybe someone else here with more experience in larger volume databases can provide more assistance.

 

[aroth]

Unfortuantely I'm stuck with Access for the time being. You answered my question, though, so I'll have to be a bit creative to make this work.

Thank you DevastatioN, for all your help.