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
– DYilm, Commented Aug 8, 2018 at 16:03
@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
– DYilm, Commented Aug 8, 2018 at 16:17
@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
– DYilm, Commented Aug 8, 2018 at 16:40
See Ibo's answer. Sorry I don't use ADO; this is easier with DAO. — HansUp
– HansUp, Commented Aug 8, 2018 at 16:47

Ibo · Accepted Answer · 2018-08-08 17:20:40Z

2

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

edited Aug 8, 2018 at 17:20

answered Aug 8, 2018 at 16:45

Ibo

4,3197 gold badges51 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

DYilm Over a year ago

Synthax error FROM clause in the line rs.Open qry, cn, adOpenKeyset,adLockOptimistic, adCmdTable

Ibo Over a year ago

try without adCmdTable

Ibo Over a year ago

Do you have Table1 in your ACCESS database? try to close everything because if you are in design mode etc your connection/queries will fail

Erik A Over a year ago

If you use adCmdTable, you need to only pass the table name, not an SQL query, that's what's causing this error

Ibo Over a year ago

also use an explicit referencing for the range, for example use rs("field1").value=WS.range("A1") where WS is a worksheet object (set WS=Thisworkbook.Worksheets("Sheet1"))

|

Collectives™ on Stack Overflow

Append data to Access Table with ADO using Excel VBA

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest