CODEWITHSUNDEEP
sqlexcelvba
Naina
Naina

Reputation: 137

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

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

enter image description here

enter image description here

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

Upvotes: 0

Views: 301

Answers (1)

Parfait
Parfait

Reputation: 107587

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

Upvotes: 3

Related Questions