VBA run time error 13 type mismatch, code provided

[Stephen11]

Hi people,
My aim was to move towards autoreporting and I am now in my final stages. However my final hurdle involves a run time error 13.
I am using excel to pull data from access tables. However one table is connected to an OCDB oracle database. I've attached the code below, The connection seems to be working as it asks me to enter the password but then I get the error 13 "type mismatch" and it highlights

Set rs = db.OpenRecordset("SELECT * FROM " & TableName & _ 
" WHERE " & FieldName & _ 
" = " & Chr$(34) & MyCriteria & Chr$(34), dbReadOnly)

Here is the full code below

Sub Test1() 
    Dim DBFullName As String 
    Dim TableName As String 
    Dim FieldName As String 
    Dim TargetRange As Range 
    Dim MyCriteria As String 
    Dim cnn As ADODB.Connection 
    Dim rs As ADODB.Recordset 
    Set cnn = New ADODB.Connection 
    Set rs = New ADODB.Recordset 
    Dim db As database 
    Dim intColIndex As Integer 
     
    DBFullName = "D:\Documents and Settings\E400845\Desktop\Copy of ChemToast2007 QRY.accdb" 
    TableName = "SAMPLE" 
    FieldName = "SAMPLEID" 
    MyCriteria = Sheets("Sheet1").Range("A2").Value 
    cnn.Open "SAPPHIRE 10", "SAPPHIRE", "SAPPHIRE" 
    rs.ActiveConnection = cnn 
    rs.CursorLocation = adUseServer 
     
    MyCriteria = Sheets("Sheet1").Range("A2").Value 
    Set TargetRange = Range("A6") 
    Set db = OpenDatabase(DBFullName) 
    Set rs = db.OpenRecordset("SELECT * FROM " & TableName & _ 
    " WHERE " & FieldName & _ 
    " = " & Chr$(34) & MyCriteria & Chr$(34), dbReadOnly) 
     
    rs.Open 
    rst.Close 
    Set TargetRange = TargetRange.Cells(1, 1) 
    Set db = OpenDatabase(DBFullName) 
     
     
    For intColIndex = 0 To rs.Fields.Count - 1 
        TargetRange.Offset(0, intColIndex).Value = rs.Fields(intColIndex).Name 
         
    Next 
     ' write recordset
    TargetRange.Offset(1, 0).CopyFromRecordset rs 
     
    Set rst = Nothing 
    Set cnn = Nothing 
    Set rs = Nothing 
    db.Close 
    Set db = Nothing 
End Sub

Any help would be great, I think it might just be something silly!
<!-- BEGIN TEMPLATE: bbcode_code -->

 

[pcc]

If sampleid is a number field in your database, you will get this error. You would need to change the sql to

Set rs = db.OpenRecordset("SELECT * FROM " & TableName & _ 
" WHERE " & FieldName & _ 
" = "  & MyCriteria , dbReadOnly)