Unanswered: Random database crash - no error message
Does anyone have any suggestions on how to fix or at least further investigate the following problem?
I support a complex Access database (front end) with an Oracle backend and a user base of 4 - 6 people. The front end (mde) runs on the users PC.
Since converting to Windows2000 and Access2002 (from Windows95 and Access97) the front end has developed a random but frequent crashing problem which doesn't produce an error message.
Unfortunately I am not able to replicate the problem myself and as the database and users are 500 miles away, monitoring the situation is not easy.
The crashing seems to occur when the app is idle. For example the user has minimised the app to work on Excel or to go to lunch. It also doesn't seem to matter what screen they are in when it is minimised.
The system has a form that is kept open for the duration of use. This has allowed me to put some monitoring in to see when a crash happens for each user. The results are random but as users seem to crash independantly of each other I believe the problem is in the front end (possibly even PC based) rather than with the Oracle database.
I have reviewed some of the code and whoever built the database has opted to make forms not visible rather than closing them. So at any time there can be four or more forms open. In some circumstances these are data update forms. I don't know if this is an issue.
The database, built some time ago, was originally written in Access 2, got converted to Access97 and had the back end upgraded to Oracle, and has now been converted to Access2002.
if you use odbc, its probably the problem. ms access odbc seems to have problem re-connecting to data source after a period of time. i had the same problem (mde file connecting to mysql via odbc) until i set the odbc connection timeout to zero (means dont disconnect from the data source after any period of time)
i am told this setting is not recommended for some reasons but my project has been running very WELL till now since the new setting.
you can try to set odbc timeout to zero if you want
Thank you for your response qha_vn. I forgot to include in my environment list that we use the Oracle ODBC driver (version 8.00.05.00) rather than the Microsoft one, and it doesn't seem to have the same control options. Yesterday, someone suggested that a later version of the driver might be more compatable with Win2k so I'll investigate that. Any other thoughts will be gladly received though.
possible oracle timeout error from the oracle side - I assume this does not happen if the form is kept active or other users do not have the problem if they are active and the other user has been idle?
Hi axsprog. Yes, it's completely random and I can't see any instances of users crashing at the same time. A user has just advised me that the system locks up while they are using it and they do alt+ctl+del and end task. Interestingly enough this is not showing up as a crash in my monitoring so I need to find out how the end task process works to see if it tries to close the app cleanly if it is "alive and well".
to me, either oracle or microsoft odbc, its still on windows platform and may be affected. can you do a test like this: ask somebody leave the app untouched WITHOUT ANY form open (odbc wont try to disconnect and reconnect after an idle period) if problem still happens, then we can say NO to odbc drivers
i'd like to emphasize that we did have the same problem before, and spent months before come up with the solution.
Hello again qha_vn
I took your advice but did a test in reverse. I added code to the permanently open form to read a record from the user table every 60 seconds (to force the connection to remain alive). I rolled this out a day and a half ago and haven't had a crash since. So that proves that it's the connection.
Next problem is to work out what I need to change to fix it. The Oracle client end driver and System DSN don't have any settings that relate to timeouts or disconnection. I've asked our database team to check for settings at the server end and I'm also looking at going to a later version of the client driver.
Thanks for your replies. It got me thinking straight.
so it was an oracle timeout error - When we had this problem it was coming from connecting via access and streaming files into a table. All text fields, when oracle would time out , it would actually send the error as well and write that error into the text field. Weird but true.
You can set your ODBC refresh intervals in tools/options/advance
But this may not resolve the issue. We had to contact the Oracle DBA's at the site where this occurred and they adjusted the timeout length. I am not qualified in Oracle to tell you how they did this - but whatever they did - it fixed the problem.
We were only able to download about 10k records unti they corrected the timeout issue - which then enable us to download 40k records + at a time. same principle though.