MaxLocksPerFile

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Does anyone have any idea why this code only processes 95,039 of my 99,790
records?
Public Sub AssignOwnerDF()
Dim Db As DAO.Database
Dim rs As DAO.Recordset

DAO.DBEngine.SetOption dbMaxLocksPerFile, 150000
Set Db = CurrentDb
RecordCount = DCount("*", "tblWholesaler")
Set rs = Db.OpenRecordset("tblWholesaler") '

Mgr = 0
For RecCount = 1 To RecordCount 'Traverse table
If Mgr = 20 Then
Mgr = 0
Else
Mgr = Mgr + 1
rs.Edit
Select Case rs![Zip]
Case "60505" Or "60564"
rs![Owner] = 21
Case "60134" Or "60151" Or "60174" Or "60510" Or "60542" Or
"60554"
rs![Owner] = 22
Case Else
rs![Owner] = Mgr
End Select
rs.Update
rs.MoveNext
End If
Next RecCount

End Sub

I used research from http://support.microsoft.com/kb/815281

Thanks
 
on cursory review, every time Mgr=20,
that record will not be processed.

99,790/20 = 4989.5 --> approx records not processed!

is this what you wanted?

Public Sub AssignOwnerDF()
Dim Db As DAO.Database
Dim rs As DAO.Recordset
Dim varResponse As Variant
Dim Mgr As Long
Dim RecordCount As Long
Dim RecCount As Long

DAO.DBEngine.SetOption dbMaxLocksPerFile, 150000
Set Db = CurrentDb
RecordCount = DCount("*", "tblWholesaler")
Set rs = Db.OpenRecordset("tblWholesaler") '

Mgr = 0
For RecCount = 1 To RecordCount 'Traverse table
'show which record in status bar
varReturn = SysCmd(acSysCmdSetStatus, "Processing Rec # " & RecCount)

If Mgr = 20 Then
Mgr = 0
Else
Mgr = Mgr + 1
End If

rs.Edit

Select Case rs![Zip]
Case "60505" Or "60564"
rs![Owner] = 21
Case "60134" Or "60151" Or "60174" Or "60510" Or "60542" Or
"60554"
rs![Owner] = 22
Case Else
rs![Owner] = Mgr
End Select

rs.Update

rs.MoveNext
Next RecCount

'clear display in status bar
varReturn = SysCmd(acSysCmdClearStatus)

End Sub
 
Bingo!

Gary Walter said:
on cursory review, every time Mgr=20,
that record will not be processed.

99,790/20 = 4989.5 --> approx records not processed!

is this what you wanted?

Public Sub AssignOwnerDF()
Dim Db As DAO.Database
Dim rs As DAO.Recordset
Dim varResponse As Variant
Dim Mgr As Long
Dim RecordCount As Long
Dim RecCount As Long

DAO.DBEngine.SetOption dbMaxLocksPerFile, 150000
Set Db = CurrentDb
RecordCount = DCount("*", "tblWholesaler")
Set rs = Db.OpenRecordset("tblWholesaler") '

Mgr = 0
For RecCount = 1 To RecordCount 'Traverse table
'show which record in status bar
varReturn = SysCmd(acSysCmdSetStatus, "Processing Rec # " & RecCount)

If Mgr = 20 Then
Mgr = 0
Else
Mgr = Mgr + 1
End If

rs.Edit

Select Case rs![Zip]
Case "60505" Or "60564"
rs![Owner] = 21
Case "60134" Or "60151" Or "60174" Or "60510" Or "60542" Or
"60554"
rs![Owner] = 22
Case Else
rs![Owner] = Mgr
End Select

rs.Update

rs.MoveNext
Next RecCount

'clear display in status bar
varReturn = SysCmd(acSysCmdClearStatus)

End Sub

Rod said:
Does anyone have any idea why this code only processes 95,039 of my 99,790
records?
Public Sub AssignOwnerDF()
Dim Db As DAO.Database
Dim rs As DAO.Recordset

DAO.DBEngine.SetOption dbMaxLocksPerFile, 150000
Set Db = CurrentDb
RecordCount = DCount("*", "tblWholesaler")
Set rs = Db.OpenRecordset("tblWholesaler") '

Mgr = 0
For RecCount = 1 To RecordCount 'Traverse table
If Mgr = 20 Then
Mgr = 0
Else
Mgr = Mgr + 1
rs.Edit
Select Case rs![Zip]
Case "60505" Or "60564"
rs![Owner] = 21
Case "60134" Or "60151" Or "60174" Or "60510" Or "60542" Or
"60554"
rs![Owner] = 22
Case Else
rs![Owner] = Mgr
End Select
rs.Update
rs.MoveNext
End If
Next RecCount

End Sub

I used research from http://support.microsoft.com/kb/815281

Thanks
Click to expand...
Click to expand...
 
I just noticed i still have 4526 unassigned records. Any idea why the
process just stops?

Gary Walter said:
on cursory review, every time Mgr=20,
that record will not be processed.

99,790/20 = 4989.5 --> approx records not processed!

is this what you wanted?

Public Sub AssignOwnerDF()
Dim Db As DAO.Database
Dim rs As DAO.Recordset
Dim varResponse As Variant
Dim Mgr As Long
Dim RecordCount As Long
Dim RecCount As Long

DAO.DBEngine.SetOption dbMaxLocksPerFile, 150000
Set Db = CurrentDb
RecordCount = DCount("*", "tblWholesaler")
Set rs = Db.OpenRecordset("tblWholesaler") '

Mgr = 0
For RecCount = 1 To RecordCount 'Traverse table
'show which record in status bar
varReturn = SysCmd(acSysCmdSetStatus, "Processing Rec # " & RecCount)

If Mgr = 20 Then
Mgr = 0
Else
Mgr = Mgr + 1
End If

rs.Edit

Select Case rs![Zip]
Case "60505" Or "60564"
rs![Owner] = 21
Case "60134" Or "60151" Or "60174" Or "60510" Or "60542" Or
"60554"
rs![Owner] = 22
Case Else
rs![Owner] = Mgr
End Select

rs.Update

rs.MoveNext
Next RecCount

'clear display in status bar
varReturn = SysCmd(acSysCmdClearStatus)

End Sub

Rod said:
Does anyone have any idea why this code only processes 95,039 of my 99,790
records?
Public Sub AssignOwnerDF()
Dim Db As DAO.Database
Dim rs As DAO.Recordset

DAO.DBEngine.SetOption dbMaxLocksPerFile, 150000
Set Db = CurrentDb
RecordCount = DCount("*", "tblWholesaler")
Set rs = Db.OpenRecordset("tblWholesaler") '

Mgr = 0
For RecCount = 1 To RecordCount 'Traverse table
If Mgr = 20 Then
Mgr = 0
Else
Mgr = Mgr + 1
rs.Edit
Select Case rs![Zip]
Case "60505" Or "60564"
rs![Owner] = 21
Case "60134" Or "60151" Or "60174" Or "60510" Or "60542" Or
"60554"
rs![Owner] = 22
Case Else
rs![Owner] = Mgr
End Select
rs.Update
rs.MoveNext
End If
Next RecCount

End Sub

I used research from http://support.microsoft.com/kb/815281

Thanks
Click to expand...
Click to expand...
 
Found the problem. It was processing the records but the loop was setting
the value to 0:
If Mgr = 20 Then
Mgr = 0
It should have been setting it to 1:
If Mgr = 20 Then
Mgr = 1

Done deal!
Thanks MUCH!

Rod said:
I just noticed i still have 4526 unassigned records. Any idea why the
process just stops?

Gary Walter said:
on cursory review, every time Mgr=20,
that record will not be processed.

99,790/20 = 4989.5 --> approx records not processed!

is this what you wanted?

Public Sub AssignOwnerDF()
Dim Db As DAO.Database
Dim rs As DAO.Recordset
Dim varResponse As Variant
Dim Mgr As Long
Dim RecordCount As Long
Dim RecCount As Long

DAO.DBEngine.SetOption dbMaxLocksPerFile, 150000
Set Db = CurrentDb
RecordCount = DCount("*", "tblWholesaler")
Set rs = Db.OpenRecordset("tblWholesaler") '

Mgr = 0
For RecCount = 1 To RecordCount 'Traverse table
'show which record in status bar
varReturn = SysCmd(acSysCmdSetStatus, "Processing Rec # " & RecCount)

If Mgr = 20 Then
Mgr = 0
Else
Mgr = Mgr + 1
End If

rs.Edit

Select Case rs![Zip]
Case "60505" Or "60564"
rs![Owner] = 21
Case "60134" Or "60151" Or "60174" Or "60510" Or "60542" Or
"60554"
rs![Owner] = 22
Case Else
rs![Owner] = Mgr
End Select

rs.Update

rs.MoveNext
Next RecCount

'clear display in status bar
varReturn = SysCmd(acSysCmdClearStatus)

End Sub

Rod said:
Does anyone have any idea why this code only processes 95,039 of my 99,790
records?
Public Sub AssignOwnerDF()
Dim Db As DAO.Database
Dim rs As DAO.Recordset

DAO.DBEngine.SetOption dbMaxLocksPerFile, 150000
Set Db = CurrentDb
RecordCount = DCount("*", "tblWholesaler")
Set rs = Db.OpenRecordset("tblWholesaler") '

Mgr = 0
For RecCount = 1 To RecordCount 'Traverse table
If Mgr = 20 Then
Mgr = 0
Else
Mgr = Mgr + 1
rs.Edit
Select Case rs![Zip]
Case "60505" Or "60564"
rs![Owner] = 21
Case "60134" Or "60151" Or "60174" Or "60510" Or "60542" Or
"60554"
rs![Owner] = 22
Case Else
rs![Owner] = Mgr
End Select
rs.Update
rs.MoveNext
End If
Next RecCount

End Sub

I used research from http://support.microsoft.com/kb/815281

Thanks
Click to expand...
Click to expand...
Click to expand...
 
Back
Top