Updating Linked Table not Supported in this ISAM (1 Viewer)

Hi Guys,

I know you're all Access-Smart, so I figured this would be the best place to post this question.

What I Did:
*I created an Excel/CSV table and imported it as a linked table into Access. (This table has 6 fields which also occur in another larger table of 12 fields.)
*I linked the small imported table to the larger table - I used a one-sided join based on an identifier in the small table, as I want to use it to update 5 fields in the larger table. So I created this Select Query to only grab those records that exist in the small table.
*I attempted to run an update Query based on the data in the small table to update data in the large table.
*Access complains that, "Updating data in a linked table is not supported by this ISAM."

Is that because I didn't create a new table when I imported the CSV, but just linked to the file instead?

Anyone suggest any workarounds? Manual data entry for this number of records is more time consuming (and prone to error) than automating this.

Thanks!

Is that because I didn't create a new table when I imported the CSV, but just linked to the file instead?

Click to expand...

Yes

Import the csv or excel table, do your data entry then export using transfertext.

When one part of a query is not updateable, none of the query is updateable. Text files cannot be directly updated. They can be written and they can be appended to (add records to the end) but records cannot be updated or deleted.

So, as CJ said, importing the csv file will solve the problem.

Pat Hartman said:

When one part of a query is not updateable, none of the query is updateable.

Click to expand...

That's interesting. I did get it to work by creating a table within the database using the file as a source. I wasn't trying to update the file portion, just using it as a source.

I wonder if other relational databases bypass this limitation (like PostgreSQL)?

Is there a specific name for this limitation?

Now I'm just getting all theoretical. I appreciate your answers!

Pat / All,

This was a huge help to me; I had no idea you couldn't update from a linked text file and was struggling with this for some time until I stumbled on this post!

(Incidentally, my workaround was to run a 'Make Table' query off the linked table into a temporary table, perform the update off that, then delete the temporary table - works a charm!)

Much appreciated!

AOB

I wonder if other relational databases bypass this limitation (like PostgreSQL)?

Click to expand...

Don't confuse relational databases with development environments. If you are using Access as your development environment, you can create joins that join tables from any external source that supports ODBC. If you are using something else such as Oracle Forms, ASP, VB.Net, you might not even be able to do the heterogeneous join (tables from two different types of data store) let alone create an updateable query.