Update Query

A

Antonio

I have a link to a text file that is updated daily. I run an append query to
add any new records on the text file into a table stored in my database.

I am attempting to update previously appended records based on a field that
may change on a daily basis. The record remains on the report, but one field
goes through 4 changes through the records duration on the report. (linked
text file)

Example
Day 1 Field 21 = "Prelim"
Day 2 Field 21 = "Prelim"
Day 3 Field 21 = "Submitted"
Day 4 Field 21 = "Finalized"

I attempted to set up an update query that establishes a link beteween a
unique account number on the lilnked text file and the table in my data base.

However, I am receiving an error message stating,
"Updating data in a linked table is not supported by this ISAM"

Am I doing something wrong? Is there a work around?

TIA

Antonio
 
J

Jackie L

Try importing the text file into a temporary table in the database through a
make table query. Then you should be able to do the update query. If the
make table does not produce the correct field type, then create the temp
table an use an append query instead so the the field types are the same in
your key fields.

Hope this helps.
 
C

Chris L.

I have a link to a text file that is updated daily. I run an append query to
add any new records on the text file into a table stored in my database.

I am attempting to update previously appended records based on a field that
may change on a daily basis. The record remains on the report, but one field
goes through 4 changes through the records duration on the report. (linked
text file)

Example
Day 1 Field 21 = "Prelim"
Day 2 Field 21 = "Prelim"
Day 3 Field 21 = "Submitted"
Day 4 Field 21 = "Finalized"

I attempted to set up an update query that establishes a link beteween a
unique account number on the lilnked text file and the table in my data base.

However, I am receiving an error message stating,
"Updating data in a linked table is not supported by this ISAM"

Am I doing something wrong? Is there a work around?

TIA

Antonio
Click to expand...

According to the error message, you're trying to update the linked
text file instead of your access table.
Please post the SQL of your update query to verify it

C.
 
A

Antonio

Chris,

Here it is.

UPDATE UnprintedBills INNER JOIN tblUnprintedBill ON UnprintedBills.Field5 =
tblUnprintedBill.Acct SET tblUnprintedBill.AbsStatus =
[UnprintedBills].[Field21];

UnprintedBills.Field21 is the name of the linked text file and field that
has the info I am trying to update from.
tblUnprintedBill.AbsStatus is the table and field I am trying to update.

I thought I might have gotten it backwards so I changed it like so:

UPDATE UnprintedBills INNER JOIN tblUnprintedBill ON UnprintedBills.Field5 =
tblUnprintedBill.Acct SET UnprintedBills.Field21 =
[tblUnprintedBill].[AbsStatus];

But I got the same error message.
TIA
Antonio
 
C

Chris L.

I tried linking a text file and updating an Access table with it and
I'm getting your exact same error message. Seems like you're out of
luck, because I browsed the newsgroups and this error is being
reported by users, from a long time ago (2002).

I suggest importing the whole TXT (or the relevant records / columns)
to a temp table in Access and work from there onwards. I don't know if
there are any other workarounds.

Regards
C.

Chris,

Here it is.

UPDATE UnprintedBills INNER JOIN tblUnprintedBill ON UnprintedBills.Field5 =
tblUnprintedBill.Acct SET tblUnprintedBill.AbsStatus =
[UnprintedBills].[Field21];

UnprintedBills.Field21 is the name of the linked text file and field that
has the info I am trying to update from.
tblUnprintedBill.AbsStatus is the table and field I am trying to update.

I thought I might have gotten it backwards so I changed it like so:

UPDATE UnprintedBills INNER JOIN tblUnprintedBill ON UnprintedBills.Field5 =
tblUnprintedBill.Acct SET UnprintedBills.Field21 =
[tblUnprintedBill].[AbsStatus];

But I got the same error message.
TIA
Antonio



According to the error message, you're trying to update the linked
text file instead of your access table.
Please post the SQL of your update query to verify it
Click to expand...
C.- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...
 
Top