This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.
COURSE of the MONTH
14 days, 18 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
SQL JOINS to External Databases wWITHOUT Links to Current Db
Posted on 2011-03-08
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.
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.
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
6
4
3
13 Comments
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 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.
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
>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?
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!
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.[MEDICAR
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];"
aha... and you get a SQL Syntax error, correct?
what about:
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])"
Ok. I had a fiddle with my sample databases, and the following SQL runs.
Stick than into a DoCmd.RunSQL (or at least your version with your paths, databases, tables, and fields) and see what happens.
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;
Stick than into a DoCmd.RunSQL (or at least your version with your paths, databases, tables, and fields) and see what happens.
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.....
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
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.
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.
Featured Post
To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s
ManageEngine
webinar, where attendees received a comprehensive look at the ma…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen.
Visualize your data! ... really see it
To use the code to create a calendar from a q…
Suggested Courses
Course of the Month14 days, 18 hours left to enroll
771 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.