Macro to Transfer data to Access not Working

Demorganafari

New Member
Joined
Jun 19, 2016
Messages
2
Dear All,
I need your kind assistance with this issue: I have been running the code below to transfer data from my Excel worksheet to an Access backend Database. This has been working all this while, but suddenly, I am seeing this error (my first time):
Run-time error'-2147217887(80040e21)':
Multiple Step OLE DB operation generated errors. Check each OLE DB
status value, if available no work done.


When I debug, it highlights this part of the Code: rst("Customer_Name") = Customer_Name

Below is the Code:

Code:
Sub CallAddTransfer()
    ' Used to call the code from page 480
    Dim WS As Worksheet
    Dim Qty As Integer
    Set WS = Worksheets("CashBook")
    FinalRow = WS.Cells(Rows.Count, 1).End(xlUp).Row
    Ctr = 0
    For i = 2 To FinalRow
        Customer_Name = Cells(i, 1).Value
        Document_Date = Cells(i, 2).Value
        Posting_Date = Cells(i, 3).Value
        Amount_ = Cells(i, 4).Value
        Curency = Cells(i, 5).Value
        Journal_Number = Cells(i, 6).Value
        Text_ = Cells(i, 7).Value
        Reference = Cells(i, 8).Value
        GL = Cells(i, 9).Value
        Customer_Account = Cells(i, 10).Value


        Ctr = Ctr + 1
        Application.StatusBar = "Adding Record " & Ctr
    AddTransfer Customer_Name, Document_Date, Posting_Date, Amount_, Curency, Journal_Number, Text_, Reference, GL, Customer_Account
    Next i
    Application.StatusBar = False
    MsgBox Ctr & " records added."
    
End Sub




Sub AddTransfer(Customer_Name As Variant, Document_Date As Variant, Posting_Date As Variant, Amount_ As Variant, Curency As Variant, Journal_Number As Variant, Text_ As Variant, Reference As Variant, GL As Variant, Customer_Account As Variant)
    ' Page 480
    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset
    
    MyConn = ThisWorkbook.Path & Application.PathSeparator & "Backend.mdb"
    MyConn = "Driver=Microsoft Access Driver (*.mdb);DBQ=" & MyConn
    
    ' open the connection
    Set cnn = New ADODB.Connection
    With cnn
  '      .Provider = "Microsoft.Jet.OLEDB.4.0"
        .Open MyConn
    End With
    
    ' Define the Recordset
    Set rst = New ADODB.Recordset
    rst.CursorLocation = adUseServer
    
    ' open the table
    rst.Open Source:="CASHBOOK", _
    ActiveConnection:=cnn, _
    CursorType:=adOpenDynamic, _
    LockType:=adLockOptimistic, _
    Options:=adCmdTable
    
    ' Add a record
    rst.AddNew
    
    ' Set up the values for the fields. The first four fields
    ' are passed from the calling userform. The date field
    ' is filled with the current date.
    rst("Customer_Name") = Customer_Name
    rst("Document_Date") = Document_Date
    rst("Posting_Date") = Posting_Date
    rst("Amount_") = Amount_
    rst("Curency") = Curency
    rst("Journal_Number") = Journal_Number
    rst("Text_") = Text_
    rst("Reference") = Reference
    rst("GL") = GL
    rst("Customer_Account") = Customer_Account
    
    
    ' Write the values to this record
    rst.Update
    
    ' Close
    rst.Close
    cnn.Close
End Sub

Kindly assist me on this.

Regards,
Stephen
 
Last edited by a moderator:

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Watch MrExcel Video

Forum statistics

Threads
1,114,524
Messages
5,548,553
Members
410,848
Latest member
anuradhagrewal
Top