Front-End Excel and back-end access update data

Question

I need a small help. I have an Access file name "DB_MLL.accdb" with table name "tblMLL" total 31 columns including primary key. I'm using front-end Excel and back-end access to fetch the data. I have two buttons to pull data from Acess and push back to access. Pulling data from access is working fine but pushing back is not working. I'm using the below codes. request you please guide me where I am doing wrong.

Sub PushTableToAccess()
Dim cnn As ADODB.Connection
Dim MyConn
Dim rst As ADODB.Recordset
Dim i As Variant, j As Variant
Dim Rw As Long

Sheets("Data").Activate
Rw = Range("A65536").End(xlUp).Row

Set cnn = New ADODB.Connection
MyConn = ThisWorkbook.Path & Application.PathSeparator & TARGET_DB

With cnn
    .Provider = "Microsoft.ACE.OLEDB.12.0"
    .Open MyConn
End With

Set rst = New ADODB.Recordset
rst.CursorLocation = adUseServer
rst.Open Source:="tblMLL", ActiveConnection:=cnn, _
         CursorType:=adOpenDynamic, LockType:=adLockOptimistic, _
         Options:=adCmdTable

'Load all records from Excel to Access.
For i = 3 To Rw
    rst.AddNew
    For j = 1 To 31
    If Cells(i, j).Value = "" Then
        rst(Cells(2, j).Value) = ""
        Else
        rst(Cells(2, j).Value) = Cells(i, j).Value
    End If
    Next j
    rst.Update
Next i

' Close the connection
rst.Close
cnn.Close
Set rst = Nothing
Set cnn = Nothing
MsgBox "Data Upload Completed successfully."
End Sub

but pushing back is not working...is not helpful for us. Please post error or undesired results. — Parfait

Dy.Lee Dy.Lee · Accepted Answer · 2018-01-28T14:06:13

Your code seems to have a bit of a fix.

Sub PushTableToAccess()
    Dim cnn As ADODB.Connection
    Dim MyConn
    Dim rst As ADODB.Recordset
    Dim i As Variant, j As Variant
    Dim Rw As Long
    Dim strConn As String

    Sheets("Data").Activate
    Rw = Range("A65536").End(xlUp).Row

    Set cnn = New ADODB.Connection
    MyConn = ThisWorkbook.Path & Application.PathSeparator & TARGET_DB
        strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
            "Data Source=" & MyConn

    cnn.Open strConn
    Set rst = New ADODB.Recordset
    wirh rst
        .CursorLocation = adUseServer
        .Source = "tblMLL"
        .Options = adCmdTable
        .ActiveConnection = strConn
        .CursorType = adOpenDynamic
        .LockType = adLockOptimistic
        .Open


    'Load all records from Excel to Access.
        For i = 3 To Rw
            .AddNew
            For j = 1 To 31
                If Cells(i, j).Value = "" Then
                    .Fields(Cells(2, j).Value) = ""
                    Else
                    .Fields(Cells(2, j).Value) = Cells(i, j).Value
                End If
            Next j
            .Update
        Next i
    End With
    ' Close the connection
    rst.Close
    cnn.Close
    Set rst = Nothing
    Set cnn = Nothing
    MsgBox "Data Upload Completed successfully."
End Sub

Front-End Excel and back-end access update data

1 Answers