Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old June 7th, 2007, 11:41 AM
Registered User
 
Join Date: Jun 2007
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default Update Query using inner join with a linked table


I have a text file that is linked my database. What I'm trying to do is update another with the data from this linked file using an inner join.

I get the error message 'Updating linked table is not supported by this ISAM'. BUT I am not updating the linked file I am updating the other table, is there a way to work around (please keep in mind performance is everything)

Linked file: IIB_XREF
Other table: GRID_XREF

Query:

UPDATE GRID_XREF
INNER JOIN IIB_XREF2 ON IIB_XREF2.ALTERN_CODE = GRID_XREF.ALTERN_CODE
SET GRID_XREF.GRID_ID = IIB_XREF2.GRID_ID
WHERE IIB_XREF2.GRID_ID<>GRID_XREF.GRID_ID;


Anyone ?

 
Old June 7th, 2007, 11:52 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

You need to pull the data into a local table first, then run the update from the local table.

Create the table.

Then in your code:
Run a delete query on it to make sure there is no data in it.
Append the data from the text file.
Do the update.
Run the Delete query again to empty the table.

In any event, you want to change the local table's ID field (Can you do this?) to the text file's ID field value in each case where they are not equal? Won't this get stuck in an infinite loop?

Did any of that help?


mmcdonal
 
Old June 7th, 2007, 03:38 PM
Registered User
 
Join Date: Jun 2007
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thx for your reply I hope using a temporary table will not decrease the performance to much

Note: the grid_id is not the id/autonumber of the table, it is not a primary key it is some kind of code


Quote:
quote:Originally posted by mmcdonal
 You need to pull the data into a local table first, then run the update from the local table.

Create the table.

Then in your code:
Run a delete query on it to make sure there is no data in it.
Append the data from the text file.
Do the update.
Run the Delete query again to empty the table.

In any event, you want to change the local table's ID field (Can you do this?) to the text file's ID field value in each case where they are not equal? Won't this get stuck in an infinite loop?

Did any of that help?


mmcdonal
 
Old June 8th, 2007, 12:35 PM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

How many records in the text file, and how often is this done? That is your determining factor. Otherwise the queries are nto abig deal.


mmcdonal





Similar Threads
Thread Thread Starter Forum Replies Last Post
updating table w/ query that has left join msordo VB.NET 2002/2003 Basics 4 April 11th, 2008 11:26 AM
Update table using SQL query from form pater53 Access VBA 4 January 24th, 2007 11:24 AM
Can a Make Table Query produce a Linked table? kronik Access 5 May 16th, 2006 06:17 AM
Update linked table penta Access 2 September 28th, 2004 07:58 AM
Access and update linked SQL Server 2000 table Lizu Access 9 May 10th, 2004 12:42 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.