我正在尝试从 SQL 服务器中获取一些数据以使用 VBA 表现出色。在网上研究后,我拼凑了一个代码,当我执行它时,它会抛出一个
运行时错误 -“2147217900 (80040e14)”:
关键字“ON”附近的语法不正确。
从错误看来,错误是由于查询中的某些异常而检测到的。该查询由 DBA 团队提供,并包含来自 2 个不同表的数据。我使用相同的查询手动将数据从数据库导出到 excel 并且它有效。我不知道为什么 vba 会抛出错误。任何帮助将不胜感激。谢谢。
Sub sync_data_vba_sqlserver()
Dim con As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sqlQry As String, strCon As String
Set con = New ADODB.Connection
Set rs = New ADODB.Recordset
ActiveSheet.Cells.ClearContents
sqlQry = "SELECT A.PUMA_TSD_PollID AS ID, PUMA_TSD_FieldName AS Field_Name,PUMA_TSD_FieldValue AS Field_Value,PUMA_TSD_IPAddress AS IP_Address,PUMA_TSD_PollDate AS Poll_Date,PUMA_TSD_Channel AS Channel,PUMA_TSD_MachineName AS Machine_Name,PUMA_TSD_TestBedType AS TestBedType FROM [WWW_AUXMOD].[dbo].[tblPUMA_TSD_AVLLynx] A INNER JOIN [WWW_AUXMOD].[dbo].tblPUMA_TSD_AVLLynxData L ON A.PUMA_TSD_PollID = L.PUMA_CH_TSD_PollID ON A.PUMA_TSD_PollID = L.PUMA_CH_TSD_PollID WHERE PUMA_TSD_MachineName != 'ELMS_SYSTEM' AND PUMA_TSD_PollDate >= '6/2/19';"
'---- Replace below highlighted names with the corresponding values
strCon = "Provider=sqloledb;Data Source=ECCDB1503.MD3Q.FORD.COM;Initial Catalog=WWW_AUXMOD;Integrated Security=SSPI"
'--- Open the above connection string.
con.Open (strCon)
rs.Open sqlQry, con
ActiveSheet.Cells(2, 1).CopyFromRecordset rs
rs.Close
con.Close
Set rs = Nothing
End Sub
期望将数据从 SQL Server 导出到 Excel。
拉出您的查询并粘贴在这里。在下面评论,但删除了 ON 并更改为 AND。你连续开了 2 个 ON。
SELECT A.PUMA_TSD_PollID AS ID, PUMA_TSD_FieldName AS Field_Name,PUMA_TSD_FieldValue AS Field_Value,PUMA_TSD_IPAddress AS IP_Address,PUMA_TSD_PollDate AS Poll_Date,PUMA_TSD_Channel AS Channel,
PUMA_TSD_MachineName AS Machine_Name,PUMA_TSD_TestBedType AS TestBedType
FROM [WWW_AUXMOD].[dbo].[tblPUMA_TSD_AVLLynx] A
INNER JOIN [WWW_AUXMOD].[dbo].tblPUMA_TSD_AVLLynxData L ON A.PUMA_TSD_PollID = L.PUMA_CH_TSD_PollID
-- change ON to AND HERE (ON IS REMOVED)
--AND A.PUMA_TSD_PollID = L.PUMA_CH_TSD_PollID (Redundant statement)
WHERE PUMA_TSD_MachineName != 'ELMS_SYSTEM'
AND PUMA_TSD_PollDate >= '6/2/19'
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句