sql - 尝试从 ODBC 数据库中获取数据时在 vba 上出现类型不匹配错误
问题描述
我正在尝试使用 vba 从 SQL 查询中获取一些数据,但是当我尝试运行代码时,它给了我一个类型不匹配错误。谁能帮忙
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
解决方案
考虑将涉及的长 SQL 保存在一个文本文件中,其中包含所有需要的换行符和空格格式,这有助于提高可读性和可维护性。您甚至可以捕获语法错误,例如未将IN
元素包装在括号中(尽管应将一个项目列表替换为=
)。
然后,以字符串形式读入 Excel,没有任何杂乱的连接或引号标点符号。
SQL (另存为 .sql 文件)
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
推荐阅读
- javascript - 如何查看由 Heroku 上的脚本创建的文件的内容
- sql - 递归 ORDER BY
- java - 使用 apache-poi 读取 xlsx 文件时出现问题
- ssas - SSAS比较前行数据并获取计数
- python - 分别按时间{O(n logn)或O(kn)}和空间{O(1)和O(k)}比较算法
- scala - Scala 找不到 mixin 提供的隐式值
- python - 即使使用 loc 也触发 SettingWithCopyWarning
- r - 将列表中的 probitmfx 输出提供给 Stargazer
- javascript - 向上滚动和向下滚动时波动的网站横幅在下方出现空白
- mongoose - 用文档中的字段填充模型