Link to home
Start Free TrialLog in
Avatar of Fritz Paul
Fritz PaulFlag for South Africa

asked on

Why can't I update or add records to a table in Access?

I created a make table query to make a table tblListOfQueries from the queries in my database. I included a "Purpose" field.

Now I want to add another Query_Name to the table, but find that I can't add a Query_Name and I cannot edit any Query_Name in the table. I show my table definition below.

Do you see any reason why I can't edit the names?

 User generated image
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

have you tried doing a Compact and repair?
close all objects before editing the table design
Avatar of Fritz Paul

ASKER

I tried both options thanks.

I exported the table to a new small database and attached here. I will appreciate it if someone will take a look and see what is the problem.

The issue is in tblListOfQueries.

Thanks.
Database1.accdb
this is weird, the four fields in the table shows (New) after the Last record.

what is the source of the Make table query that created the table?
is the table came from a replica db?
try to create a Make table query, but do not use ANY system fields.
the three fields "Query_Name","DateCreate", and "DateUpdate" are all replication fields.
FWIW,
Just for fun, I made a make table query from the table and the data was still not updateable.
I think Rey is on the right path about Replication, ...so I will let you continue on with him.
I appreciate your help.

Here is the select query which should show a list of all queries in an Access database:

SELECT MsysObjects.Name, "" AS Purpose, MsysObjects.DateCreate, MsysObjects.DateUpdate
FROM MsysObjects
WHERE (((Left$([MsysObjects].[Name],1))<>"~") AND ((MsysObjects.Type)=5))
ORDER BY MsysObjects.Name;

Open in new window


And the Make Table query:

SELECT MsysObjects.Name, "" AS Purpose, MsysObjects.DateCreate, MsysObjects.DateUpdate INTO tblListOfQueries
FROM MsysObjects
WHERE (((Left$([MsysObjects].[Name],1))<>"~") AND ((MsysObjects.Type)=5))
ORDER BY MsysObjects.Name;

I just changed Name to QueryName afterwards in the table.
Here is the design view of MsysObjects.
User generated image
that is why..
you are using the MSysObjects as the source of the make table query.
Sorry I do not understand.
Why is that wrong?
How does the table know it to such an extent that it is not updateable?
ASKER CERTIFIED SOLUTION
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Excellent!
Thanks all.