Getting Type Mismatch error on vba while trying to fetch data from ODBC database

Question

I am trying to fetch some data from SQL query using vba, but when I try to run the code it gives me a type mismatch error. can anyone please help

Sub Get_Rowkey()

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Dim DBresults As ADODB.Recordset
Dim sqlquery As String, wsData As Worksheet, Datasht As Worksheet
Dim elr As Long
Dim lRow As Long
Set DBresults = New ADODB.Recordset

Set wsData = Worksheets("Main")
Set Datasht = Worksheets("Backend")
lRow2 = Datasht.Cells(Rows.Count, 2).End(xlUp).Row
elr = Datasht.Cells(Rows.Count, 3).End(xlUp).Row
If lRow2 > 1 Then

    Call createconn
    
    
     sqlquery = "SELECT vyc.object_id, vyc.rowkey, el.instrument, i.ccy, vyc.timezone, vyc.asof, vyc.snaptime, vp.rate as Mid, vp.bid, vp.ask, vp.updatetime" & vbNewLine & _
"from val_yield_curves vyc" & vbNewLine & _
"join val_yc_def_elements el on el.pkey = valuations_yieldcurves.getCurveDefinitionKey(vyc.asof, vyc.object_id)" & vbNewLine & _
"join (SELECT asof, curve, instrument, rate, quality, source, updatetime, bid, ask from val_prices_interestrates" & vbNewLine & _
"union SELECT asof, curve, instrument, rate, quality, source, updatetime, bid, ask from val_prices_basisswaps" & vbNewLine & _
"union SELECT asof, curve, instrument, rate, quality, source, updatetime, bid, ask from val_prices_oisrates" & vbNewLine & _
"union SELECT asof, curve, instrument, rate, quality, source, updatetime, bid, ask from val_prices_fx)" & vbNewLine & _
"vp on el.instrument = vp.instrument and vp.asof = vyc.asof and vp.curve = vyc.rowkey" & vbNewLine & _
"join (select pkey, symbol from val_mds_sources) vms on vms.pkey = vp.source" & vbNewLine * _
"join instruments i on  i.pkey = el.instrument" & vbNewLine & _
"where vyc.asof in '12Oct2020'" & vbNewLine & _
"and vyc.timezone in 'L1200'" & vbNewLine & _
"and vyc.object_id like '%FXSpot%'" & vbNewLine & _
"ORDER BY vp.updatetime"`enter code here`
    ''sqlquery = Left(sqlquery, Len(sqlquery) - 1) & ")"
    DBresults.Open sqlquery, DBConn, adOpenDynamic

You need a space before all clauses, FROM, JOIN, UNION, WHERE, ORDER BY, as well as operators: AND. — Parfait
– Parfait, Commented Oct 18, 2020 at 13:04
Hi Parfait, thanks for the solution, can you pls shed some more light on it. do I need to use something like this " from abc table ? — Naina
– Naina, Commented Oct 18, 2020 at 13:07

Parfait · Accepted Answer · 2020-10-18 13:17:18Z

3

Consider saving your long, involved SQL in a text file with all needed formatting of line breaks and spaces which helps in readability and maintainability. You can even catch syntax errors like not wrapping IN elements in parentheses (though one item list should be replaced with =).

Then, read into Excel as string without any messy concatenation or quote punctuation.

SQL (save as .sql file)

SELECT vyc.object_id
       , vyc.rowkey
       , el.instrument
       , i.ccy
       , vyc.timezone
       , vyc.asof
       , vyc.snaptime
       , vp.rate AS Mid
       , vp.bid
       , vp.ask
       , vp.updatetime

FROM val_yield_curves vyc

JOIN val_yc_def_elements el 
  ON el.pkey = valuations_yieldcurves.getCurveDefinitionKey(vyc.asof, vyc.object_id)

JOIN 
    (SELECT asof, curve, instrument, rate, quality, source, updatetime, bid, ask 
     FROM val_prices_interestrates
     UNION
     SELECT asof, curve, instrument, rate, quality, source, updatetime, bid, ask
     FROM val_prices_basisswaps
     UNION
     SELECT asof, curve, instrument, rate, quality, source, updatetime, bid, ask 
     FROM val_prices_oisrates
     UNION 
     SELECT asof, curve, instrument, rate, quality, source, updatetime, bid, ask 
     FROM val_prices_fx) vp
  ON el.instrument = vp.instrument 
  AND vp.asof = vyc.asof 
  AND vp.curve = vyc.rowkey
    
JOIN 
    (SELECT pkey, symbol FROM val_mds_sources) vms 
  ON vms.pkey = vp.source

JOIN instruments i 
  ON  i.pkey = el.instrument

WHERE vyc.asof IN ('12Oct2020')         -- FIXED
  AND vyc.timezone IN ('L1200')         -- FIXED
  AND vyc.object_id LIKE '%FXSpot%'

ORDER BY vp.updatetime

VBA

...

' READ SQL QUERY FROM FILE INTO STRING
With CreateObject("Scripting.FileSystemObject")
      sqlquery = .OpenTextFile("C:\path\to\my\SQL\Query.sql", 1).readall
End With

DBresults.Open sqlquery, DBConn, adOpenDynamic

answered Oct 18, 2020 at 13:17

Parfait

108k19 gold badges103 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Naina Over a year ago

i have made the suggested changes but still getting the same error. i have attached a screenshot of the amended query. not sure what else is going wrong

Parfait Over a year ago

If you actually did use an .sql file and same error still raises, try testing query by taking out one JOIN and WHERE condition at a time to locate which is the problem line. Otherwise, identify all data types of every variables in join/where lines. We do not know your schema. Also, check the returned type of valuations_yieldcurves.getCurveDefinitionKey().

Naina Over a year ago

i have rewritten the whole query manually and now the error has changed to Run-time error 91, object variable or with block variable not set which comes at this line : DBresults.Open sqlquery, DBConn, adOpenDynamic

Naina Over a year ago

i got it , Set DBresults = New ADODB.Recordset . its working now :)

Naina Over a year ago

no, i actually had to write the entire query manually with proper indentation. but I really appreciate your time and effort given.

|

Collectives™ on Stack Overflow

Getting Type Mismatch error on vba while trying to fetch data from ODBC database

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related