Catastrophic failure errors, mystery solved

[easycode]

Please read the entire text!

This is a question that i posted over the internet, i spent long days (2 weeks) trying to solve it, with no luck and then an MVP person reply with a great tips that worked. Here it is!

Access 2002 unexpectedly quits and Catastrophic Failure messages (question)

From: Frav - view profile
Date: Fri, Jul 28 2006 11:22 am

The Reps team have been experiencing that Access 2002 unexpectedly quits while working and also lots of Corruption Failures and "Record lock can not update" messages since the upgrade from Windows 2000 and Access 2000 to Windows XP and Access 2002 (XP) was done.
Now, they are working and at anytime in anywhere of the application access unexpectedly quits with no error messages and the info that was being entered is lost.

all computers have a standard configuration with the latest services packs, Microsoft Windows XP SP2 and Microsoft Office 2002 (XP) SP3, Microsft Jet 4.0.8618.0, and have also check all the references and they are good.

Here are the references:
-Visual Basic for applications
-Microsoft Access 10.0 Object Library
-Microsoft Office 10.0 Object Library
-Microsoft Office XP Web Components
-Microfosoft ActiveX Data Objects 2.8 Library
-OLE Automation


I heard something about Opportunistic locking if you disable it (Oplocks) on the server may reduce the risk of data corruption.

This problems occurred in an .mde file, but i also have experienced the same problem and i am working with the .mdb file

I've been dealing with this problem since 7/12/06 (more than 2 weeks) doing lots of different things like changing references, disabling and enabling the "open databases using record locking", compact and repair, migrate database to access 2002, contacting Microsoft co (The suggestions they gave did not work)., etc.

Sometimes I have the opportunity to debug and the error usually comes in the next part (testing with a total of 3 users saving a new contact record each user and saving all simultaneously at once), after the other users are done saving when i got the error message then click on
continue process and it saves with no problem.

Code:

Sub WriteAuditlog(txtOperation, txtTableName, txtFieldName, frefnum, OrgValue, CurValue)
Dim rstAudit As ADODB.Recordset
Dim fsql As String
On Error GoTo Err_WriteAuditlog

Set rstAudit = New ADODB.Recordset
Set rstAudit.ActiveConnection=CurrentProject.Connection
rstAudit.CursorType = adOpenKeyset
rstAudit.LockType = adLockPessimistic
fsql = "Select * from AuditTable"


rstAudit.Open fsql ==============> IT WILL BREAK HERE

rstAudit.AddNew


When i continue the execution after the message catastrophic failure it saves the contact record with no problem.

Does anybody know what could be happening? if you need any more information please let me know. Thanks for your replies, any help is appreciated!


-------------------------------------------------


Reply>>


From: Allen Browne - view profile
Date: Sat, Jul 29 2006 4:24 am

Not sure exactly what's happening, but some generic suggestions:

1. Uncheck the boxes under:
Tools | Options | General | Name AutoCorrect
Explanation of why:

http://allenbrowne.com/bug-03.html

2. Compact the database to get rid of this junk:
Tools | Database Utilities | Compact/Repair


3. Close Access. Make a backup copy of the file. Decompile the database by entering something like this at the command prompt while Access is not running. It is all one line, and include the quotes:

"c:\Program Files\Microsoft office\office\msaccess.exe" /decompile
"c:\MyPath\MyDatabase.mdb"

4. Open Access (holding down the Shift key if you have any startup code),
and compact again.

5. Open a code window.


Now, some more specific ones.

If the form that triggers the crash has a subform that has a multi-table
query as its RecordSource, this could be an Access bug:
1. Open the main form in design view.
2. Clear the Link Master Fields and Link Child Fields properties.
3. Save. Close.
4. Open the subform in design view, and add a text box for the foreign key
fields (the ones previously named in Link Child Fields.)
5. Save. Close.
6. Open the main form, again, and restore the Link Master Fields and Link Child Fields properties.

Typically the foreign key fields are not shown on the form, so their data type is AccessField, and the bug seems to be with this type. By adding a
text box, you avoid the bug.)


Other alternatives would be to use an append-only recordset for the target, or a DAO recordset, or execute an Append query instead of opening the recordset.


You may be able to remove the OLE and Web Components libraries. Access gives
you those by default; remove them if you are not using them.

HTH


--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users -

http://allenbrowne.com/tips.html

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

 

[JoeAtWork]

The following code struck me as very inefficient:

rstAudit.LockType = adLockPessimistic 
fsql = "Select * from AuditTable" 


rstAudit.Open fsql ==============> IT WILL BREAK HERE 

rstAudit.AddNew

Judging by the name of the procedure, WriteAuditLog, all you want to do is add a record.
So why pessimistic locking?
And more importantly, why retrieve every record from the AuditTable?
If you have to use a recordset for adding the record, you should open one that retrieves no record, something like:
"SELECT * FROM AuditLog WHERE ID = 0"

 

[JerryKlmns]

And not to mention that this SQL statement

strSQL = "INSERT INTO AuditTable(Operation, TableName, FieldName, frefnum, OrgValue, CurValue)" & _
         "VALUES '" & txtOperation & "', '" & txtTableName & "', '" & _
                      txtFieldName & "', '" &  frefnum & "', '" & _
                      OrgValue & "', '" &  CurValue & "'"

shall run ultra fast using the

CurrentProject.Connection.Execute strSQL, , 129

giving the same result to your code