CODEWITHSUNDEEP
excelvba
Pete Nova
Pete Nova

Reputation: 3

Can't update Access database from Excel sheet

I'm new to Excel VBA and looking to update Access database from Excel sheet based on the below code. I'm getting an error when I run the code:

run-time error '3001': Arguments are of the wrong type, are of acceptable range, or are in conflict with one another

I think the issue is with myRecordset.Open command but I'm just totally stuck and cannot figure out how to fix it.

Any advice would be much appreciated.

Private Sub CommandButton1_Click()
    Update MS Access database

    Dim oConn As Object
    Dim myRecordset As Object
    Dim sConn As String

    sConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Documents\Standard Form for Rate Requests\Database41.accdb"
    Set oConn = CreateObject("ADODB.Connection")
    oConn.Open sConn

    Set myRecordset = CreateObject("ADODB.RecordSet")
    myRecordset.Open "MainTable", oConn, adOpenForwardOnly, adLockPessimistic, adCmdTable

    With myRecordset
        .AddNew
        .Fields("ID").value = Worksheets("Sheet1").Range("A5").value
        .Fields("Order Number").value = Worksheets("Sheet1").Range("A5").value
        .Fields("Requester").value = Worksheets("Sheet1").Range("B2").value
        .Fields("Request Type").value = Worksheets("Sheet1").Range("B5").value
        .Fields("Transport Mode").value = Worksheets("Sheet1").Range("C5").value
        .Fields("Origin").value = Worksheets("Sheet1").Range("B16").value
        .Fields("Destination").value = Worksheets("Sheet1").Range("I16").value
        .Fields("Collection Date").value = Worksheets("Sheet1").Range("D5").value
        .Fields("Delivery Date").value = Worksheets("Sheet1").Range("E5").value
        .Fields("Note").value = Worksheets("Sheet1").Range("J12").value

        .Update
        .Close
    End With

    Set myRecordset = Nothing
    Set oConn = Nothing
End Sub

Upvotes: 0

Views: 209

Answers (2)

Pete Nova
Pete Nova

Reputation: 3

Many thanks GSD!!

I selected Microsoft ActiveX Data Objects 6.1 Library and this resolved my issue. I also had to delete reference to update "ID" field in Access database (.Fields("ID").value = Worksheets("Sheet1").Range("A5").value) as this was also causing an error.

Upvotes: 0

GSD
GSD

Reputation: 1252

Did you set a reference to Microsoft Activex Data Objects Library? 1. Go to VBE and Select References.. from Tools Menu. 2. Then select ” Microsoft Activex Data Objects Library” from the list.

Upvotes: 1

Related Questions