Database closes unexpectedly

[mlcampeau]

Does anyone know why my database sometimes closes on its own for no apparent reason? I don't think it has anything to do with my code because, as an example of when it closed on me, I was entering data on a form with a subform. I entered data for about 15 records and it worked absolutely fine, then I clicked next record as I did previously, and the entire database closed on me. I was having this problem before when I was trying to open the subform mentioned above, on its own, but figured out that it was due to an expression referring to the main form (which it couldn't find since the main form wasn't open at the time). Any ideas on this one? It happens so unexpectedly that it's really hard to say what's causing the problem, except that it is always related to the above mentioned forms.

[mlcampeau]

It just did it again. Again, I was entering data into the form, and as I tabbed from one field to the next, the status bar in Access said "Calculating..." and then the entire database closed.

As mentioned before I am using a main form (JobVacancy) and subform (JobVacancySubForm). The main form is informational only and displays the job code, job title, pay grade, and then there are three textboxes.
1. TotalPositions: Calculates the total number of positions available:

=DSum("[NumberofPositions]","[JobVacancy]","[code]=[JobVacancy]![JobCode]")

where NumberofPositions is a field in the JobVacancy table and Code is the job code on the main form (this field gets updated in the subform)

2. TotalOccupants: the total number of employees that have this particular job as their job title calculated by

=DCount("*","MY - JobVacancy2","[MY - JobVacancy2]![JobCode]= Forms![JobVacancyMain]![code]")

where MY - JobVacancy2 is a query and JobVacancyMain is the main form.

3. TotalVacancy: the total number of vacancies related to this position

=IIf((DSum("[NumberofPositions]","[JobVacancy]", "[code]=[JobVacancy]![JobCode]"))-(DCount("[MY - JobVacancy2]![ID]","MY - JobVacancy2","[MY - JobVacancy2]![JobCode]= Forms![JobVacancyMain]![code]"))<0,0, (DSum("[NumberofPositions]","[JobVacancy]", "[code]=[JobVacancy]![JobCode]"))-(DCount("[MY - JobVacancy2]![ID]","MY - JobVacancy2","[MY - JobVacancy2]![JobCode]= Forms![JobVacancyMain]![code]")))

I put this in the if statement because there are cases where there are more employees than positions (sometimes an employee is terminated but the db hasn't been updated yet, etc) so instead of displaying a negative number, I just want to know that there are 0 vacancies.

My subform, called JobVacancySubForm, is based on a query and has 2 combo boxes, one to display the department and one to display the division that the job is in. The user selects the department and division, and then enters the Number of Positions .

I then have 2 more textboxes with calculations:
1. CountofId: calculates how many employees have the particular job title displayed in the main form, and are located in the particular department and division in the subform (I should mention that the subform is a continuous form)

=DCount("*","MY - JobVacancy2","([MY - JobVacancy2]![JobCode]=Forms![JobVacancyMain]![code]) AND ([MY - JobVacancy2]![DivisionCode]= [JobVacancy]![DivisionCode])")

2. NumberofVacancies: Calculates the number of vacancies in this particular department and division

=IIf(([NumberofPositions]-(DCount("*","MY - JobVacancy2","([MY - JobVacancy2]![JobCode]=Forms![JobVacancyMain]![code]) AND ([MY - JobVacancy2]![DivisionCode]= [JobVacancy]![DivisionCode])")))<0,0,([NumberofPositions]-(DCount("*","MY - JobVacancy2","([MY - JobVacancy2]![JobCode]=Forms![JobVacancyMain]![code]) AND ([MY - JobVacancy2]![DivisionCode]= [JobVacancy]![DivisionCode])"))))

Again i do not want negative numbers to display.

I have a feeling that the system is just getting bogged down with these calculations, which is causing it to shut down unexpectedly, so if anyone has any suggestions, I'd really appreciate it!! Please keep in mind that I have never coded in Visual Basic before, so if that's where your solution lies, please give lots of details!

[nico5038]

Hmm, the calculations won't cause this in general, but it looks like your database has gone corrupt.
Try my MS Access database recovery steps:

1) Create a backup of the corrupt database. (Just in case of)

2) Create a new database and use File/Get external data/Import to get all objects of the damaged database.

3) Try these Microsoft solutions:
Repair A97/A2000:
http://support.microsoft.com/support.../Q109/9/53.asp
Jetcomp:
http://support.microsoft.com/default...;en-us;Q273956
and/or read the article:
ACC2000: How to Troubleshoot Corruption in a Microsoft Access Database
http://support.microsoft.com/default...b;en-us;306204

4) Bit "heavier":
Access decompile:
http://www.granite.ab.ca/access/decompile.htm

5) Try a recovery tool / Table rescue
Table datarecovery:
www.mvps.org/access/tables/tbl0018.htm
Access recovery:
http://www.officerecovery.com/access/index.htm

6) Ask a company (will cost $$'s ! )
http://www.pksolutions.com/services.htm

check also: http://www.granite.ab.ca/access/corruptmdbs.htm


Nic;o)

[mlcampeau]

Thanks for all the links! I've taken a look at a couple but will look at them all a bit more thoroughly in a bit. I have already tried compacting the database, but I still have the same problem. The articles have solutions for fixing corruption when you know which part of the database is corrupted. Is there a fairly simple way to figure that out? In my case it's a little bit difficult because I don't get any error messages - Access just freezes for a second while "Calculating" and then closes. And it's never consistent on when it does it. I'll keep looking through those articles to see if I can find anything!

[missinglinq]

I agree; sudden unexpeced closing of a db is almost assuredly a harbinger of corruption! And amazingly, Steps # 1 & 2, simple as they are, often work!

Linq ;0)>

Added note: You say you've tried compacting, but have you tried compiling your code? This will frequently identify problems.

[mlcampeau]

I've never had to import before (Steps 1 & 2). I'm trying it right now, but the problem I'm running into is that my corrupted database requires a password so when I try to import, it says that I do not have the permissions - how do I get around this?

[nico5038]

Hmm, what type of database do you have ?
Normally a password would be known when you use one. Haven't you developed the .mdb yourself or is it a .mde ?

Nic;o)

[mlcampeau]

Hmm, what type of database do you have ?
Normally a password would be known when you use one. Haven't you developed the .mdb yourself or is it a .mde ?

Nic;o)

The database was already made - it's one of those buy it and customize it to your needs type, so I have been doing all modifications while being signed in as the system administrator. When I say modifications, I basically mean I populated the database, and added a couple tables/forms to add some functionality. Other than that, I have not messed with the original set up. To be honest, I'm not 100% sure if this is a .mdb or .mde - I thought it was .mdb but now I can't actually find anywhere that it says that. It just says Microsoft Office Access Application - I'm using Access 2003 if that helps.

[nico5038]

Hmm, never used such a database, but for the .mdb or .mde you only have to lookup the name of the database in the folder it's situated.
When you open the database as an admin, is it asking for a password ?

Nic;o)

[mlcampeau]

It is a .mdb. And yes, as soon as I try to open the database, it asks for a username and password before it will open. I suspect I would need to disable that in order to import, but am not sure exactly where to do that (Tools --> Security --> ??) - I would need to turn the password back on once imported though because there is sensitive information in the database.

[nico5038]

Just use that userid / password when importing all database objects with the File/Getexternal data/import

Nic;o)

[mlcampeau]

The problem was that I wasn't being prompted for a password. What I had to do was open up the corrupted database and log in, then go to File/Close, then open a new database and import. Got it figured out! Thanks!

[nico5038]

Keep us posted about the result :-)

Nic;o)

[mlcampeau]

Okay, so I was able to import all objects into a new database, but the formatting was really strange on my forms (although looking at the properties, it was all the same), and some of my buttons were causing errors when I clicked on them.

What I tried doing to rectify the problem is, in the corrupt database, I recreated the form that I think started causing all the problems (as I said before, the database would only close down while this form was open, and my problems didn't start until after I created this form) and recreated all the queries that the form referred to, and then deleted all the old ones. I haven't run into a problem yet (doesn't mean I won't though!). I was just wondering though, since I've never had any experience with a corrupt database before, if the problem was caused by that particular form or queries, would recreating them and deleting the old ones actually fix my problem, or should I still be looking at getting my data into a new database?

[nico5038]

I know that deleting a form and creating a new one with the same name in the same Access session can cause trouble like this.
That's why I always do a Compact/Repair after deletion of a form.

Hope it keeps working and glad the diagnose of a corrupted DB was correct :-)

Nic;o)

[mlcampeau]

Hmm, I wonder if that's what caused my problem in the first place then. I had deleted and made a new form with the same name a couple times while trying to get it working. When I did it today though, I used different names so hopefully I won't run into any problems! *fingers crossed*