Is it possible for a query to blow up the database?

[KevinS]

I ran this query (not an update or append) multiple times with no problem.

This one time I got an error and suddenly nobody could get in. It was a
syntax error having to do with a form that I didn't even touch.

so...back to my question:
Is it possible for a query to blow up the database?

 

[Klatuu]

"I got an error" is like telling the doctor "I feel bad" and expecting to get
the right treatment.

The error number and text would be helpful.

A query can blow up any time it wants to. Two possible causes come to mind.
One is incorrect or corrupt data in the tables. The other is corruption in
your query or form object. That is, you could have some database corruption
going on.

When you say nobody can get in leads me to believe you have multiple users
using the same front end database. This is a very common cause of corruption.

I would suggest reloading the database from your backup. There are also
other ways to attempt to recover from corruption.

The correct configuration for an Access application is to have your database
split. Each user should have a copy of the front end (application objects)
database on their own computers. The back end (tables, relationships,
indexes) should be on a shared folder where all users have full rights.

 

[KevinS]

Thank you. It sounds like the Access 97 database is getting corrupt. We
restored from a few days before and it works fine but we lost the data.

I didn't want to concentrate on the error message because at this stage it
was irrelevant.

My boss was accusing me of blowing up the database by running a query.

Query below.

SELECT DISTINCT OpenCases.ASCI, OpenCases.ASCISequence,
OpenCases.Supervisor, OpenCases.POCName, PCCases.KFName, PCCases.KLName,
PCCases.Relation, PCCases.KDOB, OpenCases.Gender, PCCases.MaritalStatus,
OpenCases.Race, [IntakeKinship Query].FName, [IntakeKinship Query].LName,
IntakeKinship.Race, IntakeKinship.Edu, IntakeKinship.Gender, OpenCases.Name,
OpenCases.LName, OpenCases.DOB
FROM (Index1 RIGHT JOIN ([IntakeKinship Query] RIGHT JOIN (Discharge RIGHT
JOIN (IntakeKinship RIGHT JOIN (OpenCases LEFT JOIN PCCases ON
OpenCases.IntakeChild.CaseID = PCCases.CaseID) ON IntakeKinship.KSSN =
PCCases.KSSN) ON Discharge.CaseID = OpenCases.IntakeChild.CaseID) ON
[IntakeKinship Query].KSSN = PCCases.KSSN) ON Index1.Code =
PCCases.MaritalStatus) INNER JOIN Staff ON OpenCases.Supervisor =
Staff.StaffID
GROUP BY OpenCases.ASCI, OpenCases.ASCISequence, OpenCases.Supervisor,
OpenCases.POCName, PCCases.KFName, PCCases.KLName, PCCases.Relation,
PCCases.KDOB, OpenCases.Gender, PCCases.MaritalStatus, OpenCases.Race,
[IntakeKinship Query].FName, [IntakeKinship Query].LName, IntakeKinship.Race,
IntakeKinship.Edu, IntakeKinship.Gender, OpenCases.Name, OpenCases.LName,
OpenCases.DOB, Discharge.CloseDate
ORDER BY OpenCases.Supervisor, OpenCases.POCName;

 

[Tom van Stiphout]

On Tue, 7 Jul 2009 05:50:01 -0700, KevinS

Very little is impossible when it comes to computers and databases,
but this is exceedingly unlikely.

-Tom.
Microsoft Access MVP

 

[Klatuu]

Error messages are never irrelevant.

You problem is most likely your configuration. If you lost data, that means
you do not have the database split. You can expect reoccurances if you do
not correct that.
--
Dave Hargis, Microsoft Access MVP

Thank you. It sounds like the Access 97 database is getting corrupt. We
restored from a few days before and it works fine but we lost the data.

I didn't want to concentrate on the error message because at this stage it
was irrelevant.

My boss was accusing me of blowing up the database by running a query.

Query below.

SELECT DISTINCT OpenCases.ASCI, OpenCases.ASCISequence,
OpenCases.Supervisor, OpenCases.POCName, PCCases.KFName, PCCases.KLName,
PCCases.Relation, PCCases.KDOB, OpenCases.Gender, PCCases.MaritalStatus,
OpenCases.Race, [IntakeKinship Query].FName, [IntakeKinship Query].LName,
IntakeKinship.Race, IntakeKinship.Edu, IntakeKinship.Gender, OpenCases.Name,
OpenCases.LName, OpenCases.DOB
FROM (Index1 RIGHT JOIN ([IntakeKinship Query] RIGHT JOIN (Discharge RIGHT
JOIN (IntakeKinship RIGHT JOIN (OpenCases LEFT JOIN PCCases ON
OpenCases.IntakeChild.CaseID = PCCases.CaseID) ON IntakeKinship.KSSN =
PCCases.KSSN) ON Discharge.CaseID = OpenCases.IntakeChild.CaseID) ON
[IntakeKinship Query].KSSN = PCCases.KSSN) ON Index1.Code =
PCCases.MaritalStatus) INNER JOIN Staff ON OpenCases.Supervisor =
Staff.StaffID
GROUP BY OpenCases.ASCI, OpenCases.ASCISequence, OpenCases.Supervisor,
OpenCases.POCName, PCCases.KFName, PCCases.KLName, PCCases.Relation,
PCCases.KDOB, OpenCases.Gender, PCCases.MaritalStatus, OpenCases.Race,
[IntakeKinship Query].FName, [IntakeKinship Query].LName, IntakeKinship.Race,
IntakeKinship.Edu, IntakeKinship.Gender, OpenCases.Name, OpenCases.LName,
OpenCases.DOB, Discharge.CloseDate
ORDER BY OpenCases.Supervisor, OpenCases.POCName;

--
Is it the times or the Zeitgiest?

"I got an error" is like telling the doctor "I feel bad" and expecting to get
the right treatment.

The error number and text would be helpful.

A query can blow up any time it wants to. Two possible causes come to mind.
One is incorrect or corrupt data in the tables. The other is corruption in
your query or form object. That is, you could have some database corruption
going on.

When you say nobody can get in leads me to believe you have multiple users
using the same front end database. This is a very common cause of corruption.

I would suggest reloading the database from your backup. There are also
other ways to attempt to recover from corruption.

The correct configuration for an Access application is to have your database
split. Each user should have a copy of the front end (application objects)
database on their own computers. The back end (tables, relationships,
indexes) should be on a shared folder where all users have full rights.

 

[KevinS]

You are right - the database is not split. It was a syntax error.
Thank you,
Kevin
--
Is it the times or the Zeitgiest?

Error messages are never irrelevant.

You problem is most likely your configuration. If you lost data, that means
you do not have the database split. You can expect reoccurances if you do
not correct that.
--
Dave Hargis, Microsoft Access MVP

Thank you. It sounds like the Access 97 database is getting corrupt. We
restored from a few days before and it works fine but we lost the data.

I didn't want to concentrate on the error message because at this stage it
was irrelevant.

My boss was accusing me of blowing up the database by running a query.

Query below.

SELECT DISTINCT OpenCases.ASCI, OpenCases.ASCISequence,
OpenCases.Supervisor, OpenCases.POCName, PCCases.KFName, PCCases.KLName,
PCCases.Relation, PCCases.KDOB, OpenCases.Gender, PCCases.MaritalStatus,
OpenCases.Race, [IntakeKinship Query].FName, [IntakeKinship Query].LName,
IntakeKinship.Race, IntakeKinship.Edu, IntakeKinship.Gender, OpenCases.Name,
OpenCases.LName, OpenCases.DOB
FROM (Index1 RIGHT JOIN ([IntakeKinship Query] RIGHT JOIN (Discharge RIGHT
JOIN (IntakeKinship RIGHT JOIN (OpenCases LEFT JOIN PCCases ON
OpenCases.IntakeChild.CaseID = PCCases.CaseID) ON IntakeKinship.KSSN =
PCCases.KSSN) ON Discharge.CaseID = OpenCases.IntakeChild.CaseID) ON
[IntakeKinship Query].KSSN = PCCases.KSSN) ON Index1.Code =
PCCases.MaritalStatus) INNER JOIN Staff ON OpenCases.Supervisor =
Staff.StaffID
GROUP BY OpenCases.ASCI, OpenCases.ASCISequence, OpenCases.Supervisor,
OpenCases.POCName, PCCases.KFName, PCCases.KLName, PCCases.Relation,
PCCases.KDOB, OpenCases.Gender, PCCases.MaritalStatus, OpenCases.Race,
[IntakeKinship Query].FName, [IntakeKinship Query].LName, IntakeKinship.Race,
IntakeKinship.Edu, IntakeKinship.Gender, OpenCases.Name, OpenCases.LName,
OpenCases.DOB, Discharge.CloseDate
ORDER BY OpenCases.Supervisor, OpenCases.POCName;

--
Is it the times or the Zeitgiest?

"I got an error" is like telling the doctor "I feel bad" and expecting to get
the right treatment.

The error number and text would be helpful.

A query can blow up any time it wants to. Two possible causes come to mind.
One is incorrect or corrupt data in the tables. The other is corruption in
your query or form object. That is, you could have some database corruption
going on.

When you say nobody can get in leads me to believe you have multiple users
using the same front end database. This is a very common cause of corruption.

I would suggest reloading the database from your backup. There are also
other ways to attempt to recover from corruption.

The correct configuration for an Access application is to have your database
split. Each user should have a copy of the front end (application objects)
database on their own computers. The back end (tables, relationships,
indexes) should be on a shared folder where all users have full rights.
--
Dave Hargis, Microsoft Access MVP


:

I ran this query (not an update or append) multiple times with no problem.

This one time I got an error and suddenly nobody could get in. It was a
syntax error having to do with a form that I didn't even touch.

so...back to my question:
Is it possible for a query to blow up the database?

 

[Jerry Whittle]

I don't see anything the the query that should cause such a problem. Of
course you are calling on other queries an that could be trouble.

It is a rather complicated query - unnecessarily so. You have a Distinct
Clause at the top then a long Group By at the bottom with essentially the
same fields with the addition of Discharge.CloseDate. Since you aren't doing
any summing or other such math, dump the entire Group By clause. I bet that
you get the same results, but faster.

And Klatuu is very correct about splitting the database. If you can't do
that, at least take nightly backups so that you won't lose as much data when
it happens again.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Thank you. It sounds like the Access 97 database is getting corrupt. We
restored from a few days before and it works fine but we lost the data.

I didn't want to concentrate on the error message because at this stage it
was irrelevant.

My boss was accusing me of blowing up the database by running a query.

Query below.

SELECT DISTINCT OpenCases.ASCI, OpenCases.ASCISequence,
OpenCases.Supervisor, OpenCases.POCName, PCCases.KFName, PCCases.KLName,
PCCases.Relation, PCCases.KDOB, OpenCases.Gender, PCCases.MaritalStatus,
OpenCases.Race, [IntakeKinship Query].FName, [IntakeKinship Query].LName,
IntakeKinship.Race, IntakeKinship.Edu, IntakeKinship.Gender, OpenCases.Name,
OpenCases.LName, OpenCases.DOB
FROM (Index1 RIGHT JOIN ([IntakeKinship Query] RIGHT JOIN (Discharge RIGHT
JOIN (IntakeKinship RIGHT JOIN (OpenCases LEFT JOIN PCCases ON
OpenCases.IntakeChild.CaseID = PCCases.CaseID) ON IntakeKinship.KSSN =
PCCases.KSSN) ON Discharge.CaseID = OpenCases.IntakeChild.CaseID) ON
[IntakeKinship Query].KSSN = PCCases.KSSN) ON Index1.Code =
PCCases.MaritalStatus) INNER JOIN Staff ON OpenCases.Supervisor =
Staff.StaffID
GROUP BY OpenCases.ASCI, OpenCases.ASCISequence, OpenCases.Supervisor,
OpenCases.POCName, PCCases.KFName, PCCases.KLName, PCCases.Relation,
PCCases.KDOB, OpenCases.Gender, PCCases.MaritalStatus, OpenCases.Race,
[IntakeKinship Query].FName, [IntakeKinship Query].LName, IntakeKinship.Race,
IntakeKinship.Edu, IntakeKinship.Gender, OpenCases.Name, OpenCases.LName,
OpenCases.DOB, Discharge.CloseDate
ORDER BY OpenCases.Supervisor, OpenCases.POCName;

--
Is it the times or the Zeitgiest?

"I got an error" is like telling the doctor "I feel bad" and expecting to get
the right treatment.

The error number and text would be helpful.

A query can blow up any time it wants to. Two possible causes come to mind.
One is incorrect or corrupt data in the tables. The other is corruption in
your query or form object. That is, you could have some database corruption
going on.

When you say nobody can get in leads me to believe you have multiple users
using the same front end database. This is a very common cause of corruption.

I would suggest reloading the database from your backup. There are also
other ways to attempt to recover from corruption.

The correct configuration for an Access application is to have your database
split. Each user should have a copy of the front end (application objects)
database on their own computers. The back end (tables, relationships,
indexes) should be on a shared folder where all users have full rights.

 

[David W. Fenton]

I ran this query (not an update or append) multiple times with no
problem.

This one time I got an error and suddenly nobody could get in. It
was a syntax error having to do with a form that I didn't even
touch.

so...back to my question:
Is it possible for a query to blow up the database?

Not without help from something else going wrong, such as a missed
disk write (which is going to be caused by something that should
never happen, such as a software glitch or a hardware error).

 

[David W. Fenton]

My boss was accusing me of blowing up the database by running a
query.

Your boss knows nothing of value in helping you resolve the problem.

The problem lies outside your query, though it could be, for
instance, that your query causes Jet to attempt to read corrupt
parts of your data file, which then puts the database in an
unopenable state.

 

[David W. Fenton]

=?Utf-8?B?SmVycnkgV2hpdHRsZQ==?=

And Klatuu is very correct about splitting the database. If you
can't do that, at least take nightly backups so that you won't
lose as much data when it happens again.

If the database is not split, then there's no point in working on
troubleshooting anything else.

 

[Tom van Stiphout]

On Tue, 7 Jul 2009 06:37:01 -0700, KevinS

That sounds like an uninformed opinion.
Perhaps the boss can quote some authorative sources?

-Tom.
Microsoft Access MVP