MS Access Pass through to 2 SQL Server DBs within one SQL Statemen

[Guest]

Hi Hope you can help

i have a pass thorugh query in access that reads results from some tables in
a sql server db (SSERVA) tables tbla1 tbla2 tbla3 the problem is that i need
to sub select data in another sql server db as well (SSERVB) with a join to
(SSERVB) tblb1 which subselects data via a join in sserva tbla5

in SQL Server the query is just sservb.dbo.tblb1 but this doesnt work for a
passthrough

example of structure
select col1.tbla1
right join col1.tbla2
right join col1.tbla3
right join sservb.dbo.col1.tblb1
right join col1.tbla5

any help appreciated

Dann

 

[Tom Ellison]

Dear Dann:

Are you saying you have this working in QA but it then won't work as a pass
thru?

I strongly recommend you do the work in QA first, so you know it's a
pass-thru problem. I had though pass-thru would not have this problem, but
I may have something to learn.

Can you just write a simple query referencing the alternate database? Is
this alternate database in the same instance of SQL Server? I'm quite sure
that there must be one instance of SQL server that is aware of all the
tables being used, so you must link to it in the server to which you're
connected if it is on another instance for this to work. You cannot just
connect to both and expect them to work together on it. You must choose a
single server to run the query and make that server aware of the other
table(s) you need.

As you can see from my guesses about what might be wrong, we may need more
information to see what is going on with this.

Tom Ellison

 

[Michel Walsh]

Hi,


A standard SELECT SQL statement requires a FROM clause and standard joins
require ON clause. Furthermore, the two parts syntax is
tableName-dot-ColumnName, not ColumnName-dot-tableName. The closest thing is
see is NATURAL JOIN, but I don't think MS SQL Server supports those.

------------------------
USE PUBS
GO

SELECT au_id.authors RIGHT JOIN au_id.titleauthor
GO
------------------------

to be a simpler formulation of your syntax just errs (incorrect syntax near
the keyword 'RIGHT').

Can you supply an example that works in Pubs?



Hoping it may help,
Vanderghast, Access MVP