conversion from Access query to mssql query

Hello Guys

I am chaging the connectivity of MSaccess2K to sqlserver
the code is written in vb editor of access
i have established the connection string
but the following query is generating error of
invalid object name

strSQL = SELECT DISTINCT [Sites Union Controls].Description,
[Sites Union Controls].[Rous Reportable Site], [Sites Union Controls].[Type], Samples.SequenceNumber FROM Jobs INNER JOIN ([Sites Union Controls] INNER JOIN Samples ON [Sites Union Controls].SiteSerial = Samples.SiteSerial) ON Jobs.JobSerial = Samples.JobSerial
WHERE ((Jobs.JobSerial) = " & intJobSerial & ") ORDER BY Samples.SequenceNumber

I am getting an error invalid object name sites union controls

can't we do union of two tables as above in mssql
Please Help

Thanks In AdvanceI'm sure some one will correct me if Im wrong but I dont think this query will ever work in SQL Server, it does look like something that might run in access though.

Ive had problems like this before, Access loves adding in brackets () that just get in the way and confuse things in SQL Server. It also seams to list all the tables then join them in the FROM statement which is not s SQL thing either.

I think your also going to have a problem with the WHERE clause, namely the part " & intJobSerial & " reefers to a variable in Access. Even if you have created the variable in SQL Server the syntax is still wrong

Your query doesnt look like a union query, but a standard select query gone a bit wrong in the FROM part. Your query should look something like

DECLARE @.intJobSerial VARCHAR(50) -- this creates the variable as a 50 character text field, don't need this bit if youve done it already

SET @.intJobSerial = 'XXX' -- this sets the variable to XXX, don't need this bit if youve done it already

SELECT DISTINCT [Sites Union Controls].[Description],[Sites Union Controls].[Rous Reportable Site], [Sites Union Controls].Type, Samples.SequenceNumber

FROM [Sites Union Controls]
INNER JOIN Samples
ON [Sites Union Controls].SiteSerial = Samples.SiteSerial
INNER JOIN Jobs
ON Jobs.JobSerial = Samples.JobSerial

WHERE Jobs.JobSerial = @.intJobSerial

ORDER BY Samples.SequenceNumber

The invalid object sites union controls in actually the table used the query, Im guessing its because the FROM Part was all messed up at it was the first thing it came across after it went wrong.

Hope this helps|||Hello,

Trying by used the next name Sites_Union_Controls because I'm not sure that you can have a name with blanc characters

Good luck

Sylvie

Quote:

Originally Posted by aakash

Hello Guys

I am chaging the connectivity of MSaccess2K to sqlserver
the code is written in vb editor of access
i have established the connection string
but the following query is generating error of
invalid object name

strSQL = SELECT DISTINCT [Sites Union Controls].Description,
[Sites Union Controls].[Rous Reportable Site], [Sites Union Controls].[Type], Samples.SequenceNumber FROM Jobs INNER JOIN ([Sites Union Controls] INNER JOIN Samples ON [Sites Union Controls].SiteSerial = Samples.SiteSerial) ON Jobs.JobSerial = Samples.JobSerial
WHERE ((Jobs.JobSerial) = " & intJobSerial & ") ORDER BY Samples.SequenceNumber

I am getting an error invalid object name sites union controls

can't we do union of two tables as above in mssql
Please Help

Thanks In Advance

|||Hey, a couple of points...
SQL uses + for concatenation so you need to change your where statement to
((Jobs.JobSerial) = " + @.intJobSerial + ")

JohnK is right, if intJobSerial is a local variable then it needs to be declared and it must begin with an @..

There is nothing wrong with the FROM statement, it's a little unusual to delay the two ON clauses at the end but this gives a different result set because nulls in the 3rd table, in your query SAMPLES, can be handled differently this way. It's more effective if your mixing Left Outer and Inner Joins, however.

The biggest thing I see is that the permissions on the SQL table [Sites Union Controls] may be different than what you expect. You should probably be using a full reference to it as [database].[owner].[table] to at least eliminate the possibility that your error is a security setup problem.

Tom|||Sites Union Controls, was query in old ms access project which was connected to ms access database i am changing connectivity to ms sql 2000 ,none of the above solutions seem to be working ,
please help