What makes a query updateable?

  • Thread starter Thread starter LAS
  • Start date Start date
L

LAS

This query behind a form is UPDATEABLE

SELECT tblScores.*, tblperiods.Description, tblScores.Student_ID,
tblStudents.Personal_Goals
FROM (tblScores INNER JOIN tblperiods ON tblScores.Period_Code =
tblperiods.Period_Code) INNER JOIN tblStudents ON tblScores.Student_ID =
tblStudents.Student_ID
WHERE
(((tblScores.Student_ID)=[Forms]![frmStudentScoreEntry]![txtStudent_ID]) AND
((tblScores.Score_Date)=[Forms]![frmStudentScoreEntry]![txtScore_Date]) AND
((tblperiods.Period_Code)<>'*'))
ORDER BY tblperiods.Sort_Order;

When I modified it to add some Where criteria, it became NOT UPDATEABLE.
Anyway, the error message said it wasn't updateable. I assume it was the
query change, since I didn't make any other changes I know about.

SELECT tblScores.*, tblperiods.Description, tblScores.Student_ID,
tblStudents.Personal_Goals
FROM (tblScores INNER JOIN tblperiods ON tblScores.Period_Code =
tblperiods.Period_Code) INNER JOIN tblStudents ON tblScores.Student_ID =
tblStudents.Student_ID
WHERE
(((tblScores.Student_ID)=[Forms]![frmStudentScoreEntry]![txtStudent_ID]) AND
((tblScores.Score_Date)=[Forms]![frmStudentScoreEntry]![txtScore_Date]) AND
((tblperiods.Class_Code)=[Forms]![frmStudentScoreEntry]![cboClass_Code]) AND
((tblperiods.Weekday)=[Forms]![frmStudentScoreEntry]![txtWeekday]) AND
((tblperiods.Period_Code)<>'*'))
ORDER BY tblperiods.Sort_Order;

Why?
 
When I modified it to add some Where criteria, it became NOT
UPDATEABLE.
Anyway, the error message said it wasn't updateable. I assume it
was the query change, since I didn't make any other changes I know
about.
Click to expand...

Have you tried compacting the database it's in? I see no reason why
the altered query should not be editable, so I'm guessing the query
compilation is out of date and needs to be recalculated (a compact
marks all queries for recompiliation).
 
When I modified it to add some Where criteria, it became NOT
UPDATEABLE.
Anyway, the error message said it wasn't updateable. I assume it
was the query change, since I didn't make any other changes I know
about.
Click to expand...

Also, in general, I think it's good practice to define all
references to form controls as parameters. The main reason for this
is that it will handle Nulls in the expected way (which it will not
if you don't declare the parameters), but it also gives the query
optimizer more to work with in terms of optimization, since it then
knows the data types of the arguments in the WHERE clause.
 
Back
Top