Results 1 to 11 of 11
  1. #1
    Join Date
    Jul 2002
    Location
    Ukraine, Kiev
    Posts
    5

    Unanswered: How to insrt large data into Access using ADO

    I need to insert ~ 10.000 records into Access table. AddNew() works too slowly. Can i use bulk insert? or linked table?

    Thank.

  2. #2
    Join Date
    May 2002
    Location
    Atlanta, GA
    Posts
    117
    Hey DmitryK,

    I will agree with you that ADO can be quite slow for large inserts or updates when using AddNew or Update. Try using straight SQL instead, I find it works much faster.

    Here is a crude example to try:

    Dim cnn As ADODB.Connection
    Dim cmd As ADODB.Command
    Set cnn = CurrentProject.Connection
    Set cmd = New ADODB.Command
    Set cmd.ActiveConnection = cnn
    cmd.CommandType = adCmdText
    cmd.CommandText = "INSERT INTO sometable SELECT * FROM sometable;"
    cmd.Execute

    Let me know if this helps,

    Kal

  3. #3
    Join Date
    Jul 2002
    Location
    Ukraine, Kiev
    Posts
    5
    Hi!

    Thank you, KPalmer. I know that way, but i need to insert data from file, not from another table. In Oracle (or MS SQL or Sybase etc) i can use bulk insert routinies, Access haven't got bulk insert. That is my problem.

    Thank you!

  4. #4
    Join Date
    Apr 2002
    Location
    Germany
    Posts
    228
    try the text data import wizard in Access

  5. #5
    Join Date
    Jul 2002
    Location
    Ukraine, Kiev
    Posts
    5
    Hi!

    I can't use Access'es UI, i need to use ADO/ADOX only.
    Thank you!

  6. #6
    Join Date
    Jul 2002
    Location
    Australia
    Posts
    147
    FSO from VBA?

    Cheers,
    Andrew
    There have been many posts made throughout the world.
    This was one of them.

  7. #7
    Join Date
    Apr 2002
    Location
    Germany
    Posts
    228
    Is it possible to link the file via the text file odbc driver? You could do mass inserts from there then. I don't know of any way to do bulk inserts on an Access data source via ADO.

  8. #8
    Join Date
    May 2002
    Location
    Atlanta, GA
    Posts
    117
    Hey DmitryK, again,

    Actually, you can still achieve this, if I understand you.

    What kind of file are we talking? Text, etc... Also, are we talking multiple files or just one? It is my understanding that you are having success with ADO but its to slow, right? There are other options to try.

    Kal

  9. #9
    Join Date
    Jul 2002
    Location
    Ukraine, Kiev
    Posts
    5
    Hi!

    I decided my problem: MSDN, "HOWTO: Open Delimited Text Files Using the Jet Provider's Text IIsam".

    Thank you.

  10. #10
    Join Date
    Jul 2002
    Location
    Ukraine, Kiev
    Posts
    5
    Hi!

    I decided my problem: MSDN, "HOWTO: Open Delimited Text Files Using the Jet Provider's Text IIsam".

    Thank you.

  11. #11
    Join Date
    Nov 2001
    Posts
    336
    Hi Dmitry,

    You possibly missused word "decide". I assume you mean "solve".

    Anyway, using FSO and VBA code is a nice way to solve the problem. If you want to improve perfomance then drop all indices on the table before doing bulk insert and re-create them when its done.

    Igor

Posting Permissions

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