Append data to Access Table with ADO using Excel VBA

Question

I am trying to append Excel data to an Access table using Excel VBA.

I have data in a worksheet in Excel (active worksheet). I would like to append the contents of the row A4-BL4 to a Table in Access called Table1.

I am trying to do this with ADO and a recordset.

My code gives me an error

Arguments of the wrong type, are out of acceptable range, or are in conflict with one another

on the line:

 rs.Open qry, cn, adOpenKeyset

I am new to working with Access.

It is crucial this be triggered from Excel not Access

Sub EXPORT()

    Application.ScreenUpdating = False

    Dim cn As ADODB.Connection
    Dim rs As New ADODB.Recordset

    Set cn = New ADODB.Connection

    strcon = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
    "Data Source=C:\Users\yilmadu001\Desktop\Database.accdb;"
 
    cn.Open (strcon)

    qry = "SELECT * FROM Table1"
    rs.Open qry, cn, adOpenKeyset

    rs.AddNew
    rs.Fields("Date") = Range("A4").Value
    rs.Fields("Order Number") = Range("B4").Value
    rs.Fields("Product Code") = Range("C4").Value
    rs.Fields("E-Number") = Range("D4").Value
    rs.Fields("size") = Range("E4").Value
    rs.Fields("type") = Range("F4").Value
    rs.Fields("type2") = Range("G4").Value

    rs.Fields("Voltage") = Range("H4").Value
    rs.Fields("Compound") = Range("I4").Value
    rs.Fields("Number") = Range("J4").Value
    rs.Fields("Tag Number") = Range("K4").Value
    rs.Fields("Quantity") = Range("L4").Value
    rs.Fields("zero") = Range("M4").Value
    rs.Fields("net") = Range("N4").Value

    rs.Fields("Actual net") = Range("O4").Value
    rs.Fields("min") = Range("P4").Value
    rs.Fields("Actual min") = Range("Q4").Value
    rs.Fields("max1") = Range("R4").Value
    rs.Fields("max2") = Range("S4").Value
    rs.Fields("Top") = Range("T4").Value
    rs.Fields("2") = Range("U4").Value

    rs.Fields("side") = Range("V4").Value
    rs.Fields("5") = Range("W4").Value
    rs.Fields("Bottom") = Range("X4").Value
    rs.Fields("8") = Range("Y4").Value
    rs.Fields("side2") = Range("Z4").Value
    rs.Fields("10") = Range("AA4").Value
    rs.Fields("set1") = Range("AB4").Value

    rs.Fields("set2") = Range("AC4").Value
    rs.Fields("set3") = Range("AD4").Value
    rs.Fields("Usage1") = Range("AE4").Value
    rs.Fields("Usage2") = Range("AF4").Value
    rs.Fields("Main (%)") = Range("AG4").Value
    rs.Fields("A (%)") = Range("AH4").Value
    rs.Fields("ratio (%)") = Range("AI4").Value

    rs.Fields("set3") = Range("AJ4").Value
    rs.Fields("set4") = Range("AK4").Value
    rs.Fields("Core ") = Range("AL4").Value
    rs.Fields("Tip1") = Range("AM4").Value
    rs.Fields("Tip2") = Range("AN4").Value
    rs.Fields("Tip3") = Range("AO4").Value
    rs.Fields("set5") = Range("AP4").Value

    rs.Fields("speed") = Range("AQ4").Value
    rs.Fields("speed2") = Range("AR4").Value
    rs.Fields("speed3") = Range("AS4").Value
    rs.Fields("speed4") = Range("AT4").Value
    rs.Fields("speed5") = Range("AU4").Value
    rs.Fields("speed6") = Range("AV4").Value
    rs.Fields("speed7") = Range("AW4").Value

    rs.Fields("setting") = Range("AX4").Value
    rs.Fields("Selected") = Range("AY4").Value
    rs.Fields("S/U1") = Range("AZ4").Value
    rs.Fields("S/U2") = Range("BA4").Value
    rs.Fields("S/U3") = Range("BB4").Value

    rs.Fields("ON/OFF") = Range("BC4").Value
    rs.Fields("YES/NO") = Range("BD4").Value
    rs.Fields("NOTES") = Range("BE4").Value
    rs.Fields("Size1") = Range("BF4").Value
    rs.Fields("Size2") = Range("BG4").Value
    rs.Fields("Size3") = Range("BH4").Value
    rs.Fields("Temp1") = Range("BI4").Value

    rs.Fields("Temp2") = Range("BJ4").Value
    rs.Fields("Temp3") = Range("BK4").Value
    rs.Fields("Temp4") = Range("BL4").Value

    rs.Update

    rs.Close
    db.Close

    Application.ScreenUpdating = True

End Sub

@HansUp now I am getting a new error on the line "rs.AddNew" and it says current recordset does not support updating. This may be a limitation of the provider, or of the selected locktype. — DYilm
@HansUp none of those worked unfortunately, still error "current recordset does not support updating. This may be a limitation of the provider, or of the selected locktype" — DYilm
@HansUp that seems to have fixed it, but now it is giving me an error "Invalid SQL statement expected "Delete", "Insert", "Procedure", "Select", or "Update" " on the same line, I think I am missing another argument — DYilm
See Ibo's answer. Sorry I don't use ADO; this is easier with DAO. — HansUp

Ibo Ibo · Accepted Answer · 2018-08-08T16:45:21

You need to specify your recordset to be update-able:

rs.Open qry, cn, adOpenKeyset, adLockOptimistic

since your query is simply getting all of the records you can use adCmdTable option, but you have to pass the name of the table only:

rs.Open "Table1", cn, adOpenKeyset, adLockOptimistic, adCmdTable

Append data to Access Table with ADO using Excel VBA

1 Answers