尝试使用VBA从SQL Server检索数据时出现运行时错误'-2147217900(80040e14) [英] Run-time error '-2147217900 (80040e14) while trying to retrieving data from SQL Server using VBA

问题描述

我正在尝试使用以下代码从Excel VBA连接到SQL Server.但是，当我运行以下查询时，出现错误

I am trying to connect to SQL Server from Excel VBA using the following code. But when I run the below query, I get an error

运行时错误'-2147217900(80040e14).

Run-time error '-2147217900 (80040e14).

这是我的代码:

    Dim objMyConn As ADODB.Connection
    Dim objMyCmd As ADODB.Command
    Dim objMyCmd1 As ADODB.Command
    Dim objMyRecordset As ADODB.Recordset
    Dim rngUsedRange

    Set objMyConn = New ADODB.Connection
    Set objMyCmd = New ADODB.Command

    Set objMyRecordset = New ADODB.Recordset
    Set rngUsedRange = ActiveSheet.UsedRange

'Open Connection'

    objMyConn.ConnectionString = "Provider=SQLOLEDB;Data Source=10.5.3.16;Initial Catalog=PROTELECOM_AMERICAN_TOWERS;Trusted_connection=yes;"
    objMyConn.Open

'Set and Excecute SQL Command'
    Set objMyCmd.ActiveConnection = objMyConn
objMyCmd.CommandText = ";WITH CTE AS(SELECT BAN.BAN_IDFR, BAN.BAN_NBR, BAN.BAN_STATUS FROM PROTELE_BAN BAN INNER JOIN PROTELE_BILL BILL ON BAN.BAN_IDFR = BILL.BAN_IDFR" & _
"GROUP BY BAN.BAN_IDFR,BAN.BAN_NBR,BAN.BAN_STATUS)SELECT DISTINCT V.ORG_NAME [VENDOR NAME], CTE.BAN_NBR [BAN#], SVR.SER_ACCT_NBR [WTN#],W.COST_CENTER[COST CENTER],W.COST_CODE[COST CODE], BA.ECOM_ID [VENDOR ID],BC.SERVICE_TYPE [SERVICE TYPE], CONVERT(VARCHAR(10),MAX(B.BILL_DATE),101) AS [BILL DATE],SUM(BC.AMOUNT) AMOUNT, B.EXTRACT_STATUS [EXTRACT STATUS],CONVERT(VARCHAR(10),B.EXTRACT_DATE,101) AS [EXTRACTED DATE]" & _
"FROM CTE INNER JOIN PROTELE_BILL B ON CTE.BAN_IDFR = B.BAN_IDFR" & _
"INNER JOIN PROTELE_BILL_CHARGE BC ON B.BILL_IDFR = BC.BILL_IDFR" & _
"INNER JOIN PROTELE_BAN_WTN_MAPPING MAPP ON B.BAN_IDFR = MAPP.BAN_IDFR" & _
"INNER JOIN PROTELE_SVR_WTN SVR ON SVR.SVR_ACCT_IDFR=BC.SVR_ACCT_IDFR" & _
"INNER JOIN PROTELE_SVR_WTN_DETAILS W ON CTE.BAN_IDFR = W.BAN_IDFR AND SVR.SVR_ACCT_IDFR = W.SVR_ACCT_IDFR AND B.BAN_IDFR=W.BAN_IDFR" & _
"INNER JOIN PROTELE_BAN_VENDOR BV ON CTE.BAN_IDFR = BV.BAN_IDFR" & _
"INNER JOIN PROTELE_BAN BA ON CTE.BAN_IDFR = BA.BAN_IDFR" & _
"INNER JOIN PROTELE_LOCATION C ON C.LOCA_IDFR = BV.VNDR_LOCA_IDFR" & _
"INNER JOIN PROTELE_VENDOR V ON C.ORG_IDFR = V.ORG_IDFR WHERE BC.SVR_ACCT_IDFR=W.SVR_ACCT_IDFR AND (B.EXTRACT_DATE BETWEEN '02/05/2016' AND '02/06/2016')" & _
"GROUP BY CTE.BAN_NBR, B.EXTRACT_DATE, BA.ECOM_ID,B.BILL_DATE,B.EXTRACT_STATUS,BC.SERVICE_TYPE,W.COST_CENTER,W.COST_CODE, SVR.SER_ACCT_NBR,V.ORG_NAME" & _
"ORDER BY CTE.BAN_NBR, V.ORG_NAME;"
objMyCmd.CommandType = adCmdText
'Open Recordset'
    Set objMyRecordset.Source = objMyCmd
    objMyRecordset.Open
    For intColIndex = 0 To objMyRecordset.Fields.Count - 1
    Selection.Offset(0, intColIndex).Value = objMyRecordset.Fields(intColIndex).Name
    Next

'Copy Data to Excel'
    ActiveSheet.Range("A2").CopyFromRecordset objMyRecordset

推荐答案

引起我注意的一件事是您的连接.您似乎在concat之间没有空格或返回字符.

The one thing that jumps to my attention is your concatenations. You don't seem to be padding spaces or return characters between your concats.

例如，当您执行此操作时:

For example, when you do this:

sql = "select one, two, three" & _
      "from x"

它将输出显示为:

select one, two, threefrom x

应该出错.

您可以在每个字符串的末尾添加空格，看看是否有帮助?除此之外，我看不到任何明显的代码问题.

Can you add spaces at the end of each string and see if that helps? With the exception of that, I don't see any glaring issues with the code.

这篇关于尝试使用VBA从SQL Server检索数据时出现运行时错误'-2147217900(80040e14)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！