Query still kicking me out.. SQL Statement listed as well.

C

Cynthia

I have created over 34 queries in my database and up until
recently they all worked fine. Now 2 of them will not
work. Here's the deal.

When I click on the query in question it opens up in the
Datasheet view just fine.

Then I switch to the design view of the database whereby I
specify the criteria that I want for the fields. That
works fine as well.
I hit the execute key and it returns the values that I
specified in the datasheet view just fine.
However, when I go and click on the save button regardless
of whether I'm in the datasheet view or the design view, I
am kicked out of the database and returned to the drive
whereby I have to open the database all over again. It
will not save the changes to the criteria whatsoever.

It also creates another database with a padlock in the
upper right hand corner and states that the type of
application is "Microsoft Access Record Locking
Information with the size being 1KB.

I don't know what's wrong because it only does this on 2
of the 34 queries. The other 32 queries work just fine.
I've deleted these two queries and tried recreating them
but to no avail.

Any suggestions.

The SQL statments are as follows. I cannot change
anything below. Each time I try, I get thrown out and the
Record locking Information database is created. I no
longer have the new queries that I tried re-creating
because I deleted them when they wouldn't work.

SELECT [MonthlyDRRWorksheet].[RevMth/Yr],
[MonthlyDRRWorksheet].[RecordReviewDate],
[MonthlyDRRWorksheet].[DrrQraSubmit], [Hospital].
[HospitalSystem], [Hospital].[DartsCode],
[Hospital_Region].[Region], [Hospital].[Name],
[MonthlyDRRWorksheet].[VentCallsDRL],
[MonthlyDRRWorksheet]![IptDthsMortList]+
[MonthlyDRRWorksheet]![TramErDthsMortList]+
[MonthlyDRRWorksheet]![DthsCalledDRLNotOnMortLst]-
[MonthlyDRRWorksheet]![StillbornDthsMortList]-
[MonthlyDRRWorksheet]![DthsLstTwiceMortList]-
[MonthlyDRRWorksheet]![DidNotDieMortList]-
[MonthlyDRRWorksheet]![PtbelongtoSisterFacility] AS
TotalHospitalDeaths, [MonthlyDRRWorksheet]![BeginCallsDRL]+
[MonthlyDRRWorksheet]![DiedCurrMthCallMdePrvMth]+
[MonthlyDRRWorksheet]![DiedCurrMthCallMdeInFoll]-
[MonthlyDRRWorksheet]![DthsCalledTwiceDRL]-
[MonthlyDRRWorksheet]![StillbornDthsCalledDRL]-
[MonthlyDRRWorksheet]![DidNotDieDRL]-[MonthlyDRRWorksheet]!
[SnfRehabHospCallsDRL]-[MonthlyDRRWorksheet]!
[CallMdeCurrMthDiedFollMth] AS TotalStatlineCalls,
Format$([MonthlyDRRWorksheet].[RevMth/Yr],'mmmm yyyy') AS
DeathMthYr, [Hospital_Region].[EndDate]
FROM (Hospital RIGHT JOIN MonthlyDRRWorksheet ON
[Hospital].[DartsCode]=[MonthlyDRRWorksheet].[DartsCode])
LEFT JOIN Hospital_Region ON [MonthlyDRRWorksheet].
[DartsCode]=[Hospital_Region].[DartsCode]
GROUP BY [MonthlyDRRWorksheet].[RevMth/Yr],
[MonthlyDRRWorksheet].[RecordReviewDate],
[MonthlyDRRWorksheet].[DrrQraSubmit], [Hospital].
[HospitalSystem], [Hospital].[DartsCode],
[Hospital_Region].[Region], [Hospital].[Name],
[MonthlyDRRWorksheet].[VentCallsDRL],
[MonthlyDRRWorksheet]![IptDthsMortList]+
[MonthlyDRRWorksheet]![TramErDthsMortList]+
[MonthlyDRRWorksheet]![DthsCalledDRLNotOnMortLst]-
[MonthlyDRRWorksheet]![StillbornDthsMortList]-
[MonthlyDRRWorksheet]![DthsLstTwiceMortList]-
[MonthlyDRRWorksheet]![DidNotDieMortList]-
[MonthlyDRRWorksheet]![PtbelongtoSisterFacility],
[MonthlyDRRWorksheet]![BeginCallsDRL]+
[MonthlyDRRWorksheet]![DiedCurrMthCallMdePrvMth]+
[MonthlyDRRWorksheet]![DiedCurrMthCallMdeInFoll]-
[MonthlyDRRWorksheet]![DthsCalledTwiceDRL]-
[MonthlyDRRWorksheet]![StillbornDthsCalledDRL]-
[MonthlyDRRWorksheet]![DidNotDieDRL]-[MonthlyDRRWorksheet]!
[SnfRehabHospCallsDRL]-[MonthlyDRRWorksheet]!
[CallMdeCurrMthDiedFollMth], Format$([MonthlyDRRWorksheet].
[RevMth/Yr],'mmmm yyyy'), [Hospital_Region].[EndDate],
[MonthlyDRRWorksheet].[Reviewee]
HAVING (((MonthlyDRRWorksheet.[RevMth/Yr]) Between
#1/1/2003# And #6/30/2003#) AND ((Hospital.HospitalSystem)
Like "meth*") AND ((Hospital_Region.EndDate) Is Null))
WITH OWNERACCESS OPTION;
 
J

John Viescas

Cynthia-

Sounds like you have a corrupted database. Create a new, empty database,
and import all the objects from the old one. You might find that Import
blows up on some objects, so you'll need to skip those and rebuild from
scratch.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out" (coming soon)
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Cynthia said:
I have created over 34 queries in my database and up until
recently they all worked fine. Now 2 of them will not
work. Here's the deal.

When I click on the query in question it opens up in the
Datasheet view just fine.

Then I switch to the design view of the database whereby I
specify the criteria that I want for the fields. That
works fine as well.
I hit the execute key and it returns the values that I
specified in the datasheet view just fine.
However, when I go and click on the save button regardless
of whether I'm in the datasheet view or the design view, I
am kicked out of the database and returned to the drive
whereby I have to open the database all over again. It
will not save the changes to the criteria whatsoever.

It also creates another database with a padlock in the
upper right hand corner and states that the type of
application is "Microsoft Access Record Locking
Information with the size being 1KB.

I don't know what's wrong because it only does this on 2
of the 34 queries. The other 32 queries work just fine.
I've deleted these two queries and tried recreating them
but to no avail.

Any suggestions.

The SQL statments are as follows. I cannot change
anything below. Each time I try, I get thrown out and the
Record locking Information database is created. I no
longer have the new queries that I tried re-creating
because I deleted them when they wouldn't work.

SELECT [MonthlyDRRWorksheet].[RevMth/Yr],
[MonthlyDRRWorksheet].[RecordReviewDate],
[MonthlyDRRWorksheet].[DrrQraSubmit], [Hospital].
[HospitalSystem], [Hospital].[DartsCode],
[Hospital_Region].[Region], [Hospital].[Name],
[MonthlyDRRWorksheet].[VentCallsDRL],
[MonthlyDRRWorksheet]![IptDthsMortList]+
[MonthlyDRRWorksheet]![TramErDthsMortList]+
[MonthlyDRRWorksheet]![DthsCalledDRLNotOnMortLst]-
[MonthlyDRRWorksheet]![StillbornDthsMortList]-
[MonthlyDRRWorksheet]![DthsLstTwiceMortList]-
[MonthlyDRRWorksheet]![DidNotDieMortList]-
[MonthlyDRRWorksheet]![PtbelongtoSisterFacility] AS
TotalHospitalDeaths, [MonthlyDRRWorksheet]![BeginCallsDRL]+
[MonthlyDRRWorksheet]![DiedCurrMthCallMdePrvMth]+
[MonthlyDRRWorksheet]![DiedCurrMthCallMdeInFoll]-
[MonthlyDRRWorksheet]![DthsCalledTwiceDRL]-
[MonthlyDRRWorksheet]![StillbornDthsCalledDRL]-
[MonthlyDRRWorksheet]![DidNotDieDRL]-[MonthlyDRRWorksheet]!
[SnfRehabHospCallsDRL]-[MonthlyDRRWorksheet]!
[CallMdeCurrMthDiedFollMth] AS TotalStatlineCalls,
Format$([MonthlyDRRWorksheet].[RevMth/Yr],'mmmm yyyy') AS
DeathMthYr, [Hospital_Region].[EndDate]
FROM (Hospital RIGHT JOIN MonthlyDRRWorksheet ON
[Hospital].[DartsCode]=[MonthlyDRRWorksheet].[DartsCode])
LEFT JOIN Hospital_Region ON [MonthlyDRRWorksheet].
[DartsCode]=[Hospital_Region].[DartsCode]
GROUP BY [MonthlyDRRWorksheet].[RevMth/Yr],
[MonthlyDRRWorksheet].[RecordReviewDate],
[MonthlyDRRWorksheet].[DrrQraSubmit], [Hospital].
[HospitalSystem], [Hospital].[DartsCode],
[Hospital_Region].[Region], [Hospital].[Name],
[MonthlyDRRWorksheet].[VentCallsDRL],
[MonthlyDRRWorksheet]![IptDthsMortList]+
[MonthlyDRRWorksheet]![TramErDthsMortList]+
[MonthlyDRRWorksheet]![DthsCalledDRLNotOnMortLst]-
[MonthlyDRRWorksheet]![StillbornDthsMortList]-
[MonthlyDRRWorksheet]![DthsLstTwiceMortList]-
[MonthlyDRRWorksheet]![DidNotDieMortList]-
[MonthlyDRRWorksheet]![PtbelongtoSisterFacility],
[MonthlyDRRWorksheet]![BeginCallsDRL]+
[MonthlyDRRWorksheet]![DiedCurrMthCallMdePrvMth]+
[MonthlyDRRWorksheet]![DiedCurrMthCallMdeInFoll]-
[MonthlyDRRWorksheet]![DthsCalledTwiceDRL]-
[MonthlyDRRWorksheet]![StillbornDthsCalledDRL]-
[MonthlyDRRWorksheet]![DidNotDieDRL]-[MonthlyDRRWorksheet]!
[SnfRehabHospCallsDRL]-[MonthlyDRRWorksheet]!
[CallMdeCurrMthDiedFollMth], Format$([MonthlyDRRWorksheet].
[RevMth/Yr],'mmmm yyyy'), [Hospital_Region].[EndDate],
[MonthlyDRRWorksheet].[Reviewee]
HAVING (((MonthlyDRRWorksheet.[RevMth/Yr]) Between
#1/1/2003# And #6/30/2003#) AND ((Hospital.HospitalSystem)
Like "meth*") AND ((Hospital_Region.EndDate) Is Null))
WITH OWNERACCESS OPTION;
Click to expand...
 
Top