Data type mismatch in criteria expression error from Excel

[Philosophaie]

I am trying to populate a Access table from Excel 2010.



I get the error: "Data type mismatch in criteria expression."

It points to the rs.Open command.

I tried a few different ways but each time the same error occurs.

    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim ConnStr As String
    Set rs = New ADODB.Recordset
    Set cn = New ADODB.Connection
    ConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\path\db1.mdb;"
    cn.Open (ConnStr)
    For k = 1 To Sheets("2013").Cells(Rows.Count, "A").End(xlUp).Row - 4
        With rs
            PrimaryKey = k
            SQLstr = "SELECT * FROM [table1] WHERE [Index]='" & PrimaryKey & "';"

            .Open Source:=SQLstr, ActiveConnection:=cn, CursorType:=adOpenDynamic, LockType:=adLockOptimistic, Options:=adCmdText
            
            If .EOF = True Then
              .AddNew ' create a new record
            End If
            .Fields("Index") = Sheets("2013").Cells(k + 4, 1).Value
            .Fields("DatePaid") = Sheets("2013").Cells(k + 4, 2).Value
            .Fields("WhatPaid") = Sheets("2013").Cells(k + 4, 3).Value
            .Fields("AmtPaid") = Sheets("2013").Cells(k + 4, 4).Value
            .Fields("Total") = Sheets("2013").Cells(k + 4, 5).Value
            .Fields("AmtRec") = Sheets("2013").Cells(k + 4, 6).Value
            .Fields("WhatRec") = Sheets("2013").Cells(k + 4, 7).Value
            .Fields("DateRec") = Sheets("2013").Cells(k + 4, 8).Value
            .Update
        End With
    Next k
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing

[June7]

Is Index a number type field? If so, remove the apostrophe delimiters from around PrimaryKey variable. Numbers don't require delimiters. Apostrophe is for text and # for date.