ADO Data Control?

Posted on 2005-05-06
Last Modified: 2013-12-25
My form includes: ADO Data Control:Adodc1
                          VsFlexgrid: FlexGrid
                   Command Button:cmdAdd,
                  Textbox: txtDay,txtHour(used to addnew and update)
My Database has:
                          One table: Table1
                          Two field:                   Day,Hour                    datatype:date/time
                          In field Day, the rows have same value is: 01/01/05
                  In field Hour, the rows have same value is: 22:00:00
Here is my code:

Private Sub cmdAdd_Click()
     Adodc1.Recordset.Fields("Day") = CDate(txtDay.Text)
     Adodc1.Recordset.Fields("Hour") = CDate(txtHour.Text)
End Sub

Private Sub cmdDel_Click()
End Sub

Private Sub cmdUpdate_Click()
     Adodc1.Recordset.Fields("Hour") = CDate(txtHour.Text)
End Sub

Private Sub Form_Load()
     Adodc1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\db1.mdb"
     Adodc1.RecordSource = "SELECT * FROM Table1"
     Adodc1.CursorType = adOpenDynamic
     Adodc1.LockType = adLockOptimistic
     Set FlexGrid.DataSource = Adodc1
End Sub

(Please note that field Day has the same value in every row.)
When I press cmdUpdate button, a error happens:

    Run-time error -2147467259 (8004005):
    Key column information is insufficient or incorrect. Too many rows were affected by update.

After I run my program again.
All of records in database have the same value
I only want to update one row that I chose. Anybody help me?

The same error happens when I press cmdDelete button
After I run my program again.
All of records in database have deleted
I only want to delete one row that I chose. Anybody help me?
Question by:DoDinhNguyen
    LVL 69

    Expert Comment

    by:Éric Moreau
    add a primary key to your table

    Author Comment

    I don't want add primary you have another solution?
    LVL 69

    Accepted Solution

    You need one! You could add a autonumber field with which you never interact.
    LVL 10

    Expert Comment

    Hi, DoDinhNguyen.

    emoreau is right--one of the primary constraints of modern database development is the requirement that every record/row in a table be unique (whether based on a single field in that record or a combination of fields).  Setting a primary key is the only way to interact with your database.
    LVL 27

    Expert Comment

    Hi DoDinhNguyen:
    > I don't want add primary key
    Why not?

    LVL 5

    Expert Comment

    You Have no other solution in this planet try use metaphysic solution maybe work
    there is no problem to add a primary key with autonumber to your DB else intend on your words .

    listen to professional kid and be better ;)
    LVL 29

    Expert Comment

    1. Get Primary key

    2. Get rid of ADODC

    Expert Comment


    When you click on a command button you know which record it is for example day= 11 and hour = 12:00pm.

    I suggest you change your Adodc1.RecordSource = "SELECT * FROM Table1" to
    Adodc1.RecordSource = "SELECT * FROM Table1 where day = 11 and hour  12:00pm" (on click)and you move to the last one, so it wil delete the last one if you have multiple records with the same data.
    If you don't have multiple records with the same data you don't have to go to the last record in the record set.

    kind regards


    PS: so you don't have to use a primary key.

    LVL 6

    Expert Comment

    Standard "It can not be done, sorry" = correct answer

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
    Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now