I've found this excellent resource that covers why a query might be read-only. I have a query that is not read only, yet I'm unable to add new records to it.
There are three tables. Let's call them A, B, and C. Each has a unique primary key, but B and C also contain a foreign key that indexes into A. If building a query that does a inner join between A and B, the query is both editable and I can add new records to it. Similarly, a query that inner joins A and C is editable and supports adding new records. However, when doing inner join on A and B, then inner joining result with C, I can edit records, but not add new records. There is presumably something amiss with the "one-to-manyness" of this query, but I'm having a hard time seeing it, and also thinking through possible remedies.
With typical usage, tables A, B, and C should all have 1:1 relationships (though this is not enforced). Any ideas?
Here's the SQL syntax for the join
SELECT [Contact Information].[Record #],
[Contact Information].[Last Name],
[Contact Information].[First Name],
[Study Information].[Study Group],
[Study Information].[Study Status],
[Clinical Information].[Contact Name],
[Study Information].[Contact Name2]
FROM ([Contact Information]
INNER JOIN [Study Information]
ON [Contact Information].[Record #] = [Study Information].[Contact Name2])
INNER JOIN [Clinical Information]
ON [Contact Information].[Record #] = [Clinical Information].[Contact Name];
and here's a link to image that shows how Access is visually interpreting the query
Any ideas? Thanks!
Best Answer
Microsoft Access has a setting in the Query Properties window called
Recordset Type
. You need to set this toDynaset (Inconsistent Updates)
to allow your query to be updatable.