error:Business Object Cannot be created

Question

Please help me with below:

I need to connect to the remote server and run a select query (Sql server) and copy the result to Excel file. the select query is working when directly run in the remote server, SQL server management studio.

Managed to successfully open the connection string but when I try to open it to record-set I am getting error "Run time error 4100 Business Object Cannot be created"  in Excel. here is my code:

Dim SQLStr As String

Dim rs As ADODB.Recordset

Dim cnn As ADODB.Connection

Public Const strConn= "Provider=MS Remote;" & _

                                 "Remote Server=333333333;" & _

                                 "Remote Provider=SQLOLEDB;" & _

                                 "Data Source=databaseName;" & _

                                 "Integrated Security = SSPI;" & _

                                 "User ID=XXXX;" & _

                                 "Password=XXXXXX"

Set rs = New ADODB.Recordset

Set cnn = New ADODB.Connection

cnn.Open strConn

Debug.Print cnn.State   ' Returns 1

If cnn.State = adStateOpen Then

        MsgBox "Connection Succeeded", vbInformation

        Debug.Print cnn.State

    Else

        For Each vError In cn.Errors

            sErrors = sErrors & vError.Description & vbNewLine

        Next vError

        If sErrors > "" Then

            MsgBox sErrors, vbExclamation

        Else

            MsgBox "Connection Failed", vbExclamation

        End If

    End If

rs.Open "Select * from Table1", cnn, adOpenKeyset, adLockOptimistic  ' getting 4100 error message

 Debug.Print rs.RecordCount

Set rsresult = Nothing

Set objCmd = Nothing

Set TargetRange = Nothing

cnSQL2.Close

Set cnSQL2 = Nothing

ErrorHandler:

If cnSQL2.State = 0 Then

    MsgBox "Unable to connect to the Database Server as this time.", vbCritical, "Server connection error"

   'Exit Function

End If

Answer 1

Hi,

Your Windows 10 question is more complex than what is typically answered in the Microsoft Answers forums. It is better suited for the IT Pro audience on MSDN. Please post your question in the Microsoft Developer Network forum.

Feel free to post back should you have further concerns.