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 :)!