Access Size limitations

[WilliamB]

Greetings, new to the forum. I need help and or suggestions. I sell mailing lists, property records mainly. I use Access 2007. When I acquire a new list from a different source, I format it into a record length of approx. 150 characters long. Some of the different lists may have 200,000 records and some may have as many as 2 million records. I would like to put all records in one table, run my queries from that single table. With past experience in attempting to put all records into one table, Access starts to have problems with around 4 million records, slows down, crashes. I have read that access has a size limit on the size of a single table, maybe I'm approaching that. I would like to put all of my records into one table which would amount to around 15-20 million records. I have 2 questions:



1 Can Access be modified to accommodate what I'm looking for or maybe the newer version of access able to handle my needs?
2 Is there another DBMS that I could switch to accomplish what I want?

PS, I'm not very literate in SQL. I know just enough to get my job done. Querys, import, export, very simple tasks, and access has served me well except for this one issue of making several tables to accomplish my goals. I would appreciate all input, realize that you are talking to a senior citizen with limited technical experience. Thanks in advance, Bill

[JoeM]

Access has a size limit on the database as a whole, specifically 2 GB. It should be pretty easy to tell if you are approaching that by looking at the size of your database.
You can also help keep the size of your database down by Compacting and Repairing regularly, especially if you are deleting records.

While the number of records will certainly affect performance, so will other things, such as whether or not your database is normalized (designed properly), and indexed properly.
If you only have one table, there is a good chance you may have a design issue. Very few well designed databases only have one data.

One indicator of a database that is not normalized is if you have a lot of blank fields, or records with repeating data.
For example, let's say you had a database of client invoices, where they are typically invoiced once a month. If you have one table that contains client name, address information, invoice date, and invoice amount, it probably is not normalized (as you may have many records for one particular client but the same address information for each of those records). A proper design would we one table for address information (which has one record per client), and then one table for invoice information.

Note that no matter how well designed your database is, Access has its limitations. And once you start getting into multi-million records tables, performance will suffer. However, many times, a hybrid approach will work. You can store that data in tables in more powerful database programs SQL, Oracle, or MySQL, and continue to use Access as the front-end database for what you are trying to do.

[June7]

Access file size is limited to 2GB, cannot be altered.

Look at SQLExpress and MySQL. These are freebies that I think have a 4GB limit. Otherwise I think you have to consider buying something like Oracle or SQLServer.

[WilliamB]

Thank You for your replies. If I'd been up to speed I would have learned to use Dbase 20 years ago as I've heard it had no upward limit. I don't need most of the features that most newer DBMS have, just the nuts and bolts of a data base will work fine. I'll look into SQLExpress, Thank You

[ssanfu]

I believe that starting with SQLExpress 2008 R2, the size limit is now 10GB...