Run-time error '-2147467259 (80004500)' - VBA for Access dbase update

Hey guys,

A little bit of how my set goes. First, I have an access database that stores the employee information as well as call metric goals. I use a VBA script to pull certain agents based on a few parameters into a table. What I need to happen is, if any data is changed in that same table, such as, an employees metric goal is adjusted, I need it to updated on the access database table. Here is the script I am using. I'm using Excel and Access 2013.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    Dim r As Long
    Dim conn As ADODB.Connection
    Dim strConn As String
    Dim strSQL As String

    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
        "C:\Users\Josh\Desktop\agent-data-2014.accdb"
    Set conn = New ADODB.Connection
    conn.Open strConn
    For Each rng In Target.Rows
        r = rng.Row
        If r > 1 Then
            strSQL = "UPDATE MSC_MU SET " & _
                "MU_ID='" & Worksheets("CONF").Range("F" & r).Value & "', " & _
                "AGENT_NAME='" & Worksheets("CONF").Range("G" & r).Value & "', " & _
                "AGENT_ID='" & Worksheets("CONF").Range("H" & r).Value & ", " & _
                "CATEGORY='" & Worksheets("CONF").Range("I" & r).Value & "', " & _
                "TEAM='" & Worksheets("CONF").Range("J" & r).Value & "', " & _
                "SUPERVISOR='" & Worksheets("CONF").Range("K" & r).Value & "', " & _
                "EMP_STATUS='" & Worksheets("CONF").Range("L" & r).Value & "', " & _
                "TERM_DATE='" & Worksheets("CONF").Range("M" & r).Value & "', " & _
                "DAYS='" & Worksheets("CONF").Range("N" & r).Value & "', " & _
                "TIME='" & Worksheets("CONF").Range("O" & r).Value & "', " & _
                "BREAKS='" & Worksheets("CONF").Range("P" & r).Value & "', " & _
                "LUNCH='" & Worksheets("CONF").Range("Q" & r).Value & "', " & _
                "CPH='" & Worksheets("CONF").Range("R" & r).Value & "', " & _
                "RING='" & Worksheets("CONF").Range("S" & r).Value & "', " & _
                "TALK='" & Worksheets("CONF").Range("T" & r).Value & "', " & _
                "ACW='" & Worksheets("CONF").Range("U" & r).Value & "', " & _
                "HOLD='" & Worksheets("CONF").Range("V" & r).Value & "', " & _
                "UAUX='" & Worksheets("CONF").Range("W" & r).Value & "', " & _
                "PAUX=" & Worksheets("CONF").Range("X" & r).Value & " WHERE " & _
                "ID=" & Worksheets("CONF").Range("E" & r).Value
            conn.Execute strSQL
        End If
    Next rng
    conn.Close
    Set conn = Nothing
End Sub

Now, I get the feeling I'm using the wrong strConn/reference in this script, like it's looking for sql and not access library. When I change something in the table, I get this gnarly error message:

Run-time error '-2147467259 (80004500)' Unrecognized database format.

Any ideas why or what I can do to make this work?

This works:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    Dim r As Long
    Dim conn As ADODB.Connection
    Dim strConn As String
    Dim strSQL As String

    strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _
        "C:\Users\Josh\Desktop\agent-data-2014.accdb"

        
    Set conn = New ADODB.Connection
    conn.Open strConn
    For Each rng In Target.Rows
        r = rng.Row
        If r > 1 Then
            strSQL = "UPDATE MSC_MU SET " & _
                "MU_ID='" & Worksheets("SQL").Range("B" & r).Value & "', " & _
                "AGENT_NAME='" & Worksheets("SQL").Range("C" & r).Value & "', " & _
                "AGENT_ID='" & Worksheets("SQL").Range("D" & r).Value & "', " & _
                "CATEGORY='" & Worksheets("SQL").Range("E" & r).Value & "', " & _
                "TEAM='" & Worksheets("SQL").Range("F" & r).Value & "', " & _
                "SUPERVISOR='" & Worksheets("SQL").Range("G" & r).Value & "', " & _
                "EMP_STATUS='" & Worksheets("SQL").Range("H" & r).Value & "', " & _
                "TERM_DATE='" & Worksheets("SQL").Range("I" & r).Value & "' WHERE " & _
                "ID=" & Worksheets("SQL").Range("A" & r).Value
            conn.Execute strSQL
        End If
    Next rng
    conn.Close
    Set conn = Nothing
End Sub

However, is there a way to make this a button click-able macro that updates the entire table at once rather than have it update each time a field is changed?

Private Sub CommandButton1_Click()
    Dim rng As Range
    Dim r As Long
    Dim conn As ADODB.Connection
    Dim strConn As String
    Dim strSQL As String

    strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _
        "C:\Users\Josh\Desktop\agent-data-2014.accdb"

        
    Set conn = New ADODB.Connection
    conn.Open strConn
    For Each rng In Target.Rows
        r = rng.Row
        If r > 1 Then
            strSQL = "UPDATE MSC_MU SET " & _
                "MU_ID='" & Worksheets("CONF").Range("F" & r).Value & "', " & _
                "AGENT_NAME='" & Worksheets("CONF").Range("G" & r).Value & "', " & _
                "AGENT_ID='" & Worksheets("CONF").Range("H" & r).Value & "', " & _
                "CATEGORY='" & Worksheets("CONF").Range("I" & r).Value & "', " & _
                "TEAM='" & Worksheets("CONF").Range("J" & r).Value & "', " & _
                "SUPERVISOR='" & Worksheets("CONF").Range("K" & r).Value & "', " & _
                "EMP_STATUS='" & Worksheets("CONF").Range("L" & r).Value & "', " & _
                "TERM_DATE='" & Worksheets("CONF").Range("M" & r).Value & "' WHERE " & _
                "ID=" & Worksheets("CONF").Range("E" & r).Value
            conn.Execute strSQL
        End If
    Next rng
    conn.Close
    Set conn = Nothing
End Sub

Okay I have added the button and the code. I edited some fields and clicked the button and got this error:
"Run-time error '424' Object Required" and the debug is highlighting For Each rng In Target.Rows

Pareshj, you have been a huge help and I thank you! Honestly, I know very little of VBA and I've been at this for over 12 hours.

Here is the script I am using now, it seems to be working!

Private Sub CommandButton1_Click()
    Dim rng As Range
    Dim r As Long
    Dim conn As ADODB.Connection
    Dim strConn As String
    Dim strSQL As String

    strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _
        "C:\Users\Josh\Desktop\agent-data-2014.accdb"

        
    Set conn = New ADODB.Connection
    conn.Open strConn
    'For Each rng In Target.Rows
    r = 8
    Do While Len(Range("E" & r).Formula) > 0
        If r > 1 Then
            strSQL = "UPDATE MSC_MU SET " & _
                "MU_ID='" & Worksheets("CONF").Range("F" & r).Value & "', " & _
                "AGENT_NAME='" & Worksheets("CONF").Range("G" & r).Value & "', " & _
                "AGENT_ID='" & Worksheets("CONF").Range("H" & r).Value & "', " & _
                "CATEGORY='" & Worksheets("CONF").Range("I" & r).Value & "', " & _
                "TEAM='" & Worksheets("CONF").Range("J" & r).Value & "', " & _
                "SUPERVISOR='" & Worksheets("CONF").Range("K" & r).Value & "', " & _
                "EMP_STATUS='" & Worksheets("CONF").Range("L" & r).Value & "', " & _
                "TERM_DATE='" & Worksheets("CONF").Range("M" & r).Value & "' WHERE " & _
                "ID=" & Worksheets("CONF").Range("E" & r).Value
            conn.Execute strSQL
        End If
        r = r + 1
        Loop
    conn.Close
    Set conn = Nothing
End Sub