Update syntax for a remote table access

  • Thread starter Thread starter RTY
  • Start date Start date
R

RTY

I have to update a remote database. I can't link to the tables because it
slows down my application to a crawl. So I just modified the SQL statements
to get to the remote database. The append was easy and the syntax works.
But, the Update statement doesn't. I need to use one table to update the
other. When I leave out the second table (tblOpportunityLocal) and all
references the statement will update the table correctly. How do I get the
SQL statement to accept both the remote table and local table references?
(and I can't use replication).

UPDATE tblOpportunityCollectionMaster in
'\\FILESHARES.CORP.XX.COM\Americas\Opportunity Collection Database\Master
Opportunity Collection Database V1.mdb', tblOpportunityLocal SET
tblOpportunityCollectionMaster.[Opportunity Name] =
tblOpportunityLocal.oppOpportunityName,
tblOpportunityCollectionMaster.Customer = "Test2",
tblOpportunityCollectionMaster.Project = "Test Project"
WHERE (((tblOpportunityCollectionMaster.[Work
Type])=tblOpportunityLocal.[oppWorkType]));
 
I have to update a remote database. I can't link to the tables because it
slows down my application to a crawl. So I just modified the SQL statements
to get to the remote database. The append was easy and the syntax works.
But, the Update statement doesn't. I need to use one table to update the
other. When I leave out the second table (tblOpportunityLocal) and all
references the statement will update the table correctly. How do I get the
SQL statement to accept both the remote table and local table references?
(and I can't use replication).
Click to expand...

You will need an explicit join between the tables: something like

UPDATE tblOpportunityCollectionMaster in
'\\FILESHARES.CORP.XX.COM\Americas\Opportunity Collection Database\Master
Opportunity Collection Database V1.mdb' INNER JOIN tblOpportunityLocal
ON tblOpportunityCollectionMaster.joinfield = tblOpportunityLocal.joinfield
SET
tblOpportunityCollectionMaster.[Opportunity Name] =
tblOpportunityLocal.oppOpportunityName,
tblOpportunityCollectionMaster.Customer = "Test2",
tblOpportunityCollectionMaster.Project = "Test Project"
WHERE (((tblOpportunityCollectionMaster.[Work
Type])=tblOpportunityLocal.[oppWorkType]));

I presume there is some field (which I called joinfield) which identifies
which record in Master should be updated by which record in Local.

If this is truly a *remote* (over a WAN rather than a Local Area Network)
database, you may be putting both the local and the remote database at risk of
corruption. Access isn't really designed to work over a WAN, and a dropped
connection during the execution of your query could irreversibly damage your
database! KEEP GOOD BACKUPS, and see if there is some other way to get the
data.
 
Back
Top