Multiple ODBC connections to SQL Databases

[llkhoutx]

I want to create an MS Access pass through query which joins tables in 2 different SQL Server databases, i.e.

SELECT 
a.seller_name, 
a.seller_PAR, 
b.SAP_Cust_PAR
FROM 
a 
LEFT JOIN 
b 
ON 
a.seller_name = b.SAP_Cust_Nm
WHERE 
(a.seller_PAR Is Null Or a.seller_PAR Like ' *') AND (b.SAP_Cust_PAR Is Not Null And b.SAP_Cust_PAR Not Like ' *')

Table "a" (millions of rows) is on one SQL Server database, table "b" (thousands of rows) is on another. My privileges prohibit my creating temporary tables I'm charged with doing this as a pass through query as opposed to using ADO.

Any help would be appreciated.

 

[pbaldy]

The fully qualified method of referring to a table in another database on the same server is:

FROM DatabaseName.Owner.TableName ...

for example:

FROM CarMaint.dbo.CarRecords

 

[llkhoutx]

Thank you for your response.

"a" and "b" are on different servers.

 

[pbaldy]

I think they have to be linked, but the fully-fully qualified syntax is

ServerName.DatabaseName.Owner.TableName