Hello. I am still relatively new to VBA, but I am having a problem copying a working VBA script from one workbook and having it work in another.

It is a simple code that takes 85 fields in one workbook and sends it to an access database. The code works fine in the original workbook. Once I copy it over to another wookbook, I start getting "Type Mismatch" errors. The code (in the new workbook only) gets tripped up when the value entered is text. When I delete out the text and put in numbers as a test, a blank error box appears and nothing gets sent to the access database. I checked the access database, and the field in question is text, so the text in excel should not have been a "mismatch" in the first place.

It seems so simple but it is driving me bonkers. Any ideas?

The code is below:


Sub PushTableToAccess()

    Dim cnn As ADODB.Connection
    Dim MyConn
    Dim rst As ADODB.Recordset
    Dim i As Long, j As Long, k As Long
    Dim Rw As Long


    Sheets("Record Keeping").Activate

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

    With cnn
       .Provider = "Microsoft.Jet.OLEDB.4.0"
       .Open MyConn
    End With

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

    For i = 4 To 4
       rst.AddNew

       For j = 3 To 87

          rst(Cells(1, j).Value) = Cells(i, j).Value

        ' the code throws a "Type Mismatch" error at j = 33 when text is in the cell.

       Next j
       rst.Update
       
    Next i

    ' Clean up
    rst.Close
    cnn.Close
    Set rst = Nothing
    Set cnn = Nothing
    
    MsgBox "Done and Done"
    
End Sub