Help with SQL string using a union join into a dataset

[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

[Cor Ligthert]

Hi Mike,

I think a Join question is typical a question for Miha, the two Bills, Kevin
or others who are often active in the newsgroup

Adonet
<news://msnews.microsoft.com/microsoft.public.dotnet.framework.adonet>

Web interface:

<http://communities2.microsoft.com/co...s/?dg=microsof
t.public.dotnet.framework.adonet>

I thought that there is a bug with the Join in OleDb with an access
database.

However try it in the adonet group, although a lot of us know the answer I
think, I think there you have a better change.

Cor

[Ken Tucker [MVP]]

Hi,

Here is a similar stored procedure I used. Maybe this will help

ALTER Procedure ByCategory
@Start DateTime,
@Finish DateTime
AS Select PriceCategory.CategoryName,
SUM(InvoiceData.Each) as TotalPrice
From ((InvoiceData Inner Join Invoices ON Invoices.InvoiceID =
InvoiceData.InvoiceID)
Inner Join PriceList ON PriceList.Description LIKE InvoiceData.Description)
Inner Join PriceCategory ON PriceList.Category = PriceCategory.CategoryID
Where Invoices.BillDate > @Start AND Invoices.BillDate < @Finish
Group by PriceCategory.CategoryName

Ken
----------------------------
"Mike Fellows" <mi***************@equityhouse.co.uk> wrote in message
news:EK***********@newsfe2-gui.server.ntli.net...

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

[Mike Fellows]

the SQL Side is not a problem i can easily create the stored procedure

i can also get the data from access easily

its gettign it from both at the same time which i am having problems with
( i need them in a single sorted dataset)

Mike
"Ken Tucker [MVP]" <vb***@bellsouth.net> wrote in message
news:Oe**************@tk2msftngp13.phx.gbl...

Hi,

Here is a similar stored procedure I used. Maybe this will help

ALTER Procedure ByCategory
@Start DateTime,
@Finish DateTime
AS Select PriceCategory.CategoryName,
SUM(InvoiceData.Each) as TotalPrice
From ((InvoiceData Inner Join Invoices ON Invoices.InvoiceID =
InvoiceData.InvoiceID)
Inner Join PriceList ON PriceList.Description LIKE InvoiceData.Description) Inner Join PriceCategory ON PriceList.Category = PriceCategory.CategoryID
Where Invoices.BillDate > @Start AND Invoices.BillDate < @Finish
Group by PriceCategory.CategoryName

Ken
----------------------------
"Mike Fellows" <mi***************@equityhouse.co.uk> wrote in message
news:EK***********@newsfe2-gui.server.ntli.net...

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