Multiple SQL query ouput in one Worksheet

Question

Hi,

please I need your support for if I want to apply multiple SQL query, I ask how to add the below SQL Queries to the code to be run where through below code I'm able to get output of one/ single SELECT query output with column header. Now i want to display more than one SELECT query output in the same worksheet.

Sub ConnectSqlServer()

Dim conn As ADODB.Connection

Dim rs As ADODB.Recordset

Dim sConnString As String

' Create the connection string.

Set rngStartDate = Range("G27") '<<adjust (and it would bea good idea to include the sheet)

Set rngEndDate = Range("G28") '<<adjust (and it would bea good idea to include the sheet)

FromStr = Join(Array(Year(rngStartDate), Month(rngStartDate), Day(rngStartDate), Hour(rngStartDate), Minute(rngStartDate), 0, 0), ",")

ToStr = Join(Array(Year(rngEndDate), Month(rngEndDate), Day(rngEndDate), Hour(rngEndDate), Minute(rngEndDate), 0, 0), ",")

sConnString = "Provider=SQLOLEDB;Data Source=COMP1\SERVER1;" & _

              "Initial Catalog=EGCAHCSGALT352\SQLEXPRESS;" & _

              "Integrated Security=SSPI;"

' Create the Connection and Recordset objects.

Set conn = New ADODB.Connection

Set rs = New ADODB.Recordset

' Open the connection and execute.

conn.Open sConnString

Set rs = conn.Execute("select * into test1 from [All VDN Calls]" & _

                      "where" & _

                      "DATETIMEFROMPARTS([All VDN Calls].year, [All VDN Calls].[New Month], [All VDN Calls].Day, [All VDN Calls].Hour, [All VDN Calls].Min, 0, 0)>=DATETIMEFROMPARTS(" & FromStr & ")" & _

                      "AND" & _

                      "DATETIMEFROMPARTS([All VDN Calls].year, [All VDN Calls].[New Month], [All VDN Calls].Day, [All VDN Calls].Hour, [All VDN Calls].Min, 0, 0)<=DATETIMEFROMPARTS(" & ToStr & ")" & _

                      "AND" & _

                      "VDN = 'SA_New_ar'" & _

                      ";")

' Check we have data.

If Not rs.EOF Then

  ' Transfer result.

 Sheets(1).Range("A1").CopyFromRecordset rs

  ' Close the recordset

 rs.Close

Else

  MsgBox "Error: No records returned.", vbCritical

End If

' Clean up

If CBool(conn.State And adStateOpen) Then conn.Close

Set conn = Nothing

Set rs = Nothing

End Sub

the queries which I want to add

1st

select count(outage_with_detection) from customer_age where call_date >= Start date time

                                                            AND

                                                            call_date <= End date time;

2nd

drop table test1;

select * into test1 from [All VDN Calls]

            where

                  DATETIMEFROMPARTS([All VDN Calls].year, [All VDN Calls].[New Month], [All VDN Calls].Day, [All VDN Calls].Hour, [All VDN Calls].Min, 0, 0)>= Start date time

                  AND

                  DATETIMEFROMPARTS([All VDN Calls].year, [All VDN Calls].[New Month], [All VDN Calls].Day, [All VDN Calls].Hour, [All VDN Calls].Min, 0, 0)<= End date time

                  AND

                  VDN = 'SA_New_ar'

                   ;

select count(*)  from calls_witin_tool_outage

            cross join test1 where

                    DATETIMEFROMPARTS(calls_witin_tool_outage.year, calls_witin_tool_outage.[New Month], calls_witin_tool_outage.Day, calls_witin_tool_outage.Hour, calls_witin_tool_outage.Min, 0, 0)

                    >=

                    DATEADD(HOUR,-24,DATETIMEFROMPARTS(test1.year, test1.[New Month], test1.Day, test1.Hour, test1.Min, 0, 0))

                    AND

                    DATETIMEFROMPARTS(calls_witin_tool_outage.year, calls_witin_tool_outage.[New Month], calls_witin_tool_outage.Day, calls_witin_tool_outage.Hour, calls_witin_tool_outage.Min, 0, 0)

                    <=

                    DATEADD(MINUTE,-1,DATETIMEFROMPARTS(test1.year, test1.[New Month], test1.Day, test1.Hour, test1.Min, 0, 0))

                    AND

                    calls_witin_tool_outage.VDN = 'SA_New_ar'

                   AND

                    calls_witin_tool_outage.[Collected digits]= test1.[Collected digits]

                    ;

thanks a lot,

Answer 1

The part of the macro that needs to be repeated with the different query is

Set rs = conn.Execute(thequery)

' Check we have data.

If Not rs.EOF Then

  ' Transfer result.

 Sheets(1).Range(destination).CopyFromRecordset rs

  ' Close the recordset

 Else

  MsgBox "Error: No records returned.", vbCritical

End If

 rs.Close

...where you would replace thequery with the query string and you would replace destination with the address at which you would want

the query result to be copied.