VBA error 3035 (1 Viewer)

Using Win 7, Access 2010

I have a relatively straightfwd vba code that steps through a table and updates a field by placing a string that represents an operation number. The base table has 56K records and I am stepping through each record and based on certain criteria updating the value, record by record. My issue is that this code generates a vba error 3035. I have researched and tried various "fixes" but have not found one that solves the problem. Have changed to an update function (no help). Have increased virtual memory (no help) Have introduced a DAO.DBEngine.SetOption dbMaxLocksPerFIle instruction to increase locks to 1M (no help). Hoping you can look at the code and help me determine the cause of this error:

Private Sub Create_OpNo_s_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sBOM, sITEM As String
Dim sfilt As String
Dim sPosit As Integer

DAO.DBEngine.SetOption dbMaxLocksPerFile, 1000000
'setup BOM file as rs
Set db = CurrentDb
sfilt = "Select BOM.BOMID, BOM.ItemID, BOM.Position from BOM ORDER BY BOM.BomID, BOM.ItemID"
Set rs = db.OpenRecordset(sfilt, dbOpenDynaset)
rs.MoveLast
rs.MoveFirst
With rs
'Initialize BOM Parent String to first rs value and Position String to 10
sBOM = !BOMID
sPosit = 0

'Do while not eof rs
Do While Not rs.EOF
If sBOM = !BOMID Then 'increment Position
sPosit = sPosit + 10
Else 'reset position to 10
sPosit = 10
sBOM = !BOMID
End If

sITEM = !ItemID
'MsgBox sBOM & ", " & !BOMID & ", " & sPosit, vbOKOnly

'Update rs Position
.Edit
!Position = Str(sPosit)
.Update

' dbs.Execute "Update BOM set Position = Str(sPosit)where BOMID = '" & sBOM & "' and ItemID = '" & sITEM & "'"

.MoveNext
Loop
End With
MsgBox "BOM Position update complete!", vbOKOnly
'Cleanup
rs.Close
Set db = Nothing
Set rs = Nothing
End Sub

Hi and welcome to the forum

For best results, if you post code that raises an error, indicate at which line the error occurs. Also, the description of the error is commonly more useful than the number. I can raise error 3035 here using...
...but on my system the Err.Description is...

"Application defined or object defined error"

Click to expand...

Do you receive a more useful error message than that?
Cheers,

Code causing the error is: Set rs = db.OpenRecordset(sfilt, dbOpenDynaset)

Actual error is System Resources Exceeded.

Is BOM a table or query?

BOM is a table

Thanks! Does it have any Lookup Fields in it?

Also, is the problem intermittent, or does it always fail the same way at the same line despite everything you've tried?

Hi thanks for quick reply...

No lookup fields. Query appears to run and then bombs out with error on openrecordset command

If you paste this text...
...into the SQL view of a new query, and run that query, what happens?

Strange. Replaced string var with SQL statement in quotes, not getting VB error: Run-time error '3734: The database has been placed in a state by user 'Admin' on machine 'my machine' that prevents it from being opened or locked.

Error occurs on db = CurrentDb

Closed db and reopened. Query ran. no error. Why do you suppose that is?

Thank you's to MarkK and RuralGuy!!!! Especially for quick response. Appreciate the help. Anxious to hear why replacing string var in OpenRecordset made a diff.

Gretzman said:

Anxious to hear why replacing string var in OpenRecordset made a diff.

Click to expand...

I doubt it's that. More likely closing the database and reopening it freed up resources. Change it back and open the database fresh, and see if it fails again. It shouldn't fail, and the variable or literal shouldn't make a difference.