Has this ever happened to you?

[Laurel]

I have an application that's been running with few problesm for a couple of
years. Twice now, a table has gotten subtly corrupted so that a select on a
query doesn't work properly inside of code, but it does work in the query
builder. By not working "properly," I mean that it retrieves only one
record, the first record, when it should be retrieving hundreds. I'm sure of
the query, because I cut it out of the code in the debugger and paste it
into the builder and execute it. One place it retrieves 1 record and the
other place the expected hundreds.

I'm sure the problem is the table, because both times I was able to make the
problem go away be reverting to an older copy of the table and re-entering
data by hand. The first time it happened, I don't know exactly what piece
of data caused the change. But the second time it happened I determined
that the problem was caused when 15 records were deleted from the table.
That is ANY fifteen records. The problem first happened when I deleted 17
records with a DELETE query. Then I deleted several at a time in the table
builder. And then I deleted one at a time in the table builder, running the
application after each delete. Three times the problem cropped up after I
deleted the 15th row. In each case I did NOT delete the row that had caused
the problem previously.

Truly weird!

Any advice about how to clean up the table so this never happens again??????

Here are various details.

0. The table that causes the problem is tblStudents.

1. The Select statement that works in the query builder but not in the code.
select * from qryClassSummaryDetailBaseDebug where class_code = 'ml2' and
student_id <> -1

2. The definition of the query itself.
SELECT [first_name] & " " & [last_name] AS Full_Name, tblScores.Score_Date
AS Week_of, tblScores.Score_Date, tblScores.Safety, tblScores.Respect,
tblScores.Responsibility, tblScores.Bonus, tblScores.Safety AS TotalScores,
tblStudents.Student_ID, tblperiods.Period_Code, tblperiods.Sort_Order,
tblStudents.Class_Code
FROM tblStudents INNER JOIN (tblperiods INNER JOIN tblScores ON
tblperiods.Period_Code = tblScores.Period_Code) ON tblStudents.Student_ID =
tblScores.Student_ID
ORDER BY tblStudents.Student_ID;

3. The code that fails. I know the query is returning only one row both
because of the value of RecordCount and because of the display in the
subsequent report.
'3/26 DEBUG
'ls_temp = "Select * from qryClassSummaryDetail " & as_where
'Set rstClassDetail = CurrentDb.OpenRecordset(ls_temp)
Set rstClassDetail = CurrentDb.OpenRecordset("select * from
qryClassSummaryDetailBaseDebug where class_code = 'ml2' and student_id
<> -1")
'END 3/26 DEBU


If rstClassDetail.RecordCount = 0 Then
' liReturn = MsgBox("There are no scores for this class and time
period")
GoTo Exit_CalcSummaryInfo
End If

 

[Jeff Boyce]

Laurel

You mentioned nothing about the environment in which this Access
application is running...

Is this a standard "copy of front-end on each PC"/"single back-end on LAN
Server" design?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have an application that's been running with few problesm for a couple of
years. Twice now, a table has gotten subtly corrupted so that a select on
a query doesn't work properly inside of code, but it does work in the query
builder. By not working "properly," I mean that it retrieves only one
record, the first record, when it should be retrieving hundreds. I'm sure
of the query, because I cut it out of the code in the debugger and paste it
into the builder and execute it. One place it retrieves 1 record and the
other place the expected hundreds.

I'm sure the problem is the table, because both times I was able to make
the problem go away be reverting to an older copy of the table and
re-entering data by hand. The first time it happened, I don't know
exactly what piece of data caused the change. But the second time it
happened I determined that the problem was caused when 15 records were
deleted from the table. That is ANY fifteen records. The problem first
happened when I deleted 17 records with a DELETE query. Then I deleted
several at a time in the table builder. And then I deleted one at a time
in the table builder, running the application after each delete. Three
times the problem cropped up after I deleted the 15th row. In each case I
did NOT delete the row that had caused the problem previously.

Truly weird!

Any advice about how to clean up the table so this never happens
again??????

Here are various details.

0. The table that causes the problem is tblStudents.

1. The Select statement that works in the query builder but not in the
code.
select * from qryClassSummaryDetailBaseDebug where class_code = 'ml2' and
student_id <> -1

2. The definition of the query itself.
SELECT [first_name] & " " & [last_name] AS Full_Name, tblScores.Score_Date
AS Week_of, tblScores.Score_Date, tblScores.Safety, tblScores.Respect,
tblScores.Responsibility, tblScores.Bonus, tblScores.Safety AS
TotalScores, tblStudents.Student_ID, tblperiods.Period_Code,
tblperiods.Sort_Order, tblStudents.Class_Code
FROM tblStudents INNER JOIN (tblperiods INNER JOIN tblScores ON
tblperiods.Period_Code = tblScores.Period_Code) ON tblStudents.Student_ID
= tblScores.Student_ID
ORDER BY tblStudents.Student_ID;

3. The code that fails. I know the query is returning only one row both
because of the value of RecordCount and because of the display in the
subsequent report.
'3/26 DEBUG
'ls_temp = "Select * from qryClassSummaryDetail " & as_where
'Set rstClassDetail = CurrentDb.OpenRecordset(ls_temp)
Set rstClassDetail = CurrentDb.OpenRecordset("select * from
qryClassSummaryDetailBaseDebug where class_code = 'ml2' and student_id
<> -1")
'END 3/26 DEBU


If rstClassDetail.RecordCount = 0 Then
' liReturn = MsgBox("There are no scores for this class and time
period")
GoTo Exit_CalcSummaryInfo
End If

 

[John W. Vinson]

3. The code that fails. I know the query is returning only one row both
because of the value of RecordCount and because of the display in the
subsequent report.

Just a doublecheck - are you using a MoveLast after opening the recordset?
RecordCount will be 1 (or 0) when the code resumes, since Access won't fully
populate the recordset until it's needed.

The report is worrisome though! How are you assigning the report's
recordsource? just to the SQL string?

John W. Vinson [MVP]

 

[Laurel]

It's a front-end/back-end design, but both databases are on the same pc,
running Access 2003, Windows XP Professional.
It happens when moved from one PC to another, at least the first one did.
It happened on the production machine, and continued when moved to mine. I
think the production machine might have different versions of Windows and/or
Access, but can't check that just now - definitely not Windows Pro.

Laurel

You mentioned nothing about the environment in which this Access
application is running...

Is this a standard "copy of front-end on each PC"/"single back-end on LAN
Server" design?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have an application that's been running with few problesm for a couple
of years. Twice now, a table has gotten subtly corrupted so that a select
on a query doesn't work properly inside of code, but it does work in the
query builder. By not working "properly," I mean that it retrieves only
one record, the first record, when it should be retrieving hundreds. I'm
sure of the query, because I cut it out of the code in the debugger and
paste it into the builder and execute it. One place it retrieves 1 record
and the other place the expected hundreds.

I'm sure the problem is the table, because both times I was able to make
the problem go away be reverting to an older copy of the table and
re-entering data by hand. The first time it happened, I don't know
exactly what piece of data caused the change. But the second time it
happened I determined that the problem was caused when 15 records were
deleted from the table. That is ANY fifteen records. The problem first
happened when I deleted 17 records with a DELETE query. Then I deleted
several at a time in the table builder. And then I deleted one at a time
in the table builder, running the application after each delete. Three
times the problem cropped up after I deleted the 15th row. In each case
I did NOT delete the row that had caused the problem previously.

Truly weird!

Any advice about how to clean up the table so this never happens
again??????

Here are various details.

0. The table that causes the problem is tblStudents.

1. The Select statement that works in the query builder but not in the
code.
select * from qryClassSummaryDetailBaseDebug where class_code = 'ml2' and
student_id <> -1

2. The definition of the query itself.
SELECT [first_name] & " " & [last_name] AS Full_Name,
tblScores.Score_Date AS Week_of, tblScores.Score_Date, tblScores.Safety,
tblScores.Respect, tblScores.Responsibility, tblScores.Bonus,
tblScores.Safety AS TotalScores, tblStudents.Student_ID,
tblperiods.Period_Code, tblperiods.Sort_Order, tblStudents.Class_Code
FROM tblStudents INNER JOIN (tblperiods INNER JOIN tblScores ON
tblperiods.Period_Code = tblScores.Period_Code) ON tblStudents.Student_ID
= tblScores.Student_ID
ORDER BY tblStudents.Student_ID;

3. The code that fails. I know the query is returning only one row both
because of the value of RecordCount and because of the display in the
subsequent report.
'3/26 DEBUG
'ls_temp = "Select * from qryClassSummaryDetail " & as_where
'Set rstClassDetail = CurrentDb.OpenRecordset(ls_temp)
Set rstClassDetail = CurrentDb.OpenRecordset("select * from
qryClassSummaryDetailBaseDebug where class_code = 'ml2' and student_id
<> -1")
'END 3/26 DEBU


If rstClassDetail.RecordCount = 0 Then
' liReturn = MsgBox("There are no scores for this class and time
period")
GoTo Exit_CalcSummaryInfo
End If

 

[Laurel]

THANKS!
I do this almost everywhere, but not here. And, since it almost never has
problems, it didn't even cross my mind.
But this fixes the problem.