I try to query a MS SQL Database with the following VBA Script

Sub DatabaseQuery()

Dim Servername As String
Dim Databasename As String
Dim UserID As String
Dim Password As String
Dim Query As String


Servername = Range("B1").Value
Databasename = Range("B2").Value
UserID = Range("B3").Value
Query = Range("B4").Value

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

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

        objMyConn.ConnectionString = "DRIVER=SQL Server;SERVER=" & Servername & ";UID=" & UserID & ";Trusted_Connection=Yes;APP=Microsoft Office 2013;WSID=LISNBLOM0;DATABASE=" & Databasename & ";"
        objMyConn.Open

        Set objMyCmd.ActiveConnection = objMyConn
        objMyCmd.CommandText = Query
        objMyCmd.CommandType = adCmdText

        Set objMyRecordset.Source = objMyCmd
        objMyRecordset.Open

        Range("A10:P1000").ClearContents
        Range("A10").CopyFromRecordset objMyRecordset

        Set objMyRecordset = Nothing
        objMyConn.Close

End Sub

It works fine for short and not too complexe SQL queries but unfortunately I have to use serveral times the feature with the query in order to concatenate serveral rows for one value.

At that moment the query fails, no error message appears and the column which should contain the concatenate values remains empty and the following columns remain empty too.

Did I forget to adjust something or is there an alternative insteed of using the feature? I also tried to use variables as alternative for this feature but then I got the error message - Run-time error 3704 operation is not allowed the object is closed.

I would be very glad if someone could help me to solve my problem :)!

Kind regards

Related posts

Recent Viewed