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

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

On Fri, 21 May 2004 10:31:44 +0100, "Mike Fellows"
<(E-Mail Removed)> wrote:

>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
>
>