Help using a union join on a SQL DB and an Access DB

[Mike Fellows]

Help,

im trying to use a union join from an SQL database and a Access database -
the program works fine from my own machine as i have the odbc DSN
connections setup
from access to link to the SQL tables.

i need to set this up in code to connect to both databases and do my union
join but i have no idea how to

below is my current code (using the DSN connection):

Dim strConnect As String = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=\\Gringotts\Common Documents\Master Database\OwnAndSave.mdb;" &
_
"Persist Security Info=False"
Dim RowCount As Integer = 0

Dim ocon1 As OleDbConnection = New OleDbConnection(strConnect)
ocon1.Open()
Dim ds1 As DataSet = New DataSet()
Dim da1 As OleDbDataAdapter = New OleDbDataAdapter()
da1.SelectCommand = New OleDbCommand("SELECT Personal.ID, Personal.Surname1,
Personal.Postcode, Personal.Town, LiveTracker.SalesAppointment,
LiveTracker.SalesManager FROM (Personal LEFT JOIN LiveTracker ON Personal.ID
= LiveTracker.ID) LEFT JOIN Closed ON Personal.ID = Closed.ID WHERE
(((LiveTracker.SalesAppointment) Between Date() And Date()+28) AND
((LiveTracker.PostalPackReturned)=#1/1/1900#) AND ((Closed.ID) Is Null))
UNION SELECT dbo_Personal.ID, dbo_Personal.surname1, dbo_Personal.postcode1,
dbo_Personal.town1, dbo_LiveTracker.SalesAppointment,
dbo_LiveTracker.SalesManager FROM (dbo_Personal INNER JOIN dbo_LiveTracker
ON dbo_Personal.ID = dbo_LiveTracker.ID) LEFT JOIN dbo_Closed ON
dbo_Personal.ID = dbo_Closed.ID WHERE
(((dbo_LiveTracker.SalesAppointment)>Date()) AND
((dbo_LiveTracker.PostalPackReturned)=#1/1/1900#) AND ((dbo_Closed.ID) Is
Null));", ocon1)
da1.Fill(ds1)

any help would be greatly appreciated

Thanks

Mike Fellows

 

[Mary Chipman]

You're going to need to perform the union query on the Access client,
not on SQL Server. I'd recommend creating a pass-through query in the
Access mdb that selects from the SQLS tables, and then union your
local Jet query to that.

--mary