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
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