Database Closing

  • Thread starter Thread starter Rebecca
  • Start date Start date
R

Rebecca

My database is closing on me without saving the changes I make to a query.
For example, I go to the query, make the changes, hit the ! to run the
query, get the results I wanted, click the X to close the query, and it
closes my whole database!!!! So when I open the database again, I go to the
same query and the changes were not saved. I do it again, but instead of
hitting the X to close, I hit the little disk in the toolbar to save, and it
closes the database. I open again.. and it was not saved.. any ideas what
could have happend to my database? I did a back up and then compacted it
twice. re-opened the database and opened a few different querys and forms
and closed them with the X.. no problem. But when I make the changes to the
query and hit the X,,,, database closes....????

Thanks in advance

Rebecca S.
 
Assuming Access 2000 or later, try this:

1. From the Tools menu, choose Options. On the General Tab, uncheck the
boxes under:
Name AutoCorrect.

2. Open the query in SQL View.
Copy the query statement.
Paste somewhere (e.g. into Notepad).
Close the query. If Access crashes, reopen it.

3. Delete the problem query. (We will recreate it at step 5).

4. Compact the database: Tools | Database Utilities | Compact.

5. Create a new query.
Switch to SQL View (View menu).
Paste the SQL back in here.
Save the query.

You may need to drag the tables around in the upper window of query design
view, but this should give you a working query if the original were corrupt.

If that does not work for you, post back, and include:
- the SQL statement from the query.
- your version of Access, and the service pack (from Help | About).
- the version of the file msjet40.dll (usually found in windows\system32).
 
Rebbeca:

Did you try re-creating the query and deleting the one
with that problem?

Cesar
 
Thank you, I believe that worked!
What does the boxes under Name AutoCorrect deal with? Will it effect
anything else in my database that I should be aware of?

Thanks,
Rebecca S.
 
Geez.. Maybe not working.. pefectly.

The problem... When I click to run the query it pops up a box asking "Enter
Parameter Value" Query2.MailCode

When I made the new query and pasted the SQL from the original, I named it
Query2 and when it worked I renamed it to the current name MAILINGADDRESS
WIP. That is the only changes I made, renaming it.

Here is the SQL :
SELECT [Member File].MemberID, [Member File]![title] & " " & [Member
File]![FirstName] & " " & [Member file]![middle] & " " & [member
file]![lastname] AS Membername, [Member File].SurName, masterFIRMinfo.Firm,
IIf([Member File]![MailCode]="A" Or [Member File]![MailCode] Is
Null,IIf([masterFIRMinfo]![FirmPOBox] Is
Null,[masterFIRMinfo]![FirmAddress1] & " " &
[masterFIRMinfo]![FirmAddress2],[masterFIRMinfo]![FirmPOBox]),IIf([Member
File]![MailCode]="H",IIf([Member File]![Home_Address1] Is
Null,[masterFIRMinfo]![FirmAddress1] & " " &
[masterFIRMinfo]![FirmAddress2],[Member File]![Home_Address1]))) AS Address,
IIf([Member File]![MailCode]="A" Or [masterFIRMinfo]![FirmPOBox] Is
Null,[masterFIRMinfo]![FirmCity] & ", " & [masterFIRMinfo]![FirmState] & "
" & [masterFIRMinfo]![FirmZIP],[masterFIRMinfo]![FirmPOBoxCity] & ", " &
[masterFIRMinfo]![FirmPOBoxState] & " " & [masterFIRMinfo]![FirmPOBoxZip])
AS CityStateZip, [Member File].MailCode, [Member File].Suspension, [Member
File].NonMember, IIf([Member File]![MailCode]="A" Or
[masterFIRMinfo]![FirmPOBox] Is
Null,[masterFIRMinfo]![FirmZIP],[masterFIRMinfo]![FirmPOBoxZip]) AS Zip,
[Member File].Spouse_Last
FROM masterFIRMinfo INNER JOIN [Member File] ON masterFIRMinfo.FirmNo =
[Member File].FirmNo
GROUP BY [Member File].MemberID, [Member File]![title] & " " & [Member
File]![FirstName] & " " & [Member file]![middle] & " " & [member
file]![lastname], [Member File].SurName, masterFIRMinfo.Firm, IIf([Member
File]![MailCode]="A" Or [Member File]![MailCode] Is
Null,IIf([masterFIRMinfo]![FirmPOBox] Is
Null,[masterFIRMinfo]![FirmAddress1] & " " &
[masterFIRMinfo]![FirmAddress2],[masterFIRMinfo]![FirmPOBox]),IIf([Member
File]![MailCode]="H",IIf([Member File]![Home_Address1] Is
Null,[masterFIRMinfo]![FirmAddress1] & " " &
[masterFIRMinfo]![FirmAddress2],[Member File]![Home_Address1]))),
IIf([Member File]![MailCode]="A" Or [masterFIRMinfo]![FirmPOBox] Is
Null,[masterFIRMinfo]![FirmCity] & ", " & [masterFIRMinfo]![FirmState] & "
" & [masterFIRMinfo]![FirmZIP],[masterFIRMinfo]![FirmPOBoxCity] & ", " &
[masterFIRMinfo]![FirmPOBoxState] & " " & [masterFIRMinfo]![FirmPOBoxZip]),
[Member File].MailCode, [Member File].Suspension, [Member File].NonMember,
IIf([Member File]![MailCode]="A" Or [masterFIRMinfo]![FirmPOBox] Is
Null,[masterFIRMinfo]![FirmZIP],[masterFIRMinfo]![FirmPOBoxZip]), [Member
File].Spouse_Last
HAVING ((([Member File].Suspension) Is Null) AND (([Member File].NonMember)
Is Null Or ([Member File].NonMember)="mail"));



Rebecca S.
 
i thought you would get better advice than i gave you in the other thread,
and i was right! but Allen probably croaked when he saw that SQL statement!
here's a quick fix that might work re the Query2 reference: first, try
closing and compacting the db, if you haven't already. if that doesn't work,
try:
copy the SQL code again, and paste it into a Word doc.
delete the query entirely.
open a new query, open the SQL pane and paste the SQL statement into it.
save the new query, naming it the "real* name you want it to have.

hth


Rebecca said:
Geez.. Maybe not working.. pefectly.

The problem... When I click to run the query it pops up a box asking "Enter
Parameter Value" Query2.MailCode

When I made the new query and pasted the SQL from the original, I named it
Query2 and when it worked I renamed it to the current name MAILINGADDRESS
WIP. That is the only changes I made, renaming it.

Here is the SQL :
SELECT [Member File].MemberID, [Member File]![title] & " " & [Member
File]![FirstName] & " " & [Member file]![middle] & " " & [member
file]![lastname] AS Membername, [Member File].SurName, masterFIRMinfo.Firm,
IIf([Member File]![MailCode]="A" Or [Member File]![MailCode] Is
Null,IIf([masterFIRMinfo]![FirmPOBox] Is
Null,[masterFIRMinfo]![FirmAddress1] & " " &
[masterFIRMinfo]![FirmAddress2],[masterFIRMinfo]![FirmPOBox]),IIf([Member
File]![MailCode]="H",IIf([Member File]![Home_Address1] Is
Null,[masterFIRMinfo]![FirmAddress1] & " " &
[masterFIRMinfo]![FirmAddress2],[Member File]![Home_Address1]))) AS Address,
IIf([Member File]![MailCode]="A" Or [masterFIRMinfo]![FirmPOBox] Is
Null,[masterFIRMinfo]![FirmCity] & ", " & [masterFIRMinfo]![FirmState] & "
" & [masterFIRMinfo]![FirmZIP],[masterFIRMinfo]![FirmPOBoxCity] & ", " &
[masterFIRMinfo]![FirmPOBoxState] & " " & [masterFIRMinfo]![FirmPOBoxZip])
AS CityStateZip, [Member File].MailCode, [Member File].Suspension, [Member
File].NonMember, IIf([Member File]![MailCode]="A" Or
[masterFIRMinfo]![FirmPOBox] Is
Null,[masterFIRMinfo]![FirmZIP],[masterFIRMinfo]![FirmPOBoxZip]) AS Zip,
[Member File].Spouse_Last
FROM masterFIRMinfo INNER JOIN [Member File] ON masterFIRMinfo.FirmNo =
[Member File].FirmNo
GROUP BY [Member File].MemberID, [Member File]![title] & " " & [Member
File]![FirstName] & " " & [Member file]![middle] & " " & [member
file]![lastname], [Member File].SurName, masterFIRMinfo.Firm, IIf([Member
File]![MailCode]="A" Or [Member File]![MailCode] Is
Null,IIf([masterFIRMinfo]![FirmPOBox] Is
Null,[masterFIRMinfo]![FirmAddress1] & " " &
[masterFIRMinfo]![FirmAddress2],[masterFIRMinfo]![FirmPOBox]),IIf([Member
File]![MailCode]="H",IIf([Member File]![Home_Address1] Is
Null,[masterFIRMinfo]![FirmAddress1] & " " &
[masterFIRMinfo]![FirmAddress2],[Member File]![Home_Address1]))),
IIf([Member File]![MailCode]="A" Or [masterFIRMinfo]![FirmPOBox] Is
Null,[masterFIRMinfo]![FirmCity] & ", " & [masterFIRMinfo]![FirmState] & "
" & [masterFIRMinfo]![FirmZIP],[masterFIRMinfo]![FirmPOBoxCity] & ", " &
[masterFIRMinfo]![FirmPOBoxState] & " " & [masterFIRMinfo]![FirmPOBoxZip]),
[Member File].MailCode, [Member File].Suspension, [Member File].NonMember,
IIf([Member File]![MailCode]="A" Or [masterFIRMinfo]![FirmPOBox] Is
Null,[masterFIRMinfo]![FirmZIP],[masterFIRMinfo]![FirmPOBoxZip]), [Member
File].Spouse_Last
HAVING ((([Member File].Suspension) Is Null) AND (([Member File].NonMember)
Is Null Or ([Member File].NonMember)="mail"));



Rebecca S.
Rebecca said:
Thank you, I believe that worked!
What does the boxes under Name AutoCorrect deal with? Will it effect
anything else in my database that I should be aware of?

Thanks,
Rebecca S.
Click to expand...
go
to instead
of save,
and ideas
what compacted
it
Click to expand...
Click to expand...
 
Hi Rebecca.

It sounds like there are still shreds of corruption from the Name
AutoCorrect problems, and simply renaming the query back to the name your
database *was* using for this query was enough to reactivate them.

Tina suggested performing a compact/repair after deleting this query and
before naming the copy back. That may help. If not:
1. Create a new (blank) database.
2. Turn off Name AutoCorrect.
3. Attach your tables: File | Get External | Link
4. Import all objects (queries, forms, reports, modules, and local tables):
File | Get External | Import.

The SQL statement does not contain the text "Query2.MailCode", so Access is
certainly confused about the names (unless "masterFIRMinfo" is another
query, and the parameter request is coming from the lower level.)

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

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

Rebecca said:
Geez.. Maybe not working.. pefectly.

The problem... When I click to run the query it pops up a box asking "Enter
Parameter Value" Query2.MailCode

When I made the new query and pasted the SQL from the original, I named it
Query2 and when it worked I renamed it to the current name MAILINGADDRESS
WIP. That is the only changes I made, renaming it.

Here is the SQL :
SELECT [Member File].MemberID, [Member File]![title] & " " & [Member
File]![FirstName] & " " & [Member file]![middle] & " " & [member
file]![lastname] AS Membername, [Member File].SurName, masterFIRMinfo.Firm,
IIf([Member File]![MailCode]="A" Or [Member File]![MailCode] Is
Null,IIf([masterFIRMinfo]![FirmPOBox] Is
Null,[masterFIRMinfo]![FirmAddress1] & " " &
[masterFIRMinfo]![FirmAddress2],[masterFIRMinfo]![FirmPOBox]),IIf([Member
File]![MailCode]="H",IIf([Member File]![Home_Address1] Is
Null,[masterFIRMinfo]![FirmAddress1] & " " &
[masterFIRMinfo]![FirmAddress2],[Member File]![Home_Address1]))) AS Address,
IIf([Member File]![MailCode]="A" Or [masterFIRMinfo]![FirmPOBox] Is
Null,[masterFIRMinfo]![FirmCity] & ", " & [masterFIRMinfo]![FirmState] & "
" & [masterFIRMinfo]![FirmZIP],[masterFIRMinfo]![FirmPOBoxCity] & ", " &
[masterFIRMinfo]![FirmPOBoxState] & " " & [masterFIRMinfo]![FirmPOBoxZip])
AS CityStateZip, [Member File].MailCode, [Member File].Suspension, [Member
File].NonMember, IIf([Member File]![MailCode]="A" Or
[masterFIRMinfo]![FirmPOBox] Is
Null,[masterFIRMinfo]![FirmZIP],[masterFIRMinfo]![FirmPOBoxZip]) AS Zip,
[Member File].Spouse_Last
FROM masterFIRMinfo INNER JOIN [Member File] ON masterFIRMinfo.FirmNo =
[Member File].FirmNo
GROUP BY [Member File].MemberID, [Member File]![title] & " " & [Member
File]![FirstName] & " " & [Member file]![middle] & " " & [member
file]![lastname], [Member File].SurName, masterFIRMinfo.Firm, IIf([Member
File]![MailCode]="A" Or [Member File]![MailCode] Is
Null,IIf([masterFIRMinfo]![FirmPOBox] Is
Null,[masterFIRMinfo]![FirmAddress1] & " " &
[masterFIRMinfo]![FirmAddress2],[masterFIRMinfo]![FirmPOBox]),IIf([Member
File]![MailCode]="H",IIf([Member File]![Home_Address1] Is
Null,[masterFIRMinfo]![FirmAddress1] & " " &
[masterFIRMinfo]![FirmAddress2],[Member File]![Home_Address1]))),
IIf([Member File]![MailCode]="A" Or [masterFIRMinfo]![FirmPOBox] Is
Null,[masterFIRMinfo]![FirmCity] & ", " & [masterFIRMinfo]![FirmState] & "
" & [masterFIRMinfo]![FirmZIP],[masterFIRMinfo]![FirmPOBoxCity] & ", " &
[masterFIRMinfo]![FirmPOBoxState] & " " & [masterFIRMinfo]![FirmPOBoxZip]),
[Member File].MailCode, [Member File].Suspension, [Member File].NonMember,
IIf([Member File]![MailCode]="A" Or [masterFIRMinfo]![FirmPOBox] Is
Null,[masterFIRMinfo]![FirmZIP],[masterFIRMinfo]![FirmPOBoxZip]), [Member
File].Spouse_Last
HAVING ((([Member File].Suspension) Is Null) AND (([Member File].NonMember)
Is Null Or ([Member File].NonMember)="mail"));



Rebecca S.
Rebecca said:
Thank you, I believe that worked!
What does the boxes under Name AutoCorrect deal with? Will it effect
anything else in my database that I should be aware of?

Thanks,
Rebecca S.
Click to expand...
go
to instead
of save,
and ideas
what compacted
it
Click to expand...
Click to expand...
 
Back
Top