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.

-2147467259(80004005)
Key Column information is insufficient or incorrect. Too many rows were affected by update.

I have tried changing the cursor type, and still I get the same error. I don't know if there is a maximum amount of records I can update at one time, and I am doing too many or what. Anyone that knows how to fix this please help.

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.

[code]
Private Sub CmdRun_Click()
Open_2000PR
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"
DoEvents
If RecCountPR > 0 Then '---------- 1
PR2000.MoveFirst
Do While PR2000.EOF = False
PrgBar.Visible = True
CurRecord = CurRecord + 1 '<-- Counting Current Record
PrgBar.Value = CurRecord '<-- setting the Current Value to Progress Bar
If IsNull(PR2000.Fields("Codes").Value) Then
Else
PR2000.Fields("Codes").Value = " "
End If
PR2000.MoveNext
Loop
PR2000.UpdateBatch
End If '-------------------------- 1
End Sub
[/code]


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.


[code]
Function Open_PayrollDB()
'Checking for instance of open connection, if open it will close
If PayrollDB.State = adStateOpen Then
PayrollDB.Close
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
PR2000.Close
End If
'setting cursor and open information for recordset table
With PR2000
.ActiveConnection = PayrollDB
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockOptimistic
.Source = "select * from 2000PR"
.Open
End With
End Function
[/code]

Someone please help, thank you.

Comments

  • Hi there:

    So, you are testing this with 600 records? And it blows up when you've processed all 600?

    Are you, in fact, updating only 600 rows?

    Have you tried to run the program with less than 600 rows? At what "magic number" of rows will it fail?

    Try to do some more research, to narrow things down.

    FYI, I suspect that it's crashing because you're doing a batch update. You might consider doing the update differently.

    Don't even get me started on why you're using Jet...


    [purple]Melissa[/purple]

  • Another thought...

    Just for fun, move that UpdateBatch to inside the Loop.

    It's not the way you'll ultimately want to run it, but let's see if it changes anything...


    [purple]Melissa[/purple]

  • Thanks for the Responce Melissa, I found what the problem was. I did not have an Index Feild to control the record ID. I did not think this was going to be needed. It appears with my VB script, as its updated the Access DB, with the records I am editing. It must find a duplicated Record, and since it has no record ID or index to identify a difference, it shuts down and drops me that error. I added a Record ID and the routine works great. Thank you for your responce, but using Jet is one of the only ways I know how to set up DB editing with VB. As this program will be networked and managed through a server from many clients. I found manually coding every connection string and using jet to locate off the mapped drive the most compattible, and comfortable to use.

    The Darthmoob

    : Another thought...
    :
    : Just for fun, move that UpdateBatch to inside the Loop.
    :
    : It's not the way you'll ultimately want to run it, but let's see if it changes anything...
    :
    :
    : [purple]Melissa[/purple]
    :
    :

  • As this program will be networked and managed through a server from many clients...

    That's a scary proposition...

    There are much better ways to do this than Jet, which is not scalable or reliable beyond a certain number of users or records.

    Take a look at the SQL website on Microsoft for their free SQL Server software for developers. This is a much better way to do it.

    See my many rants on this subject elsewhere on this board...

    Regardless, glad you found the problem. Good luck with the project!


    [purple]Melissa[/purple]

  • free SQL Server software ?????????
    WHOW ? WHERE IS THAT ?

    --------------
    : As this program will be networked and managed through a server from many clients...
    :
    : That's a scary proposition...
    :
    : There are much better ways to do this than Jet, which is not scalable or reliable beyond a certain number of users or records.
    :
    : Take a look at the SQL website on Microsoft for their free SQL Server software for developers. This is a much better way to do it.
    :
    : See my many rants on this subject elsewhere on this board...
    :
    : Regardless, glad you found the problem. Good luck with the project!
    :
    :
    : [purple]Melissa[/purple]
    :
    :

    [red]Good luck![/red]
    [blue]Hackman[/blue]

  • : free SQL Server software ?????????
    : WHOW ? WHERE IS THAT ?
    :
    I'm glad you asked!

    There are actually two versions, both free. One, called MSDE 2000 and the other, SQL Server Express 2005.

    These are both meant to run on a workstation, so you don't actually have to have server software running.

    I use these all the time. Your SQL code is transparent, so you can write as if you're using the full-blown SQL Server. If things ever progress to where you need the full power of the server product, your software will need no changes whatsoever, except to point to the new server.

    With this power available for free, there's no reason to use Jet!

    Here are the links:

    MSDE 2000
    http://www.microsoft.com/sql/msde/default.asp

    SQL Server Excpress 2005:
    http://www.microsoft.com/sql/express/




    [purple]Melissa[/purple]

  • Thanks a lot ... I used MSDE because I believe it was on some sort of licence, I did buy Office 2000 Developer Tools, I believe it was like Microsoft Access Runtime ... you can deploy the Runtime only if you had the licence of Developer Tools.

    I was wrong... I will research. Thanks.

    ----
    : : free SQL Server software ?????????
    : : WHOW ? WHERE IS THAT ?
    : :
    : I'm glad you asked!
    :
    : There are actually two versions, both free. One, called MSDE 2000 and the other, SQL Server Express 2005.
    :
    : These are both meant to run on a workstation, so you don't actually have to have server software running.
    :
    : I use these all the time. Your SQL code is transparent, so you can write as if you're using the full-blown SQL Server. If things ever progress to where you need the full power of the server product, your software will need no changes whatsoever, except to point to the new server.
    :
    : With this power available for free, there's no reason to use Jet!
    :
    : Here are the links:
    :
    : MSDE 2000
    : http://www.microsoft.com/sql/msde/default.asp
    :
    : SQL Server Excpress 2005:
    : http://www.microsoft.com/sql/express/
    :
    :
    :
    :
    : [purple]Melissa[/purple]
    :
    :

    [red]Good luck![/red]
    [blue]Hackman[/blue]

  • Hey Wow, Thanks Melissa.

    I admit I didn't research too much into Jet, but what I did read, and I must say it must have been from some very Biased programming excerts, were some good things. I have had experience with working with Access already, as the company has previous programs already utilysing it, I just never thought of changing it or as they say rocking the boat.

    After installing this, how would I point, hardcoding that is, my program to the database created with MSDE2000? I know with jet I actually type the destination or file address. Will it be similiar to pointing to the database with say, PHP or a web connection. Typing database name, user name and or host? Would I need IIS to be enabled in order to use MSDE2000 SQL features? I hope these are not silly questions that are too basic to answer. I would really first like to better understand the options before rewriting the code. Hehehehe.

    The Darthmoob

    : Thanks a lot ... I used MSDE because I believe it was on some sort of licence, I did buy Office 2000 Developer Tools, I believe it was like Microsoft Access Runtime ... you can deploy the Runtime only if you had the licence of Developer Tools.
    :
    : I was wrong... I will research. Thanks.
    :
    : ----
    : : : free SQL Server software ?????????
    : : : WHOW ? WHERE IS THAT ?
    : : :
    : : I'm glad you asked!
    : :
    : : There are actually two versions, both free. One, called MSDE 2000 and the other, SQL Server Express 2005.
    : :
    : : These are both meant to run on a workstation, so you don't actually have to have server software running.
    : :
    : : I use these all the time. Your SQL code is transparent, so you can write as if you're using the full-blown SQL Server. If things ever progress to where you need the full power of the server product, your software will need no changes whatsoever, except to point to the new server.
    : :
    : : With this power available for free, there's no reason to use Jet!
    : :
    : : Here are the links:
    : :
    : : MSDE 2000
    : : http://www.microsoft.com/sql/msde/default.asp
    : :
    : : SQL Server Excpress 2005:
    : : http://www.microsoft.com/sql/express/
    : :
    : :
    : :
    : :
    : : [purple]Melissa[/purple]
    : :
    : :
    :
    : [red]Good luck![/red]
    : [blue]Hackman[/blue]
    :
    :

  • There are no silly questions. Only silly answers :)

    Here's the connection string you'll need to build:

    cnDNC.ConnectionString = "Provider=SQLOLEDB;Persist Security Info=False;" & _
    "User ID=" & strUserID & ";" & _
    "Password=" & strUserPassword & ";" & _
    "Initial Catalog=DATABASENAME;Data Source=SERVERNAME"

    Of course, you'll substitute your actual values for "DATABASENAME" and "SERVERNAME". The "SERVERNAME" is actually the name of the workstation that you install SQL on. The "DATABASENAME" is the name given to the database when you create it.

    I don't know if they give you any database management tools with MSDE or SQL Express 2005. They didn't used to.

    If they still don't, you'll need to use a utility to build your database, or do it all in VB. Depending on the version of VB you have, there may be some SQL utilities available to you.

    All of the stuff you need to build your own SQL tools is included in the SQL DMO that gets installed when SQL is installed. If you're interested in that aspect of it, you can do a search to find more info.




    [purple]Melissa[/purple]

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!

Categories