Using UNION SELECT...

Results 1 to 2 of 2

Thread: Using UNION SELECT...

  1. #1
    dortiz@uwginc.com Guest

    Default Using UNION SELECT...

    Can somebody help with my coding...I was trying to use Union Select to combine recordsets from five access databases on the server all containing a table called Clients and all with the same fields...(there is a seperate database for each location but now I need to combine them to create a report sorting them by last name...)<BR>*<BR>My code is basically this...<BR>*<BR>set loc1Conn = Server.CreateObject("ADODB.Connection")<BR>loc1Con n.Open "loc1dsn"<BR>SQLQuery1 = "SELECT mmm,first,last,project,village,location FROM CLIENTS "<BR>SQLQuery1 = SQLQuery1 & "where project like &#039%%" & projectname & "%%&#039 "<BR>*<BR>set loc2Conn = Server.CreateObject("ADODB.Connection")<BR>loc2Con n.Open "loc1dsn"<BR>SQLQuery1 = SQLQuery1 & "UNION SELECT mmm,first,last,project,village,location FROM CLIENTS "<BR>SQLQuery1 = SQLQuery1 & "where project like &#039%%" & projectname & "%%&#039 "<BR>*<BR>set loc5Conn = Server.CreateObject("ADODB.Connection")<BR>loc5Con n.Open "loc5dsn"<BR>SQLQuery1 = SQLQuery1 & "UNION SELECT mmm,first,last,project,village,location FROM CLIENTS "<BR>SQLQuery1 = SQLQuery1 & "where project like &#039%%" & projectname & "%%&#039 " <BR>SQLQuery1 = SQLQuery1 & "order by last, first" <BR>Set RSCustomerList5 = loc5Conn.Execute(SQLQuery1)<BR>*<BR>*<BR>Display ing it in a table...<BR>*<BR>&#060;%Do While Not RScustomerList5.EOF &#037;&#062;<BR>&#060;tr><BR>&#060;td BGCOLOR="#EEE1A6" ALIGN="left" bordercolordark="#000000" bordercolor="#000000"<BR>bordercolorlight="#000000 " height="5" rowspan="2">&#060;font face="Arial" color="#000000"<BR>size="2">&#060;%= RScustomerList5("MMM") & " "&#037;&#062; &#060;%= RScustomerList5("FIRST") & " "&#037;&#062; &#060;%= RScustomerList5("LAST") &#037;&#062; &#060;/font>&#060;p align="right">&nbsp;&#060;/td><BR>&#060;td BGCOLOR="#EEE1A6" ALIGN="LEFT" bordercolordark="#000000" bordercolor="#000000"<BR>bordercolorlight="#000000 " height="5">&#060;font face="Arial" color="#000000" size="2">&#060;%= RScustomerList5("project")&#037;&#062; &#060;/font>&#060;/td><BR>&#060;td BGCOLOR="#EEE1A6" ALIGN="LEFT" bordercolordark="#000000" bordercolor="#000000"<BR>bordercolorlight="#000000 " height="5">&#060;font face="Arial" color="#000000" size="2">&#060;%= RScustomerList5("village")&#037;&#062; &#060;/font>&#060;/td><BR>&#060;td BGCOLOR="#EEE1A6" ALIGN="LEFT" bordercolordark="#000000" bordercolor="#000000"<BR>bordercolorlight="#000000 " height="5">&#060;font face="Arial" color="#000000" size="2">&#060;%= RScustomerList5("Location")&#037;&#062; &#060;/font>&#060;/td><BR>&#060;/tr><BR>&#060;%<BR>RScustomerList5.MoveNext<BR>Loop <BR>&#037;&#062;<BR>*

  2. #2
    Join Date
    Dec 1969
    Posts
    2,849

    Default RE: Using UNION SELECT...

    I could be incorrect, but I really think it&#039s impossible to use a UNION SELECT to collate information from multiple databases, at least through ADO.<BR><BR>You can create 5 separate recordset objects, each recordset object holding the results from each of the 5 databases. Have you tried it this way?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •