4

I was wondering about it. I have to put more than 1,200,000 records into a access database for backing up. thanks for answering me.

Tony Toews
  • 7,850
  • 1
  • 22
  • 27
Tim Li
  • 215
  • 1
  • 2
  • 8
  • not an expert, but idoubt theres alimit. it only affects performance. – AbiusX Mar 15 '11 at 01:43
  • Hopefully, you will provide the answer. – JeffO Mar 16 '11 at 15:24
  • possible duplicate of [Maximum number of rows in an MS Access database engine table?](http://stackoverflow.com/questions/1221435/maximum-number-of-rows-in-an-ms-access-database-engine-table) – HansUp Dec 26 '12 at 19:34

5 Answers5

7

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 Toews
  • 7,850
  • 1
  • 22
  • 27
3

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.

mwolfe02
  • 23,787
  • 9
  • 91
  • 161
  • 3
    I've had production apps that ran very well, with 350K records in the main parent table and 450K in one child table and over 600K in another child table (the last one was a very narrow table and seldom used). A few months back I was testing how many records you could create in an Access database with nothing more than four fields of type byte and a compound PK on them. I stopped testing when it reached over 7 million records and 184MBs. It was a useless data table, as it was nothing but a 4-column PK of random byte values, but that's all I was testing, i.e., how many records were possible. – David-W-Fenton Mar 15 '11 at 19:58
  • 2
    Nice to read posts like these after trudging through so much Access bashing garbage. – JeffO Mar 16 '11 at 15:22
  • Jeff O, David and I like seenig those postings and commenting accordingly. Hehehe – Tony Toews Mar 16 '11 at 21:15
  • Jeff O, be sure to vote accordingly, and downvote the Access bigots. And please gently correct the misinformed (since I'm not gentle). – David-W-Fenton Mar 17 '11 at 03:18
  • I think the last sentence is most salient (i.e. even though there have been no problems, why are they still contemplating ditching Access? After all, if you spin the chamber after every turn, even a game of Russian roulette can last for over three years ;) – onedaywhen Mar 22 '11 at 11:53
  • @David-W-Fenton: next time you feel the need to "downvote an Access bigot", consider whether the question should be closed as subjective and argumentative; if being Access is the very reason for it being argumentative, consider whether the question should be moved to a sister site e.g. SuperUser. – onedaywhen Mar 22 '11 at 11:56
  • While I have the privileges of voting for closure, I don't use them often. I also don't flag things often because I'd rather things get worked out in open discussion, rather than be closed off/deleted. – David-W-Fenton Mar 23 '11 at 21:31
0

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.

Eric
  • 17
  • 1
0

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.

Aaron Kempf
  • 580
  • 2
  • 11
  • I would actually agree that with millions of records Jet/ACE is not the right data store. Indeed, in the case of the app I described with 100s of thousands of records I was recommending upsizing to SQL Server c. 2002 or so, but they weren't convinced and didn't upsize until 2007. Why? Because the damned thing performed just fine and was completely reliable! I felt uncomfortable with that much data and really wanted to upsize earlier, but they just couldn't see the point. A server migration eventually offered the opportunity. – David-W-Fenton Mar 17 '11 at 03:20
-4

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...

satheesh kumar
  • 139
  • 1
  • 2
  • 8