Access with a very big table - will it take it

[AlisonEmmett]

I'm planning to create an access database with a table in it that may have 20 million lines in it. Does anyone know if Access will be able to deal with it, or will I need to use another DB ?? [sig][/sig]

 

[MichaelRed]

The question is more like how long are willing to wait for a result?

I have not even come close to this volume of data, and still had really poor response for even modest work loads. My example is the update of ~ 1 million records from a second set of records (also ~ 1 million records). On a 200 MHz system with ~ 5 Gig of local space, this typically required several hours. The primary report from the updated database was ~ 5000 (Yes THOUSAND) pages and took the system (w/ Lj 5 Printer) most of the day.

Even getting to the best processor available, 20 M records is going to take F O R E V E R to produce any real results. Going downhill from here is easy. NO network will be as fast as the average local hard disk, which was probably the real throttle for my modest million record table.

In my opinion you should be looking for somewhere less stressful to apply your talent.

[sig]<p>MichaelRed<br><a href=mailto:mred@duvallgroup.com>mred@duvallgroup.com</a><br>There is never time to do it right but there is always time to do it over[/sig]

 

[Waynest]

Be aware that access has a max database size of 2GB - how many rows this equates to depends entirely on your design, but common sense tells you 20 million is a mighty big number.

You could think about using access(2000) as a front end to SQL Server 7.0 which is very nice to use and handles terrabytes of data. [sig][/sig]