CODEWITHSUNDEEP
excelvbams-access
DYilm
DYilm

Reputation: 35

Append data to Access Table with ADO using Excel VBA

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

Upvotes: 1

Views: 4728

Answers (1)

Ibo
Ibo

Reputation: 4329

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

Upvotes: 2

Related Questions