VBA run time error 13 type mismatch, code provided

Stephen11

New Member
Joined
Jun 23, 2012
Messages
3
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
Code:
Set rs = db.OpenRecordset("SELECT * FROM " & TableName & _ 
" WHERE " & FieldName & _ 
" = " & Chr$(34) & MyCriteria & Chr$(34), dbReadOnly)

Here is the full code below

Code:
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 -->
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

pcc

Well-known Member
Joined
Jan 21, 2003
Messages
1,345
If sampleid is a number field in your database, you will get this error. You would need to change the sql to
Code:
Set rs = db.OpenRecordset("SELECT * FROM " & TableName & _ 
" WHERE " & FieldName & _ 
" = "  & MyCriteria , dbReadOnly)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,216
Messages
5,594,886
Members
413,947
Latest member
gizmolucy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top