[AccessD] Update query between access table and txt linked file does not work.

Gustav Brock Gustav at cactus.dk
Thu Feb 23 05:11:06 CST 2006


Hi Erwin

Well, one method is to use DLookup() to extract the long text from the linked textfile. Not very fast, but it works.
Another - which will cause no bloat - is to import the textfile to a temp mdb file, link to that, update the data, and then delete the temp mdb.

Also, I'm not sure a code solution will be that slow. Turn your update query into a select query, don't select fields from the textfile, remove DistinctRow and switch to Inconsistent Update. 
Open that as a recordset and the link textfile as another recordset ordered the same way as the query. Now you can loop through both recordsets updating the first from the second without Find. This is quite fast at least with DAO.

/gustav

>>> Erwin.Craps at ithelps.be 23-02-2006 11:00:40 >>>
Hi
 
I have a query between a Access table and a linked txt file in Access
2003.
The access table contains articles but with a short article description,
the linked txt file contains same articles with long descriptions.
I want to update the access table so this also contains long
descriptions.
 
So I linked the 2 tabled based on ArtID but halfway the execution of the
query it suddenly says "I cannot update tables that are linked with this
ISAM".
 
The thing is when setting the query to view I can change the
descriptions manualy!!!
And further more, I am only updating one field and that field is in the
access table NOT in the linked table.
 
I know I can workaround using code (slow!) or to import the txt file
which bloats the database, but that are not solutions I want to
implement unless I have to.
 
Here's the query
UPDATE DISTINCTROW PROD_MainIMPORT INNER JOIN TechData_Descriptions ON
PROD_MainIMPORT.ProductCode = TechData_Descriptions.ArtID SET
PROD_MainIMPORT.ProductDescription =
[TechData_Descriptions.Description];
 
 
PROD_MainIMPORT = regular Access Table
TechData_Descriptions= linked txt file.
 

Erwin Craps

Zaakvoerder 

www.ithelps.be/onsgezin




More information about the AccessD mailing list