Solved

SQL JOINS to External Databases wWITHOUT Links to Current Db

Posted on 2011-03-08
13
624 Views
Last Modified: 2013-11-29
I am trying to create an append query in VBA SQL that performs a LEFT JOIN on 2 tables that are not externally linked to the db running the code.

Running into trouble with my FROM clause.

hoping i can have syntax along the lines of:

FROM Table1 in 'pathway to table1 mdb' LEFT JOIN Table2 in 'path to table2 mdb'  ON Table1.Field=Table2.Field

Is it possible to generate SQL that performs joins on tables that are not externally linked???


I appreciate the help.

0
Comment
Question by:tscott_72
  • 6
  • 4
  • 3
13 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35072445
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
0
 

Author Comment

by:tscott_72
ID: 35072501
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.

0
 
LVL 8

Expert Comment

by:Andrew_Webster
ID: 35074014
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

Open in new window

0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35082346
>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?
0
 

Author Comment

by:tscott_72
ID: 35083735
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!
0
 

Author Comment

by:tscott_72
ID: 35083766
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];"

Open in new window

0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35084162
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])"

Open in new window

0
 
LVL 8

Accepted Solution

by:
Andrew_Webster earned 250 total points
ID: 35084168
Ok.  I had a fiddle with my sample databases, and the following SQL runs.
INSERT INTO tblExternal1_Target 
    ( 
    External1_TargetNumber, 
    External1_TargetText 
    ) 
IN 'C:\Path\TestExternal1.mdb'
SELECT 
    ExternalA.External1_ID, 
    ExternalB.External2_Data
FROM 
    [
    SELECT 
        A.*
    FROM tblExternal1 AS A 
    IN 'C:\Path\TestExternal1.mdb'
    ]. AS ExternalA 
LEFT JOIN 
    [
    SELECT 
        B.* 
    FROM tblExternal2 AS B 
    IN 'C:\Path\TestExternal2.mdb'
    ]. AS ExternalB 
ON 
    ExternalA.External1_ID = ExternalB.External2_ID;

Open in new window


Stick than into a DoCmd.RunSQL (or at least your version with your paths, databases, tables, and fields) and see what happens.
0
 

Author Comment

by:tscott_72
ID: 35084294
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.....
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35084444
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
0
 
LVL 8

Expert Comment

by:Andrew_Webster
ID: 35084643
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.
0
 

Author Comment

by:tscott_72
ID: 35139004
my apologies for the delay..

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

Author Comment

by:tscott_72
ID: 35150453
works great. Thank you.

Appreciate the assistance from both.
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now