2M records in in Acces 97

[EBee]

Does anyone here worked on Access 97 with as much as 2 Million records.

I just wanted to know how Access handles files of this size. I worked on a project that had as much as 800K - 1 million but not 2 million records. . .

The big question is that, can Access 97 handle it. . Or does Access database have some kind of limit then die. I Just want to know if it is worth using MS Access when it gets this big. . I am sure running a query or report is a pain.

thank you in advance for any comments or suggestions

erwin

 

[ETID]

I've heard, but not experienced, that Access gets painful at about the 1 Gig size....

 

[Guest_imported]

I've worked with an Access database with a table containing over 3 million records. The table was used for historical purposes and reporting. Many of the reports took 30 min+ to produce, but this was allowable in this specific environment.

 

[FMAQ]

I have had an experience with a mdb with a single table with more than 1 million 800 thousand records, it was historical information of one year, i was importing it from monthly dbf file, but i couldn't store all the year in a single mdb, so i used two and then design a union query, that really works.
Indexing that mdb tooks a lot of time, but select querys returning near 2000 records behaves well, if you retrieve 20000 or more records obviously is going to take more time, think about using sum,count with group by!!!!!, it,s not only dependent of access what about hardware and net speed? As mdb are limited to store 2 gb so it,s not strictly a matter of how many records are there stored, maybe it,s time to think about
SQL SERVER, isn,t it??????

 

[EBee]

is there a best way to convert access DB to SQL , i have access to a sql server but don't have that experience using it.

is reading a SQL book would be enoug??

thanks
erwin