SQL JOINS to External Databases wWITHOUT Links to Current Db

what exactly is the problem? linking the tables first (or copying locally), and then query on those local versions?
not sure what exact problem you are trying to solve, please clarify

Not so much a problem...I could externally link all referenced tables to my current dbase and make this problem go away. This is more a curiosity for me....hoping it can be done through code, but if it can't, i have the option of creating external links.

Ok here's how you do it.

You build two SELECT statements, one to get the data from the first external database, the other from the other.  

You join them in a third SELECT statement by aliasing them.

The code is based on the assumption that the two external database are both Access.  If not, then you'll have a little Googling to do about the "IN" clause in a SELECT statement.

SELECT * FROM 
    (SELECT A.*
    FROM tblExternal1 AS A 
    IN 'C:\Path\TestExternal1.mdb') AS ExternalA
LEFT OUTER JOIN
    (SELECT B.* 
    FROM  tblExternal2 B 
    IN "C:\Path\TestExternal2.mdb") AS ExternalB
 ON ExternalA.External1_ID = ExternalB.External2_ID

Select all Open in new window

>This is more a curiosity for me....hoping it can be done through code,
again: what exactly do you want to do through code, please?

A-Webster.

Definitely on the path here. Having some trouble putting it all together into a code solution. Perhaps you can take a look at my code and advise.

Thank you much!

DoCmd.RunSQL "INSERT INTO tbl_PMS_AllPayors in 'insert mdb pathway' ( Source, State, [Year], ProvID, [MEDICARE #], Payer, Charges, Revenues, [Adjusted RCC], Cost, Profit, market ) SELECT tbl_PMS_AllPayors.Source, tbl_PMS_AllPayors.State, tbl_PMS_AllPayors.Year, tbl_PMS_AllPayors.ProvID, tbl_PMS_AllPayors.[MEDICARE #], tbl_PMS_AllPayors.Payer, tbl_PMS_AllPayors.Charges, tbl_PMS_AllPayors.Revenues, tbl_PMS_AllPayors.[Adjusted RCC], tbl_PMS_AllPayors.Cost, tbl_PMS_AllPayors.Profit, [Market Purchasing Calendar].Market FROM tbl_PMS_AllPayors in 'insert mdb pathway' LEFT JOIN [Market Purchasing Calendar] in 'insert mdb pathway' ON tbl_PMS_AllPayors.ProvID = [Market Purchasing Calendar].[Provider ID];"
 

docmd.runsql"INSERT INTO tbl_PMS_AllPayors in 'insert mdb pathway' ( Source, State, [Year], ProvID, [MEDICARE #], Payer, Charges, Revenues, [Adjusted RCC], Cost, Profit, market )
SELECT tbl_PMS_AllPayors.Source, tbl_PMS_AllPayors.State, tbl_PMS_AllPayors.Year, tbl_PMS_AllPayors.ProvID, tbl_PMS_AllPayors.[MEDICARE #], tbl_PMS_AllPayors.Payer, tbl_PMS_AllPayors.Charges, tbl_PMS_AllPayors.Revenues, tbl_PMS_AllPayors.[Adjusted RCC], tbl_PMS_AllPayors.Cost, tbl_PMS_AllPayors.Profit, [Market Purchasing Calendar].Market
FROM tbl_PMS_AllPayors in 'insert mdb pathway' LEFT JOIN [Market Purchasing Calendar] in 'insert mdb pathway' ON tbl_PMS_AllPayors.ProvID = [Market Purchasing Calendar].[Provider ID];"

Select all Open in new window

aha... and you get a SQL Syntax error, correct?

what about:

docmd.runsql"INSERT INTO tbl_PMS_AllPayors in 'insert mdb pathway' ( Source, State, [Year], ProvID, [MEDICARE #], Payer, Charges, Revenues, [Adjusted RCC], Cost, Profit, market )
SELECT tbl_PMS_AllPayors.Source, tbl_PMS_AllPayors.State, tbl_PMS_AllPayors.Year, tbl_PMS_AllPayors.ProvID, tbl_PMS_AllPayors.[MEDICARE #], tbl_PMS_AllPayors.Payer, tbl_PMS_AllPayors.Charges, tbl_PMS_AllPayors.Revenues, tbl_PMS_AllPayors.[Adjusted RCC], tbl_PMS_AllPayors.Cost, tbl_PMS_AllPayors.Profit, [Market Purchasing Calendar].Market
FROM tbl_PMS_AllPayors in 'insert mdb pathway' LEFT OUTER JOIN [Market Purchasing Calendar] in 'insert mdb pathway' ON ( tbl_PMS_AllPayors.ProvID = [Market Purchasing Calendar].[Provider ID])"

Select all Open in new window

the error I get is with the From clause:

Angelll: i don't see where your code differs from mine. please clarify.

A_Webster...ok....need to play with this to see if I can make it work. looks promising.....

it's LEFT OUTER JOIN
and additional () around the join condition

anyhow, if you reported the error message(s) you get, it might be easier to troubleshoot

Keep to the format I've laid out and you'll be fine.

The code I gave you was copied straight from the query builder in Access.  AngelIII is strictly correct, it's a LEFT OUTER join.  However, in the Access dialect of SQL, LEFT is valid.

Access uses, by default, it's own dialect of SQL that allows for the occasional oddity.  You can configure it to use ANSI-92 SQL Server compatible SQL in Tools, Options, Tables/Queries if you want to be more compliant with standards.

my apologies for the delay..

I am now working with the solution...see if I can put it all together.

works great. Thank you.

Appreciate the assistance from both.