performance issues when importing large csv file into mdb using ADO

Question

This is my code, but its taking about an hour to export all 17 million rows into mdb. I cannot use mysql or sql server for this purpose. I have to do it in access db and quickly coz this process runs once in a week. Plz suggest the fastest method available for this task

Sub insertDataIntoMDB()
 Dim Dbfilepath As String
 Set cnn = CreateObject("ADODB.Connection")
 Set rs = CreateObject("ADODB.Recordset")
 Set rst = CreateObject("ADODB.Recordset")

        Dim arrData() As String
        Dim s As String
        Dim i As Integer


        Dbfilepath = ThisWorkbook.Path & "\DB\Interface.accdb"
        cnn.Open "Provider= Microsoft.ACE.OLEDB.12.0;" & " Data Source=" & Dbfilepath & ";" & "Persist Security Info =False;"

        q1 = "DELETE * FROM MYTABLE"
        Set rs = cnn.Execute(q1)

        'q1 = "ALTER TABLE MyTable ALTER COLUMN ID autonumber(1,1)"
        'Set rs = cnn.Execute(q1)

        p = UserForm1.csvFolder & "\" & sItem & ".csv"

        Open p For Input As #1
        Do While Not EOF(1)

          Close #1


            Line Input #1, s
            arrData = Split(s, ",")
           q1 = "INSERT INTO MyTable(F1,F2,F3) values(" & arrData(0) & "," & arrData(1) & "," & arrData(2) & ")"
           Set rst = cnn.Execute(q1)
        Loop
        Close #1
        rs.Close
        rs`enter code here`t.Close
        Set rst = Nothing
        cnn.Close
        Set rs = Nothing
        Set cnn = Nothing


End Sub

please tell something more about all tables you work with (MYTABLE) and CSV file, how do they look like? (structure, data), etc. — Kazimierz Jawor, Jan 20 '14 at 17:10
Any reason why you do noy wish to use MS Access? Eg. DoCmd.TransferText ? — user1917229, Jan 20 '14 at 17:50

score 0 · Answer 1 · edited May 23 '17 at 12:28

Right here you have a huge slowdown and potential for data corruption.

q1 = "INSERT INTO MyTable(F1,F2,F3) values(" & arrData(0) & "," & arrData(1) & "," & arrData(2) & ")"
Set rst = cnn.Execute(q1)

String concatenation is slow, especially in VBA. So as soon as you write "something" & "something" and put it in a loop you're asking for slow performance.

Also, Access is usually faster with DAO than with ADO.

Read this answer
And maybe this question and its answers

If you insist on using ADO you might want to open a recordset with a SELECT statement then append your data to that recordset, followed by a call to UpdateBatch.

You can read more discussion here

Good luck!

performance issues when importing large csv file into mdb using ADO

1 Answers1