Access 2002 unexpectedly quits and Catastrophic Failure messages

[Frav]

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 200 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, y 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
-Micrfosoft 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., etc,etc.

Sometimes I have the opportunity to debug and the error usually comes in
this part.

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


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!

 

[Allen Browne]

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