MaxLocksPerFile

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
 
G

Gary Walter

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
 
G

Guest

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

Guest

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

Guest

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Case Select problem 3

Top