Access 2002 Keeps Crashing 2

[ActMod]

The following macro opens the table "tblClaimsShort", which has about 170,000 records. Access crashes (my system freezes and Access shuts down) everytime I run the macro - but only after every 10,000 records or so that are properly updated. Can anyone see what may be wrong with my code - or, more importantly, how I might be able to pre-empt Access crashes in general. I am new to Access, but I manage to crash Access with incredible frequency (about 15 to 20 times a day!!) during almost all my attempts at running macros.

Thank you for any help.

Jim

Sub Truncate_SS()
'This Macro truncates the last two numbers appended to _
the Social Security Number

Dim rs As ADODB.Recordset
Dim iCount As Long

On Error GoTo ShowError_Err

Set rs = New ADODB.Recordset
With rs
.ActiveConnection = CurrentProject.Connection
.CursorType = adOpenKeyset 'Permits changing of source records
.LockType = adLockOptimistic
'.CursorLocation = adUseClient
'.Open "Select EeSS from tblClaimsShort ", Options:=adCmdText
.Open "Select EeSS from tblClaimsShort"
End With

rs.MoveFirst
rs.Move (145000)

Do Until rs.EOF
iCount = rs.AbsolutePosition

rs("EeSS&quot = Left(rs("EeSS&quot, 9)
rs.MoveNext

Loop

GoTo Line99

ShowError_Err:
MsgBox "Error Detected with rs = " & iCount, , "Error Detected"

rs.Close
Set rs = Nothing

Line99:

End Sub

 

[SBendBuckeye]

With that many records it may be timing out. Try breaking things down into smaller chunks and see if that helps solve your problem.

Good Luck!

Have a great day!

j2consulting@yahoo.com

 

[VBslammer]

I would try to do a batch update.

I see alot of people fiddling with the CursorType when they encounter recordset problems, but the documentation states that when you set the CursorLocation to adUseClient, you always get a static cursor.

I had a network problem on one system where the front end was in Houston and the data files were in Fort Worth, and the ISDN connection wasn't fast enough to keep Access from 'freezing' during lengthy operations. I didn't encounter any 'shutdowns' though, just users who did the 3-finger salute (CTRL+ALT+DEL)...

I also recall one problem on a Novell network where the 'max record locks' was set too low and affected large transactions (more freezing).

I seem to be thinking out loud here...hmm

VBSlammer

Unemployed in Houston, Texas

 

[VBslammer]

You know, an update query would work much faster

VBSlammer

Unemployed in Houston, Texas