Creating really BIG DB

Hi,
I have to create really big database using Visual Basic. The DB should contain about 2 million records - aproximatelly 10 million entries. If any one has any experiance with such a big db, please give me any useful advise how to divide db, how to access it... There are two ways of db design.
1) one big recordset containing about two million records
2)600 tables, each containing 2700 records.
Which is better?
The way of accessing db:
when created according to 2) - all tables must be searched.

Project background:
Ocean temperatures for last fifty years are to be converted from an ASCII file to Access db. Temperatures were measured every month on 2700 places around the globe.

Also I am interested in every information concerning loading data from BIG ascii files. I work in VB 6.0, and the file size is from 50MB to 90 MB.

Thanks in advance
Thomas Kliegr
[email protected]

[JimmyT]

I recently completed a project that is somewhat similar to what you are describing. I have found that if you will be limiting your queries to relatively small time frames (such as a span of a few months or years) it is better to divide the database into numerous tables segregated by time. This will reduce your search time dramatically.

If, however, you need to search the entire database routinely, you will have better results if all of the data is contained in a single table. The overhead involved for VB to create and destroy references to individual tables will become dominant in this case.

Bottom line, you will need to determine how this data will be used, ultimately, and tailor the number of tables accordingly.

Good Luck...

[Nick A.]

Perhaps doing a research for solutions other than Access (SQL Server, Oracle, etc...) might help more.

[Gary Grant]

Access? 10 Million Entries? If it has to be Access keep in mind that a database of one Gigabyte is the largest allowed (Yes, I have created larger but the largest supported is 1GB and there are corruption/performance issues). Actually any Access database will begin to have performance problems once it reaches about 400 Megabytes. If you must use Access you may have to break your data into separate tables each in a separate mdb file with a controlling Access database that links to the mdb's containting the data.
Hope this helps.