Geek asks and answers

Attempting to execute a query with OLEDBConnection Runtime Error "1004": Application-Defined Error or Object-Dependent Error

I get error 1004..Connection

    With ActiveWorkbook.Connections(ConnectionName).OLEDBConnection
    .BackgroundQuery = True
    .CommandText = MyCommandText
    .CommandType = xlCmdSql
    .Connection = ConnectionString

ConnectionString is as follows:

"Provider=SQLOLEDB.1" & Chr(59) & "Integrated Security=SSPI" & Chr(59) & _
"Persist Security Info=True" & Chr(59) & "Initial Catalog=wss_back" & Chr(59) & _
"Data Source=XX-X-0009999.de.xxx.com,12345" & Chr(59) & _
"Use Procedure for Prepare=1" & Chr(59) & "Auto Translate=True" & Chr(59) & _
"Packet Size=4096" & Chr(59) & "Workstation ID=XX-X-1234567" & Chr(59) & _
"Use Encryption for Data=False" & Chr(59) & _
"Tag with column collation when possible=False"

Any ideas why this is failing?

+3
source share
1 answer

The problem is in the line:

    .CommandType = xlCmdSql

CommandType expects a value from ADODB.CommandTypeEnum, but you send a value from xlCmdType. xlCmdSql= 2, which is the same as ADODB.adCommandTypeEnum.adCmdTable, and not adCmdTextwhat you need here.

So change it to:

With ActiveWorkbook.Connections(ConnectionName).OLEDBConnection
  .BackgroundQuery = True
  .CommandText = MyCommandText
  .CommandType = adCmdText
  .Connection = ConnectionString

... and it should work.

0
source

More articles:


All Articles