Strange ADODB Error Too Many Rows Affected

Hello everyone, I am writing a payroll program that will check between billing codes and payroll codes. I am running an edit, which is a process that compares the billing data and payroll data, and prints out a report of what errors it has found. The program checks between a table, that has approximately 500 - 600 records, and will open it using an ADODB recordset, and make changes. The program is working fine, but towards the end I get runtime error.

Key Column information is insufficient or incorrect. Too many rows were affected by update.

Here is some of the sample code I have wehre the process is taking place. I researched this error, and apparently it is mainly if I have too many recordsets open at once, but I have only one recordset open while this commmand is in effect.

Private Sub CmdRun_Click()
CmdRun.Visible = False

Dim check, RecCountPR, RecCountEM As Double
Dim ProvNum, clinum, Rhrs, Phrs, ecode, BillingCode As String
Dim DateDay As String
Dim DTday As String
Dim DTmon As String
Dim BillingCodeCheck As Boolean
Dim CurRecord As Double
Dim CheckDate As Double
Dim ResetClientHours As Double
Dim ResetProvHours As Double

RecCountPR = PR2000.RecordCount
PrgBar.Max = PR2000.RecordCount
BillingCodeCheck = False
CurRecord = 0

Label2.Caption = "Reseting Payroll Error Codes"
If RecCountPR > 0 Then '---------- 1
Do While PR2000.EOF = False
PrgBar.Visible = True
CurRecord = CurRecord + 1
PrgBar.Value = CurRecord
If IsNull(PR2000.Fields("Codes").Value) Then
PR2000.Fields("Codes").Value = " "
End If
End If '-------------------------- 1
End Sub

I am also including my ADODB connection string that I have placed as a global function in a module, so that I will not need to type out the entire connection string each time I need to open it.


Function Open_PayrollDB()
'Checking for instance of open connection, if open it will close
If PayrollDB.State = adStateOpen Then
End If

PayrollDB.Open "Provider=Microsoft.Jet.OLEDB.4.0;persist security info =false;data source=f:databasesPayroll.mdb"

End Function

Function Open_2000PR()
' Temporary Payroll Table for Formatting
If PR2000.State = adStateOpen Then
End If
'setting cursor and open information for recordset table
With PR2000
.ActiveConnection = PayrollDB
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockOptimistic
.Source = "select * from 2000PR"
End With
End Function

Someone please help, thank you.
Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!