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

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Watch MrExcel Video

Forum statistics

Threads
1,109,501
Messages
5,529,238
Members
409,857
Latest member
KailuaTown
Top