PDA

View Full Version : Union Query in VBA



mady
07-18-2007, 05:00 PM
Sup ppl.

So I have a little problem and I cant seem to find the answer.

I have a SQL query that works just fine:


SELECT parentid,convert(CHAR(10),calldatetime,110)as Ddate, crc,
count(crc) As total
from history
where history.calldatetime > '1/1/2007'
group by convert(CHAR(10),calldatetime,110), parentid, crc

Union
SELECT parentid,convert(CHAR(10),calldatetime,110)as Ddate, crc,
count(crc) As total
from historyarchive
where historyarchive.calldatetime > '1/1/2007'
group by convert(CHAR(10),calldatetime,110), parentid, crc
order by parentid , convert(CHAR(10),calldatetime,110)


and I need this in VBA , however I'm having trouble with the "Union" command and this code will give me data for only the first part of the query .


Sub AGRsqlDATA()
''' U need Microsoft ActiveX Data Objects Library

'Sales Adodb connction hit server1.
Dim conn1 As ADODB.Connection
Dim rst1 As ADODB.Recordset
Dim Nsql1 As String
Dim i1 As Integer

Application.ScreenUpdating = False

' Create the Connection object.
Set conn1 = New ADODB.Connection

On Error Resume Next
With conn1
'Assign the connection string to the connection object.
.ConnectionString = "DRIVER={SQL Server};SERVER=tssql;UID=sa;" & _
"PWD=;DATABASE=touchstar"

' Open the connection.
.Open strConn1
End With

Nsql1 = "SELECT parentid,convert(CHAR(10),calldatetime,110)as Ddate, crc, " & _
"count(crc)as total " & _
"from history " & _
"where history.calldatetime > '1/1/2007' " & _
"group by convert(CHAR(10),calldatetime,110), parentid, crc " & _
"Union " & _
"SELECT parentid,convert(CHAR(10),calldatetime,110)as Ddate, crc, " & _
"count(crc) as total " & _
"from historyarchive " & _
"where historyarchive.calldatetime > '1/1/2007' " & _
"group by convert(CHAR(10),calldatetime,110), parentid, crc " & _
"order by parentid , convert(CHAR(10),calldatetime,110)"

' Create a new Recordset Object.
Set rst1 = New ADODB.Recordset

With rst1
' Connect this recordset to the previously opened connection.
.ActiveConnection = conn1

' Retrieve all records from the table.
.Open Nsql1, conn1, adOpenDynamic, adLockBatchOptimistic
End With

' Loop through all of the fields, returning the field names to the worksheet.
For i1 = 0 To rst1.Fields.count - 1
ActiveWorkbook.Sheets("sheet2").Range("A2").Offset(0, i1).Value = _
rst1.Fields(i1).Name
Next i1

' Copy the recordset to the new worksheet.
ActiveWorkbook.Sheets("sheet2").Range("A3").CopyFromRecordset rst1

' Close the recordset.
Set rst1 = Nothing

' Close the Connection.
conn1.Close
End Sub


Help would be greatly appreciated : pray2:

Edited 20-Jul-07 by geekgirlau. Reason: insert line breaks

rory
07-19-2007, 02:12 AM
Hi,
A couple of questions if I may:
1. Which ADO library are you using?
2. I take it you know that the UNION query will automatically perform a DISTINCT operation on the combined data and that that is not your issues? (If it is, you need to use UNION ALL)
Regards,
Rory

mady
07-19-2007, 06:09 AM
The issue is that for some reason when I paste the query into my VBA code (the query works fine on the SQL server) the 'UNION' is completely disregarded and I get only data from one table.

rory
07-19-2007, 06:31 AM
And which version of ADO? Do you get the same results with this:


Dim conn1 As ADODB.Connection
Dim rst1 As ADODB.Recordset
Dim Nsql1 As String
Dim i1 As Integer

Application.ScreenUpdating = False

' Create the Connection object.
Set conn1 = New ADODB.Connection

'On Error Resume Next
With conn1
'Assign the connection string to the connection object.
.ConnectionString = "DRIVER={SQL Server};SERVER=tssql;UID=sa;" & _
"PWD=;DATABASE=touchstar"

' Open the connection.
.Open
End With

Nsql1 = "SELECT parentid,convert(CHAR(10),calldatetime,110) as Ddate, crc, " & _
"count(crc) as total " & _
"from history " & _
"where history.calldatetime > '1/1/2007' " & _
"group by convert(CHAR(10),calldatetime,110), parentid, crc " & _
"Union " & _
"SELECT parentid,convert(CHAR(10),calldatetime,110) as Ddate, crc, " & _
"count(crc) as total " & _
"from historyarchive " & _
"where historyarchive.calldatetime > '1/1/2007' " & _
"group by convert(CHAR(10),calldatetime,110), parentid, crc " & _
"order by parentid, convert(CHAR(10),calldatetime,110)"

' Create a new Recordset Object.
Set rst1 = New ADODB.Recordset

With rst1
' Connect this recordset to the previously opened connection.
' Retrieve all records from the table.
.Open Nsql1, conn1, adOpenDynamic, adLockBatchOptimistic, adCmdText
End With

' Loop through all of the fields, returning the field names to the worksheet.
For i1 = 0 To rst1.Fields.count - 1
ActiveWorkbook.Sheets("sheet2").Range("A2").Offset(0, i1).Value = _
rst1.Fields(i1).Name
Next i1

' Copy the recordset to the new worksheet.
ActiveWorkbook.Sheets("sheet2").Range("A3").CopyFromRecordset rst1

' Close the recordset.
Set rst1 = Nothing

' Close the Connection.
conn1.Close

?

Edited 20-Jul-07 by geekgirlau. Reason: insert line breaks

Norie
07-19-2007, 09:15 AM
Try using UNION ALL.

mady
07-19-2007, 01:58 PM
Union is not the problem and yes for the heck of it I did try Union All
I have spend 8 hours today to figure out why excel is unable to grab this data with no luck. I took the query and code peace by piece and I understand where the problem is just cant comprehend why that is and what the fix should be.

1: Query it self is correct and returns correct data in SQL 2000 (tested in query analyzer)
2: Query placed into VBA (for reporting purposes) as is, returns no data or incorrect data
3:I think the problem is with the "Convert" datetime command for some reason excel will grab extra CHAR, why that is I have no idea.
4: I have checked both SQL tables and they are exactly the same
5: I have run out of ideas and I'm going home disgusted with excel
6: ps the Library is 2.8

geekgirlau
07-19-2007, 07:08 PM
Why not change the approach and run it as 2 separate queries? I know this doesn't answer the question of why but you will at least get your result.

mady
07-20-2007, 08:11 AM
So I'm going with server performance issues and I'm sticking to it.

And yes I can run these separate but since I’m hardheaded, I really wanted to know what the problem is so I can adjust for it later.

PS. Thanks 4 looking over this.

Tommy
07-20-2007, 11:57 AM
Hi mady,

I would enter the table names and make the totals seperate. I also added spaces after the comma, I think geekgirlau added a few also when the SQL was broken to multiple lines

Nsql1 = "SELECT history.parentid, convert(CHAR(10), history.calldatetime,110) as Ddate, history.crc, " & _
"count(history.crc) as totalHist " & _
"from history " & _
"where history.calldatetime > '1/1/2007' " & _
"group by convert(CHAR(10), history.calldatetime,110), history.parentid, history.crc " & _
"Union " & _
"SELECT historyarchive.parentid, convert(CHAR(10), historyarchive.calldatetime,110) as Ddate, historyarchive.crc, " & _
"count(historyarchive.crc) as totalHistArch " & _
"from historyarchive " & _
"where historyarchive.calldatetime > '1/1/2007' " & _
"group by convert(CHAR(10), historyarchive.calldatetime,110), historyarchive.parentid, historyarchive.crc " & _
"order by historyarchive.parentid, convert(CHAR(10), historyarchive.calldatetime,110)"

rory
07-23-2007, 08:23 AM
mady,
I have tried your syntax using ADO 2.6 against SQL server 2000 and it works fine, though I had to comment out the strConn1 in this line:
.Open strConn1
to get the code to compile.
FWIW
Rory

mady
08-01-2007, 09:41 AM
I thought I would share what the problem solution was regarding this whole issue!

All the code here is correct, VBA and SQL however I was missing one important peace of line in my code.

conn1.ConnectionTimeout = 0

This line of code will prevent the actual time out on the server (since default is set to 30000mil.sec, entering 0 will make this query run till its done or stopped by user)


Thanks for all the suggestions

Mady

Bob Phillips
08-01-2007, 10:43 AM
I would suggest you find a more meaningful time. Setting Timeout to 0 means it will run whatever, masking real problems. With a bit of experimentation, you should be able tgo come up with a value that doesn't go on regardless, but is bifg enough for your query.

mady
08-01-2007, 12:33 PM
I would suggest you find a more meaningful time. Setting Timeout to 0 means it will run whatever, masking real problems. With a bit of experimentation, you should be able tgo come up with a value that doesn't go on regardless, but is bifg enough for your query.


Good point, I made the following adjustments considering your advice

conn1.ConnectionTimeout = 120
&
conn1.CommandTimeout = 120

Thanks again :thumb