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
Here is the full code below
Any help would be great, I think it might just be something silly!
<!-- BEGIN TEMPLATE: bbcode_code -->
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 -->