Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2007
    Posts
    6

    Red face Unanswered: need to find a fast way to insert data...

    Hi all,

    i need a fast way inserting lots of data into an ms access database, (more than 500000 rows...) im reading the data from a txt file... and ive tried using dataset and datatable but it takes lots of resources from the pc (to insert all the data into the memory...) and the direct approach is very slow (oledbcommand.excutenonquery() for every row ) is there a faster way ??

    btw im using c#,

    thanks very much for the help, im really stuck here...

    Guy.

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Take your pick: Lots of memory or snails pace ... If you use a recordset and use the UpdateBatch to write out the records in a batch that will decrease your time ... Otherwise, a half mil INSERTs, wake me up in twenty years ...
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    doCmd.TransferText

    I assume the text file is either tab or comma delimited.
    Never imported that many records but it has been fairly speedy for me in the past!
    George
    Home | Blog

  4. #4
    Join Date
    Aug 2003
    Location
    Cleveland USA
    Posts
    184
    I do a lot of big text file imports, up to as many as 150,000 records with about 75 fields, and docmd.transfertext works most of the time. Sometimes I get a weird error message when the file is too big, and it has to do with Access not being able to convert nulls to blanks. It's an Access bug, and I have the workaround code that I got from someone on this board in case you need it.

    What I've also had to do sometimes is link to the text file, and then append its data to a table. I've done this in circumstances where the text file has a header row that lists field names on each 'page'. Sometimes Access just won't import these rows (fine with me!) when trying to import, but sometimes the import fails, but then the linking trick works.

  5. #5
    Join Date
    Mar 2007
    Posts
    6
    first off all thanks for the help guys,

    but im using c# and i cant find anything about doCmd.TransferText...
    am i missing something?

    thanks.

  6. #6
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by funtik4444
    first off all thanks for the help guys,

    but im using c# and i cant find anything about doCmd.TransferText...
    am i missing something?

    thanks.
    Yes ... It's an Access command. Not gonna work in C# unless you invoke it thru an Access aplication ...
    Back to Access ... ADO is not the way to go for speed ...

  7. #7
    Join Date
    Mar 2007
    Posts
    6
    Bum :/

    need to find a workaround

    thanks dude,

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Isn't the workaround to run the command from within Access?
    Alternatively (this may or may not be applicable)...
    DTS packages. Quick - simple and can be scheduled or forced to run.
    George
    Home | Blog

  9. #9
    Join Date
    Mar 2007
    Posts
    6
    Quote Originally Posted by georgev
    Isn't the workaround to run the command from within Access?
    Alternatively (this may or may not be applicable)...
    DTS packages. Quick - simple and can be scheduled or forced to run.

    thanks guys

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •