Union 2 recordsets

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have an Access 97 database developed by someone else that I'm trying to
modify. What I need is to get 2 recordsets from 2 different (but identical)
oracle systems. Then I need to create 1 "union" recordset of the results.
sngSStart and sngSEnd are unit numbers being passed to the procedure to
check if they are currently being used by anyone.

Here is what I have so far but I'm struggling with the final union statement
(if it is even possible).

************************************************
'1. Connect to Production Servers
Set conn1 = New ADODB.Connection
With conn1
.ConnectionString = "DSN=LOCATION1"
.Open
End With

Set conn2 = New ADODB.Connection
With conn2
.ConnectionString = "DSN=LOCATION2"
.Open
End With

'2. Get a recordset of the Range
Set res1 = New ADODB.Recordset
Set res1.ActiveConnection = conn
res.CursorLocation = adUseClient

Set res2 = New ADODB.Recordset
Set res2.ActiveConnection = conn2
res2.CursorLocation = adUseClient

varSQL = "SELECT UNIT_ID, CONTRACT_ID, ORDER_DATE,'LOCATION1'AS LOCATION
FROM UNITS" _
& "WHERE UNIT_ID BETWEEN " & sngSStart & " AND " & sngSEnd

varSQL2 = "SELECT UNIT_ID, CONTRACT_ID, COLL_DATE,'LOCATION2' AS LOCATION
FROM UNITS " _
& "WHERE UNIT_ID BETWEEN " & sngSStart & " AND " & sngSEnd

res1.Open varSQL, , adOpenKeyset
res2.Open varSQL2, , adOpenKeyset
*****************************************

What the rest of the procedure needs to do from the original (essentially
return a list of number ranges still being used in the system):

******************************************
'3. If there are records do the following record found
If res.RecordCount <> 0 Then

res.MoveFirst
res.MoveLast

ReDim strArray(1 To res.RecordCount, 1 To res.Fields.Count)

sngCount = 1
res.MoveFirst
Do Until res.EOF
'3.1. Get the UnitID, Contract ID, and the Database the information was
found
strArray(sngCount, 1) = res![UNIT_ID]
strArray(sngCount, 2) = res![Contract_ID]
strArray(sngCount, 3) = res![COLL_DATE]
strArray(sngCount, 4) = res![Location]
res.MoveNext
sngCount = sngCount + 1
Loop
SystemCheck = "Yes"
Call WordData(strArray, "SystemName", sngSStart & " - " & sngSEnd)
Else
SystemCheck = "No"
End If
***********************************

Sorry for such a long message but this is a rather urgent big issue to solve
and I have to make due with what is already in place!

Thanks for any help
LeAnn
 
AFAIK, it's not possible to union together two recordsets. However, since
you're storing the data into an array, can't you just dimension the array to
ReDim strArray(1 To (res1.RecordCount + res2.RecordCount), 1 To
res1.Fields.Count)?
 
I was wondering about putting it in an array but I'm not as knowledgeable
about those. I'll give it a try, that just may work!

Thanks so much!



Douglas J. Steele said:
AFAIK, it's not possible to union together two recordsets. However, since
you're storing the data into an array, can't you just dimension the array to
ReDim strArray(1 To (res1.RecordCount + res2.RecordCount), 1 To
res1.Fields.Count)?


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



LeAnn said:
I have an Access 97 database developed by someone else that I'm trying to
modify. What I need is to get 2 recordsets from 2 different (but
identical)
oracle systems. Then I need to create 1 "union" recordset of the results.
sngSStart and sngSEnd are unit numbers being passed to the procedure to
check if they are currently being used by anyone.

Here is what I have so far but I'm struggling with the final union
statement
(if it is even possible).

************************************************
'1. Connect to Production Servers
Set conn1 = New ADODB.Connection
With conn1
.ConnectionString = "DSN=LOCATION1"
.Open
End With

Set conn2 = New ADODB.Connection
With conn2
.ConnectionString = "DSN=LOCATION2"
.Open
End With

'2. Get a recordset of the Range
Set res1 = New ADODB.Recordset
Set res1.ActiveConnection = conn
res.CursorLocation = adUseClient

Set res2 = New ADODB.Recordset
Set res2.ActiveConnection = conn2
res2.CursorLocation = adUseClient

varSQL = "SELECT UNIT_ID, CONTRACT_ID, ORDER_DATE,'LOCATION1'AS LOCATION
FROM UNITS" _
& "WHERE UNIT_ID BETWEEN " & sngSStart & " AND " & sngSEnd

varSQL2 = "SELECT UNIT_ID, CONTRACT_ID, COLL_DATE,'LOCATION2' AS LOCATION
FROM UNITS " _
& "WHERE UNIT_ID BETWEEN " & sngSStart & " AND " & sngSEnd

res1.Open varSQL, , adOpenKeyset
res2.Open varSQL2, , adOpenKeyset
*****************************************

What the rest of the procedure needs to do from the original (essentially
return a list of number ranges still being used in the system):

******************************************
'3. If there are records do the following record found
If res.RecordCount <> 0 Then

res.MoveFirst
res.MoveLast

ReDim strArray(1 To res.RecordCount, 1 To res.Fields.Count)

sngCount = 1
res.MoveFirst
Do Until res.EOF
'3.1. Get the UnitID, Contract ID, and the Database the information was
found
strArray(sngCount, 1) = res![UNIT_ID]
strArray(sngCount, 2) = res![Contract_ID]
strArray(sngCount, 3) = res![COLL_DATE]
strArray(sngCount, 4) = res![Location]
res.MoveNext
sngCount = sngCount + 1
Loop
SystemCheck = "Yes"
Call WordData(strArray, "SystemName", sngSStart & " - " & sngSEnd)
Else
SystemCheck = "No"
End If
***********************************

Sorry for such a long message but this is a rather urgent big issue to
solve
and I have to make due with what is already in place!

Thanks for any help
LeAnn
Click to expand...
Click to expand...
 
Back
Top