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

Erwin Craps - IT Helps Erwin.Craps at ithelps.be
Thu Feb 23 05:39:11 CST 2006


Thanks Gustav

I managed to adapt an earlier stage query, added the second table with
long descriptions, to import the articles togheter with long
description. 


Erwin


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: Thursday, February 23, 2006 12:11 PM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] Update query between access table and txt linked
filedoes not work.

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

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com



More information about the AccessD mailing list