Update statement with linked table join

[Guigui]

Hi all,

I have an issue with an UPDATE query in Access 2007.

I have a table to update based on informations from another table. The SQL code I get by default is :

UPDATE Table1 INNER JOIN Table2 on Table1.id=Table2.T1id
SET Table1.col1 = Table2.ColA, ...etc
WHERE Table1.Col2 <> "Toto"

The issue is that Table2 is a linked table to a text file, so Access refuses the UPDATE statement because he can not update a table linked to a text file, even if later in the statement I do not update Table2 at all, I just get data from it.

Any ways to go around this, knowing that I can not import Table2 into an Access database because it is too big (it weight more than 2Gb if I import it into Access, while still under 2Gb when in txt format).

Thanks for your help !

[NTC]

This may not work, but what I would try is to write a vanilla select query on table 2 involving only the fields needed. See if this will run/open ok. If it barfs because table 2 is too big then I do not see any solution. If it does run ok - then save the query with a name.

Remake your update query using the select query as a record source instead of table 2.

Also, as a sanity check - keep in mind you have 2 issues:
a. the update query doesn't work
b. the file is too big

be sure to resolve the first by working with a test table that is small but contains the identical field names/values.

hope this helps.

[Guigui]

Yes I've tried this but it won't work : the query is lanched, but the same error occurs a few seconds later (I guess when he is running the selection on the vanilla query)

I am in parallel working on the size issue, so I may remove this constaint and solve the issue like this, but still I would be interset in a workaround to avoid having to load the text file onto a table.

[ajetrumpet]

As another idea...

how about writing code that is similiar to:

*making a copy of the table
*running the UPDATE based on the new table
*deleting the new table

????

you can copy a linked table and destroy the link, right? I don't know, I've never done it. but if so, that's a workaround.

[weekend00]

change you update query to make table query(choose the necessary fields, but don't miss the key field), create a temporary table,

update table1 joining to this temporary table.

delete the temporary table.

[Guigui]

Hello,

thanks for the tips. Actually if I manage to reduce the size of the table, then I can do a make table, have the update query and delete the table as you suggest. This is what I am working at. Sorry there seems to have no other simpler workaround :/

[Guigui]

OK I did it this way :
- Make table query from the linked one, with the join on the the other (so I import only the usefull records and stays below the 2Gb limit)
- Update query with the imported table.
- Delete of the imported table

Still heavy, but working.

Thanks