Unable to edit records in Query due to Relations with Linked .txt

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a query that opens a local table. This has been working for long time.
The data in the local table can be edited from the query.
Now I have linked the database to an external text file and created some
relations between the local table and the linked table. This works also fine.
I have changed the query so it gets data from the local table and data from
a single colum in the linked table. Again it works perfect. The data from the
linked table can not be changed from the database.
The problem is, that now I can't change any of the data in the local table
from the query (not even the data that are only in the local table and not
joined with the linked table). If I remove the relation between the local
table and the linked table (by removing the table in the query relation
window), then I can edit the data from the query again.
Independent of the relations, I can always edit the data by opening the
table directly.
Can anyone help me with this problem?

Kind regards
Jens
 
Hi, Jens.
The problem is, that now I can't change any of the data in the local table
from the query (not even the data that are only in the local table and not
joined with the linked table). If I remove the relation between the local
table and the linked table (by removing the table in the query relation
window), then I can edit the data from the query again.
Click to expand...

You cannot edit the data in the query because it is now a non-updateable
recordset. This can be caused by an "indeterminate" relationship, or from a
unique index that allows nulls, or from a read-only table, et cetera. And
there may be multiple issues causing the non-updateable recordset problem, so
this may require some investigation on your part.
Can anyone help me with this problem?
Click to expand...

Remember that relationships cannot be enforced on external tables. Since
your linked table is external, you might as well drop this relationship and
programmatically handle the job the database engine would normally provide
for relational constraints.

An alternative is to import this table instead of linking to it. The
relationship between the two tables would then be enforceable, but you'd
still need to determine what's making the recordset non-updateable. If the
linked table was read-only and this was the only issue, then importing it
would solve the problem.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that questions
answered the quickest are often from those who have a history of rewarding
the contributors who have taken the time to answer questions correctly.
 
Hi Camero,

I imported the data into a table istead. It did not help. I still can not
edit the data from the query. I does not say anything, but nothing happens
when I punch the keyboard when the cursor is located in a field.

The imported table has standard settings and it is editable if opened
directly.

Any ideas?

Kind regards

"'69 Camaro" skrev:
 
Hmmm, seams as if the problem is not related to the linked or imported table.
No matter what two tables are places in the relation window in a query, then
the data gets un-editable even if only one of the tables are actually used.
As soon as there is more than one in the window, then the data is locked.

Kind regards
Jens

"'69 Camaro" skrev:
 
Hi, Jens.
Any ideas?
Click to expand...

Start troubleshooting, beginning with the simplest to the more complex.
First, open the Relationships window and ensure that the relationship is
between the newly imported table (not the linked table) and the original
table in your query. (Sometimes changes don't get saved like they're
supposed to, so make sure it's the way it's supposed to be.) Next, ensure
that the relationship is set up for an updateable recordset by
right-clicking on the relationship line between the two tables and selecting
"Edit Relationship..." from the pop-up menu.

Are these the only two tables in this relationship? No other tables or
self-joins?

Is the Relationship Type "Indeterminate," "one-to-many," or "one-to-one"?
If it's one-to-many, is "Cascade Update Related Fields" enabled? If it's
one-to-many or one-to-one, is it using an INNER JOIN or an OUTER JOIN? If
it's one-to-many or one-to-one, does the table on the one side have a
primary key? (And yes, you should have primary keys on all permanent
tables.)

Is the join on a single column or on a composite key? Does the join column
or composite key columns allow NULL's?

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)
 
Hi, Jens.
No matter what two tables are places in the relation window in a query,
then
the data gets un-editable even if only one of the tables are actually
used.
Click to expand...

It's the relationship between the two tables that's making the query's
recordset non-updateable, so you have to determine what issues that the
relationship depends upon for an updateable recordset are interfering. I've
listed some questions for you in another reply in this thread to help you
determine what these issues are. Once you solve those issues (as long as
you _can_ solve them), you'll have an updateable recordset.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)
 
Hello these are my 2 cents: I had the exact problem. After 3 Hrs of trouble
shooting and recreating similar tables I found that my problem was with the
joining table. Make sure the portion/field joining is indexed properly. That
seemed to work for me. Hope this helps.
VADIMBAR
 
Back
Top