Link to home
Start Free TrialLog in
Avatar of PumpMeister
PumpMeister

asked on

SQL UPDATE w/ JOIN to external DB?

I need to implement a rudimentary change tracking scheme for three tables in a database that is backed up daily.  Normally, I would track changes via a data entry form, but in this database some of the data is put into the tables by a 3rd party add-in to AutoCAD.  Thus, some of the data entry is outside my control and ability to track it.

Because of this, I am planning to implement a change tracking scheme by comparing the data in todays database tables to those in yesterday's back up.  Records that are detected to be changed from yesterday will be marked by putting today's date in a Rev_Date field.

I am looking at how to do this, and trying to work up the automatic generation of SQL code to do the comparison and marking quickly.   Since the fields in the three tables of interest may change in the future, I want to generate the appropriate SQL statement on the fly so that I don't have to keep updating it.  (I have done that type of thing before.)

There is a key field called IDCOUNT that I can use to join today's copy of the tables to yesterdays copy.

Where I am stumbling on this is in determining whether I can do the UPDATE statement with a reference to a table in an external db (yesterdays db copy, in this case).  

It looks like I need to generate the UPDATE statement such that it does a join between today's table copy and yesterdays based on IDCOUNT.  The SET clause is easy since I am only setting one field, Rev_Date.  The WHERE clause will be a pain... I will need to create a list of comparisons between a lot of fields in different tables having the same names.

MY QUESTION:
I don't see anywhere where it is possible to reference a table in an external DB in an UPDATE statement.  Is this possible?

If not, then I assume I should create a SELECT query on the fly to generate the relevant set of IDCOUNTS, then use that result set to feed the UPDATE query (subquery approach).  Correct?

I will be happy to answer any clarifying questions before awarding points.

Thanks!

Steve
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

are you aware that in ms access query, you can easily query another .mdb file from within a query:
select * from remote_table in 'C:\otherdatabase.mdf' 
I hope I got the syntax right...

Open in new window

Avatar of PumpMeister
PumpMeister

ASKER

I am aware of that... but try that with an INNER JOIN to a local table.
Or
Try that in an UPDATE query...

Cannot get it to work in either.

On one of the MS forums it suggests setting up a link to the external table and using the link in the queries... apparently supposed to have better performance, also.

Steve
the linked table is indeed better in terms of performance.

however, the inner join or update does also work.
can you post the syntax you tried, and the error(s) you got?
OK, here you go:

  NOTE: When I am saying that the SQL does not "work", this means that I am in the Query Builder
             SQL Window, and Access shows an error when attempting to switch to Design View.

The following works OK:  (Simple query to start with)

SELECT External.TAG_ AS ExtTag, Internal.TAG_ AS IntTag
FROM [Components-Instruments] AS External INNER JOIN [Components-Instruments] AS Internal ON External.ID_COUNT_ = Internal.ID_COUNT_;

The following does not work ("SYNTAX ERROR IN FROM CLAUSE"):

SELECT External.TAG_ AS ExtTag, Internal.TAG_ AS IntTag
FROM [Components-Instruments] AS External
IN 'C:\Users\Steve-Work\Documents\Work Projects\Access DBs\Wilmington-11.mdb'
INNER JOIN [Components-Instruments] AS Internal ON External.ID_COUNT_ = Internal.ID_COUNT_;

The following does not work ("SYNTAX ERROR IN FROM CLAUSE"):

SELECT External.TAG_ AS ExtTag, Internal.TAG_ AS IntTag
FROM [Components-Instruments]
IN 'C:\Users\Steve-Work\Documents\Work Projects\Access DBs\Wilmington-11.mdb' AS External
INNER JOIN [Components-Instruments] AS Internal ON External.ID_COUNT_ = Internal.ID_COUNT_;

The following does not work ("SYNTAX ERROR IN FROM CLAUSE"):

SELECT External.TAG_ AS ExtTag, Internal.TAG_ AS IntTag
FROM [Components-Instruments] AS External
INNER JOIN [Components-Instruments]
IN 'C:\Users\Steve-Work\Documents\Work Projects\Access DBs\Wilmington-11.mdb' AS Internal
ON External.ID_COUNT_ = Internal.ID_COUNT_;

The following does not work ("SYNTAX ERROR IN FROM CLAUSE"):

SELECT External.TAG_ AS ExtTag, Internal.TAG_ AS IntTag
FROM [Components-Instruments] AS External
INNER JOIN [Components-Instruments] AS Internal
IN 'C:\Users\Steve-Work\Documents\Work Projects\Access DBs\Wilmington-11.mdb'
ON External.ID_COUNT_ = Internal.ID_COUNT_;

I have tried the UPDATE query approach also, and it "does not work" either.  
Maybe the db path is too long???
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Wow!  Ok, I would have never figured that out....  Yikes!

Care to illuminate why it has to be like that?  

I imagine that this is something that Access cannot show in Query Design View, correct?
(just SQL box)

I will test later...

Steve
>Care to illuminate why it has to be like that?  
ask MS ...

>I imagine that this is something that Access cannot show in Query Design View, correct?
it worked for me...
Ok, this is wierd... I copy/pasted the SQL you posted.  Got the same error as before.

Massaged the SQL a bit,  parenthesis, moving stuff around, etc.   Got it to "take"

In Query Design View, checked properties of the Tables, ***nothing listed for Source***  (maybe I should have used that in the first place?).

I go back to SQL view, and it shows what you gave me, not my "corrected" massaged version.

Wierd or what!?!?!?  Thanks M$ for something that makes no sense whatsoever.

Hey, thanks a lot... hopefully I can take this and extrapolate it to the UPDATE queries that I actually need.  

I am still scratching my head as to what your thought process was to get from my original SQL to what you posted....  hmmmm....

Thanks again.

Steve
>I am still scratching my head as to what your thought process was to get from my original SQL to what you posted....  hmmmm....

as you said:  Thanks M$ for

I took the SQL view, and made a simple SELECT * FROM <...>.
then, switched to Design view, then right-clicked the "table", properties, and changed the alias.

changed back to SQL view, and voila ...