Import Text Specification in Access Database

Question

We are using C#.net & use access database code for import of text file specification into access table

is there any access database limit for this action, as we may have records > 5 lac (500,000) ,will this process work for huge records??

If No then how can we handle huge records insertion in access database for same ?

Thanks

Answer 1

The import process doesn't have any specific limit on the number of records that you can import or store in a table, however it does limit you to a single table size of 1 gigabyte for Access 2000 or 2 gigabytes for later versions.

A huge number of small records is OK, and a small number of huge records is OK. But, a huge number of huge records will probably hit the limit.

P.S. You shouldn't use lac (lakh) on international forums because it is only understood in India and nearby countries. 1 lac = 100,000

Answer 2

Would you consider: Load up your data in c# (StreamReader, etc),

start an oleDbTransaction,

Run an Insert query x 500k times using an oleDbCommand

Commit your transaction.

This will take away your dependency on the Access Import specification too, so might port easier to other db types in the future. The speed should be comparable to the Access Import, but requires you to code up the equivalent of your import specification (ie, 'create table' SQL, 'insert into' SQL).