View Object Dependencies break my Queries!

[kramer31]

Hi. I am working with a version controlled Access database with many
Queries and many Reports. Recently I discovered that some old reports
and the queries that those reports depend on are broken. I've tracked
the problem down to a specific version. In the last version where the
queries work, when I click on the query Project_Efforts it shows the
data that it should, but if I right click and select Object
Dependencies, Access first asks if I want to turn on 'Track name
AutoCorrect info' option. If I say OK, then the query is broken.
After that, if I click on Project_Efforts, I get the following error:

'The Microsoft Jet database engine cannot find the input table or
query 'SELECT Project_Id, Run_Set_Id, Run_Id, SUM(Activity_Effort) As
DevEffort, 0 AS InspEffort, 0 AS ReworkEffort, 0 AS TestEffort FROM
Input_Parameters, Project_Activity_Efforts WHERE (Project_Id =
ProjectId AND Phase_Id = PhaseId AND Step_Id = StepId AND '. Make
sure it exists and that its name is spelled correctly.

The full text of the query does refer to that:

SELECT Project_Id, Run_Set_Id, Run_Id, SUM(DevEffort) AS Dev_Effort,
SUM(InspEffort) AS Insp_Effort, SUM(ReworkEffort) AS Rework_Effort,
SUM(TestEffort) AS Test_Effort
FROM [SELECT Project_Id, Run_Set_Id, Run_Id, SUM(Activity_Effort) AS
DevEffort, 0 AS InspEffort, 0 AS ReworkEffort, 0 AS TestEffort FROM
Input_Parameters, Project_Activity_Efforts WHERE (Project_Id =
ProjectId AND Phase_Id = PhaseId AND Step_Id = StepId AND ] AS [%
$##@_Alias]
GROUP BY Project_Id, Run_Set_Id, Run_Id;


But that was not the text of the query before I tried to look at the
Object Dependencies. It was:

SELECT Project_Id, Run_Set_Id, Run_Id, SUM(DevEffort) AS Dev_Effort,
SUM(InspEffort) AS Insp_Effort, SUM(ReworkEffort) AS Rework_Effort,
SUM(TestEffort) AS Test_Effort
FROM [SELECT Project_Id, Run_Set_Id, Run_Id, SUM(Activity_Effort) AS
DevEffort, 0 AS InspEffort, 0 AS ReworkEffort, 0 AS TestEffort
FROM Input_Parameters, Project_Activity_Efforts
WHERE (Project_Id = ProjectId AND Phase_Id = PhaseId AND Step_Id =
StepId AND ActivityEffortType = 1)
GROUP BY Project_Id, Run_Set_Id, Run_Id
UNION
SELECT Project_Id, Run_Set_Id, Run_Id, 0 AS DevEffort,
SUM(Activity_Effort) AS InspEffort, 0 AS ReworkEffort, 0 AS TestEffort
FROM Input_Parameters, Project_Activity_Efforts
WHERE (Project_Id = ProjectId AND Phase_Id = PhaseId AND Step_Id =
StepId AND ActivityEffortType = 2)
GROUP BY Project_Id, Run_Set_Id, Run_Id
UNION
SELECT Project_Id, Run_Set_Id, Run_Id, 0 AS DevEffort, 0 AS
InspEffort, SUM(Activity_Effort) AS ReworkEffort, 0 AS TestEffort
FROM Input_Parameters, Project_Activity_Efforts
WHERE (Project_Id = ProjectId AND Phase_Id = PhaseId AND Step_Id =
StepId AND ActivityEffortType = 3)
GROUP BY Project_Id, Run_Set_Id, Run_Id
UNION SELECT Project_Id, Run_Set_Id, Run_Id, 0 AS DevEffort, 0 AS
InspEffort, 0 AS ReworkEffort, SUM(Activity_Effort) AS TestEffort
FROM Input_Parameters, Project_Activity_Efforts
WHERE (Project_Id = ProjectId AND Phase_Id = PhaseId AND Step_Id =
StepId AND ActivityEffortType = 4)
GROUP BY Project_Id, Run_Set_Id, Run_Id]. AS [%$##@_Alias]
GROUP BY Project_Id, Run_Set_Id, Run_Id;


Yes, this is a big ugly legacy query.

Anyway, I can copy the correct text back into the query and it seems
to work, but why did it break in the first place?

Also, this database supports an application that our customers use.
What if our customers want to track Object Dependencies? Can we
prevent that? Isn't 'Track Name Autocorrect' just supposed to track
changes? Why is it breaking my queries?

 

[Allen Browne]

Object Dependencies relies on Name AutoCorrect.
Name AutoCorrect is guaranteed to corrupt your database.

If you are not aware of some of the issue Name AutoCorrupt gives, here's a
brief old list:
Failures caused by Name Auto-Correct
at:
http://allenbrowne.com/bug-03.html
Although some of those issues were fixed in service packs, more have been
uncovered. I don't bother trying to maintain that page: there are just so
many problems associated with the whole misfeature.

Use it if you wish, but on an irrelevant copy of the database, not on the
original if you value your data, the project, or your sanity.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi. I am working with a version controlled Access database with many
Queries and many Reports. Recently I discovered that some old reports
and the queries that those reports depend on are broken. I've tracked
the problem down to a specific version. In the last version where the
queries work, when I click on the query Project_Efforts it shows the
data that it should, but if I right click and select Object
Dependencies, Access first asks if I want to turn on 'Track name
AutoCorrect info' option. If I say OK, then the query is broken.
After that, if I click on Project_Efforts, I get the following error:

'The Microsoft Jet database engine cannot find the input table or
query 'SELECT Project_Id, Run_Set_Id, Run_Id, SUM(Activity_Effort) As
DevEffort, 0 AS InspEffort, 0 AS ReworkEffort, 0 AS TestEffort FROM
Input_Parameters, Project_Activity_Efforts WHERE (Project_Id =
ProjectId AND Phase_Id = PhaseId AND Step_Id = StepId AND '. Make
sure it exists and that its name is spelled correctly.

The full text of the query does refer to that:

SELECT Project_Id, Run_Set_Id, Run_Id, SUM(DevEffort) AS Dev_Effort,
SUM(InspEffort) AS Insp_Effort, SUM(ReworkEffort) AS Rework_Effort,
SUM(TestEffort) AS Test_Effort
FROM [SELECT Project_Id, Run_Set_Id, Run_Id, SUM(Activity_Effort) AS
DevEffort, 0 AS InspEffort, 0 AS ReworkEffort, 0 AS TestEffort FROM
Input_Parameters, Project_Activity_Efforts WHERE (Project_Id =
ProjectId AND Phase_Id = PhaseId AND Step_Id = StepId AND ] AS [%
$##@_Alias]
GROUP BY Project_Id, Run_Set_Id, Run_Id;


But that was not the text of the query before I tried to look at the
Object Dependencies. It was:

SELECT Project_Id, Run_Set_Id, Run_Id, SUM(DevEffort) AS Dev_Effort,
SUM(InspEffort) AS Insp_Effort, SUM(ReworkEffort) AS Rework_Effort,
SUM(TestEffort) AS Test_Effort
FROM [SELECT Project_Id, Run_Set_Id, Run_Id, SUM(Activity_Effort) AS
DevEffort, 0 AS InspEffort, 0 AS ReworkEffort, 0 AS TestEffort
FROM Input_Parameters, Project_Activity_Efforts
WHERE (Project_Id = ProjectId AND Phase_Id = PhaseId AND Step_Id =
StepId AND ActivityEffortType = 1)
GROUP BY Project_Id, Run_Set_Id, Run_Id
UNION
SELECT Project_Id, Run_Set_Id, Run_Id, 0 AS DevEffort,
SUM(Activity_Effort) AS InspEffort, 0 AS ReworkEffort, 0 AS TestEffort
FROM Input_Parameters, Project_Activity_Efforts
WHERE (Project_Id = ProjectId AND Phase_Id = PhaseId AND Step_Id =
StepId AND ActivityEffortType = 2)
GROUP BY Project_Id, Run_Set_Id, Run_Id
UNION
SELECT Project_Id, Run_Set_Id, Run_Id, 0 AS DevEffort, 0 AS
InspEffort, SUM(Activity_Effort) AS ReworkEffort, 0 AS TestEffort
FROM Input_Parameters, Project_Activity_Efforts
WHERE (Project_Id = ProjectId AND Phase_Id = PhaseId AND Step_Id =
StepId AND ActivityEffortType = 3)
GROUP BY Project_Id, Run_Set_Id, Run_Id
UNION SELECT Project_Id, Run_Set_Id, Run_Id, 0 AS DevEffort, 0 AS
InspEffort, 0 AS ReworkEffort, SUM(Activity_Effort) AS TestEffort
FROM Input_Parameters, Project_Activity_Efforts
WHERE (Project_Id = ProjectId AND Phase_Id = PhaseId AND Step_Id =
StepId AND ActivityEffortType = 4)
GROUP BY Project_Id, Run_Set_Id, Run_Id]. AS [%$##@_Alias]
GROUP BY Project_Id, Run_Set_Id, Run_Id;


Yes, this is a big ugly legacy query.

Anyway, I can copy the correct text back into the query and it seems
to work, but why did it break in the first place?

Also, this database supports an application that our customers use.
What if our customers want to track Object Dependencies? Can we
prevent that? Isn't 'Track Name Autocorrect' just supposed to track
changes? Why is it breaking my queries?

 

[kramer31]

Is there a way to turn it off via security settings so that it can't
be turned on by other people using the database that I create?

Object Dependencies relies on Name AutoCorrect.
Name AutoCorrect is guaranteed to corrupt your database.

If you are not aware of some of the issue Name AutoCorrupt gives, here's a
brief old list:
Failures caused by Name Auto-Correct
at:
http://allenbrowne.com/bug-03.html
Although some of those issues were fixed in service packs, more have been
uncovered. I don't bother trying to maintain that page: there are just so
many problems associated with the whole misfeature.

Use it if you wish, but on an irrelevant copy of the database, not on the
original if you value your data, the project, or your sanity.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

Hi. I am working with a version controlled Access database with many
Queries and many Reports. Recently I discovered that some old reports
and the queries that those reports depend on are broken. I've tracked
the problem down to a specific version. In the last version where the
queries work, when I click on the query Project_Efforts it shows the
data that it should, but if I right click and select Object
Dependencies, Access first asks if I want to turn on 'Track name
AutoCorrect info' option. If I say OK, then the query is broken.
After that, if I click on Project_Efforts, I get the following error:

'The Microsoft Jet database engine cannot find the input table or
query 'SELECT Project_Id, Run_Set_Id, Run_Id, SUM(Activity_Effort) As
DevEffort, 0 AS InspEffort, 0 AS ReworkEffort, 0 AS TestEffort FROM
Input_Parameters, Project_Activity_Efforts WHERE (Project_Id =
ProjectId AND Phase_Id = PhaseId AND Step_Id = StepId AND '. Make
sure it exists and that its name is spelled correctly.

The full text of the query does refer to that:

SELECT Project_Id, Run_Set_Id, Run_Id, SUM(DevEffort) AS Dev_Effort,
SUM(InspEffort) AS Insp_Effort, SUM(ReworkEffort) AS Rework_Effort,
SUM(TestEffort) AS Test_Effort
FROM [SELECT Project_Id, Run_Set_Id, Run_Id, SUM(Activity_Effort) AS
DevEffort, 0 AS InspEffort, 0 AS ReworkEffort, 0 AS TestEffort FROM
Input_Parameters, Project_Activity_Efforts WHERE (Project_Id =
ProjectId AND Phase_Id = PhaseId AND Step_Id = StepId AND ] AS [%
$##@_Alias]
GROUP BY Project_Id, Run_Set_Id, Run_Id;

But that was not the text of the query before I tried to look at the
Object Dependencies. It was:

SELECT Project_Id, Run_Set_Id, Run_Id, SUM(DevEffort) AS Dev_Effort,
SUM(InspEffort) AS Insp_Effort, SUM(ReworkEffort) AS Rework_Effort,
SUM(TestEffort) AS Test_Effort
FROM [SELECT Project_Id, Run_Set_Id, Run_Id, SUM(Activity_Effort) AS
DevEffort, 0 AS InspEffort, 0 AS ReworkEffort, 0 AS TestEffort
FROM Input_Parameters, Project_Activity_Efforts
WHERE (Project_Id = ProjectId AND Phase_Id = PhaseId AND Step_Id =
StepId AND ActivityEffortType = 1)
GROUP BY Project_Id, Run_Set_Id, Run_Id
UNION
SELECT Project_Id, Run_Set_Id, Run_Id, 0 AS DevEffort,
SUM(Activity_Effort) AS InspEffort, 0 AS ReworkEffort, 0 AS TestEffort
FROM Input_Parameters, Project_Activity_Efforts
WHERE (Project_Id = ProjectId AND Phase_Id = PhaseId AND Step_Id =
StepId AND ActivityEffortType = 2)
GROUP BY Project_Id, Run_Set_Id, Run_Id
UNION
SELECT Project_Id, Run_Set_Id, Run_Id, 0 AS DevEffort, 0 AS
InspEffort, SUM(Activity_Effort) AS ReworkEffort, 0 AS TestEffort
FROM Input_Parameters, Project_Activity_Efforts
WHERE (Project_Id = ProjectId AND Phase_Id = PhaseId AND Step_Id =
StepId AND ActivityEffortType = 3)
GROUP BY Project_Id, Run_Set_Id, Run_Id
UNION SELECT Project_Id, Run_Set_Id, Run_Id, 0 AS DevEffort, 0 AS
InspEffort, 0 AS ReworkEffort, SUM(Activity_Effort) AS TestEffort
FROM Input_Parameters, Project_Activity_Efforts
WHERE (Project_Id = ProjectId AND Phase_Id = PhaseId AND Step_Id =
StepId AND ActivityEffortType = 4)
GROUP BY Project_Id, Run_Set_Id, Run_Id]. AS [%$##@_Alias]
GROUP BY Project_Id, Run_Set_Id, Run_Id;

Yes, this is a big ugly legacy query.

Anyway, I can copy the correct text back into the query and it seems
to work, but why did it break in the first place?

Also, this database supports an application that our customers use.
What if our customers want to track Object Dependencies? Can we
prevent that? Isn't 'Track Name Autocorrect' just supposed to track
changes? Why is it breaking my queries?

 

[Allen Browne]

The Name AutoCorrect setting is per database, so once you turn it off for
your database, someone would have to explicitly turn it back on to get into
trouble.

If you are paranoid, you could explicitly turn it off each time your
database starts. The code is:
Application.SetOption "Track Name AutoCorrect Info", False
Application.SetOption "Perform Name AutoCorrect", False
Application.SetOption "Log Name AutoCorrect Changes", False

But I'm not sure about the wisdom of blocking user choices: personally I
refuse to take responsibility for the decisions of users who choose to shoot
themselves in the foot.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Is there a way to turn it off via security settings so that it can't
be turned on by other people using the database that I create?

Object Dependencies relies on Name AutoCorrect.
Name AutoCorrect is guaranteed to corrupt your database.

If you are not aware of some of the issue Name AutoCorrupt gives, here's
a
brief old list:
Failures caused by Name Auto-Correct
at:
http://allenbrowne.com/bug-03.html
Although some of those issues were fixed in service packs, more have been
uncovered. I don't bother trying to maintain that page: there are just so
many problems associated with the whole misfeature.

Use it if you wish, but on an irrelevant copy of the database, not on the
original if you value your data, the project, or your sanity.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

Hi. I am working with a version controlled Access database with many
Queries and many Reports. Recently I discovered that some old reports
and the queries that those reports depend on are broken. I've tracked
the problem down to a specific version. In the last version where the
queries work, when I click on the query Project_Efforts it shows the
data that it should, but if I right click and select Object
Dependencies, Access first asks if I want to turn on 'Track name
AutoCorrect info' option. If I say OK, then the query is broken.
After that, if I click on Project_Efforts, I get the following error:

'The Microsoft Jet database engine cannot find the input table or
query 'SELECT Project_Id, Run_Set_Id, Run_Id, SUM(Activity_Effort) As
DevEffort, 0 AS InspEffort, 0 AS ReworkEffort, 0 AS TestEffort FROM
Input_Parameters, Project_Activity_Efforts WHERE (Project_Id =
ProjectId AND Phase_Id = PhaseId AND Step_Id = StepId AND '. Make
sure it exists and that its name is spelled correctly.

The full text of the query does refer to that:

SELECT Project_Id, Run_Set_Id, Run_Id, SUM(DevEffort) AS Dev_Effort,
SUM(InspEffort) AS Insp_Effort, SUM(ReworkEffort) AS Rework_Effort,
SUM(TestEffort) AS Test_Effort
FROM [SELECT Project_Id, Run_Set_Id, Run_Id, SUM(Activity_Effort) AS
DevEffort, 0 AS InspEffort, 0 AS ReworkEffort, 0 AS TestEffort FROM
Input_Parameters, Project_Activity_Efforts WHERE (Project_Id =
ProjectId AND Phase_Id = PhaseId AND Step_Id = StepId AND ] AS [%
$##@_Alias]
GROUP BY Project_Id, Run_Set_Id, Run_Id;

But that was not the text of the query before I tried to look at the
Object Dependencies. It was:

SELECT Project_Id, Run_Set_Id, Run_Id, SUM(DevEffort) AS Dev_Effort,
SUM(InspEffort) AS Insp_Effort, SUM(ReworkEffort) AS Rework_Effort,
SUM(TestEffort) AS Test_Effort
FROM [SELECT Project_Id, Run_Set_Id, Run_Id, SUM(Activity_Effort) AS
DevEffort, 0 AS InspEffort, 0 AS ReworkEffort, 0 AS TestEffort
FROM Input_Parameters, Project_Activity_Efforts
WHERE (Project_Id = ProjectId AND Phase_Id = PhaseId AND Step_Id =
StepId AND ActivityEffortType = 1)
GROUP BY Project_Id, Run_Set_Id, Run_Id
UNION
SELECT Project_Id, Run_Set_Id, Run_Id, 0 AS DevEffort,
SUM(Activity_Effort) AS InspEffort, 0 AS ReworkEffort, 0 AS TestEffort
FROM Input_Parameters, Project_Activity_Efforts
WHERE (Project_Id = ProjectId AND Phase_Id = PhaseId AND Step_Id =
StepId AND ActivityEffortType = 2)
GROUP BY Project_Id, Run_Set_Id, Run_Id
UNION
SELECT Project_Id, Run_Set_Id, Run_Id, 0 AS DevEffort, 0 AS
InspEffort, SUM(Activity_Effort) AS ReworkEffort, 0 AS TestEffort
FROM Input_Parameters, Project_Activity_Efforts
WHERE (Project_Id = ProjectId AND Phase_Id = PhaseId AND Step_Id =
StepId AND ActivityEffortType = 3)
GROUP BY Project_Id, Run_Set_Id, Run_Id
UNION SELECT Project_Id, Run_Set_Id, Run_Id, 0 AS DevEffort, 0 AS
InspEffort, 0 AS ReworkEffort, SUM(Activity_Effort) AS TestEffort
FROM Input_Parameters, Project_Activity_Efforts
WHERE (Project_Id = ProjectId AND Phase_Id = PhaseId AND Step_Id =
StepId AND ActivityEffortType = 4)
GROUP BY Project_Id, Run_Set_Id, Run_Id]. AS [%$##@_Alias]
GROUP BY Project_Id, Run_Set_Id, Run_Id;

Yes, this is a big ugly legacy query.

Anyway, I can copy the correct text back into the query and it seems
to work, but why did it break in the first place?

Also, this database supports an application that our customers use.
What if our customers want to track Object Dependencies? Can we
prevent that? Isn't 'Track Name Autocorrect' just supposed to track
changes? Why is it breaking my queries?