CODEWITHSUNDEEP
sqlexcelvbams-accessinsert
Ben G
Ben G

Reputation: 105

How do I transfer multiple lines of excel data into an access table

Right now I'm looping through each row in my Excel spreadhseet and using an insert query to insert each row into my Access table. It works, but it's slow. How do I make one query to add all the records at once?

This is my current code

Sub Insert()

Dim rs As ADODB.Recordset, strSQL As String, minPrem As Double, i As Long, datetime As Date

datetime = Now()

Call DB.ConnectToDB 'Connect to the database
Set rs = New ADODB.Recordset

i = 7 'first row of data

Do While (Cells(i, 1) <> "")

    If (IsNumeric(Cells(i, 6))) Then
        minPrem = Cells(i, 6)
    Else
        minPrem = 0
    End If

    strSQL = "INSERT INTO Rates (EffectiveDate, State, Company, ClassCode, Rate, MinPremium, TimeOfEntry) VALUES " _
    & "(#" & Cells(i, 1) & "#,'" & Cells(i, 2) & "','" & Cells(i, 3) & "','" & Cells(i, 4).Text & "'," & Cells(i, 5) & "," & minPrem & ", #" & datetime & "#)"
    rs.Open strSQL, Cn

    i = i + 1
Loop

End Sub

Upvotes: 2

Views: 1401

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123739

Wrapping multiple INSERT operations in a Transaction can often speed things up because the individual INSERTs are cached and then written (committed) to the database all at once. Try adding a cn.BeginTrans statement before entering your loop and then do a cn.CommitTrans after exiting the loop, and see if that helps.

Also, if you are just doing INSERT operations then you don't need to mess with a separate Recordset object; you can just do cn.Execute strSQL.

Upvotes: 1

Related Questions