How many records can Microsoft Access 2003 database store?

I was wondering about it. I have to put more than 1,200,000 records into a access database for backing up. thanks for answ开发者_JAVA技巧ering me.

The hard limit is the 2 Gb Access database file size. However as Tim states there may be a performance problem depending on the number of records.

However a hard-to-quantify limit may be the number of pages that Access can create. It may very well be that Access uses one page per record when inserting records. Now given that Access 2000/Jet 4.0 and newer has 4K page sizes there can only be at most 500,000 pages per 2 Gb database. (Roughly due to system overhead.) Now you might be able to get around this problem by ensuring you exclusively lock the Access database file as Access 2000/Jet 4.0 and newer might then insert multiple records on a page.

Added later: Now if you do a compact and repair this will place as many records in one page as possible. If you were to add all the records at once then you might have more problems. But if you cuold put in say 600K, compact and the rest this might get you more room.

However I would suggest looking at other alternatives with that volume of records.

Tony's answer is right on.

For anecdotal purposes, I can tell you I have one ~300 MB .mdb that has a single table currently holding over 2.4 million records. We've had no corruption of the .mdb and it has been in production use for over three years. It sits on a network drive and is updated by about five different people (accessed read-only by maybe ten more).

We have not had any problems, but upsizing the datastore to SQL Server is definitely on the to-do list.

I used to use millions upon millions of rows in Access. I had a dozen tables / databases with a millions rows each, running most of the database work for an accounting department for a retail chain with 50 stores. (we had an AccPac consultant come in once a month for an hour or two).

Access isn't the right solution for that job.

I would recommend looking at SQL Server. I've been using SQL Server fulltime for 11 years now, I highly reccomend you take a look at 'Access Data Projects'.

If you upsize the database, Access calls it a 'Client-Server application'.

Of course, I'm a SQL Server guy, so these guys are going to claim that I'm biased.

MS Access calls MDB files databases, but they are really just tables. Your database is made up of thousands of these MDB files each containing 2 GIG of data. Each MDB file for example could hold the data for 1 of 50 states in the U.S. So you have 100 GIG of storage space in your database for 50 MDB files. Or perhaps you have 50 clients and store each in a separate MDB file, but it is all one database, just stored in separate files by each client. Informix Standard Engine (SE) worked like this year ago before Dynamic Server came out. I like seeing my DB files. I did not like Dynamic Server's vapor DB files held on cloud 9. And I did not like DS storing tables on different partitions. Try running a SQL statement on 2 tables, each on a separate partition, and see how horrible the performance is. SQL Server is probably the same way. That is why I use MS-Access 97 databases for all my Win32 Perl applciations that connect by ODBC and manage all user traffic. My compiled program contains the password, so no one can edit the databases except via the Perl applications.

We can't store Unlimited data into the "MS Access" Database...

"Ms Access 2007" will allow you to store only Up to 2GB file size including tables,Modules,Reports,and etc...

If the Database Reaches more than 2GB Size then it will throw the error while you are writing through codings...