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?

 Applicable picture
Fritz PaulAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
have you tried doing a Compact and repair?
close all objects before editing the table design
0
Fritz PaulAuthor Commented:
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
0
Rey Obrero (Capricorn1)Commented:
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?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Rey Obrero (Capricorn1)Commented:
is the table came from a replica db?
0
Rey Obrero (Capricorn1)Commented:
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.
0
Jeffrey CoachmanMIS LiasonCommented:
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.
0
Fritz PaulAuthor Commented:
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.
0
Fritz PaulAuthor Commented:
Here is the design view of MsysObjects.
Applicable picture
0
Rey Obrero (Capricorn1)Commented:
that is why..
you are using the MSysObjects as the source of the make table query.
0
Fritz PaulAuthor Commented:
Sorry I do not understand.
Why is that wrong?
How does the table know it to such an extent that it is not updateable?
0
Rey Obrero (Capricorn1)Commented:
here is what you can do
in design view Create your table, then use this codes to populate the table
Sub getQueries()
Dim rs As DAO.Recordset, sql As String, rsQ As DAO.Recordset
Set rsQ = CurrentDb.OpenRecordset("tblListOfQueries")
sql = "SELECT MsysObjects.Name AS QueryName, MsysObjects.DateCreate AS DateCreated, MsysObjects.DateUpdate AS DateUpdated" _
    & " FROM MsysObjects" _
    & " WHERE (((Left$([MsysObjects].[Name], 1)) <> '~') And ((MsysObjects.Type) = 5))" _
    & " ORDER BY MsysObjects.Name;"

Set rs = CurrentDb.OpenRecordset(sql)
Do Until rs.EOF
    rsQ.AddNew
    rsQ!QueryName = rs!QueryName
    rsQ!DateCreate = rs!DateCreated
    rsQ!DateUpdate = rs!DateUpdated
    rsQ.Update
    
    rs.MoveNext
Loop
rs.Close
rsQ.Close
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Fritz PaulAuthor Commented:
Excellent!
Thanks all.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.