Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2004
    Posts
    2

    Unanswered: Cant do simple update to ODBC mysql table in VBA

    Hi, all:
    I'm trying to update a column in a mysql table using myODBC and VBA and I'm getting the following error when I hit the 'update' statement: "Query-based update failed because the row to update could not be found."

    Here's my code:
    Dim Conn As ADODB.Connection
    Dim rsPoles As ADODB.Recordset

    Set Conn = CreateObject("ADODB.Connection")
    Conn.Open "FILE NAME=C:\nelson\photos-mysql.udl", "<username>", "<password>"

    Set rsPoles = CreateObject("ADODB.Recordset")

    rsPoles.Open "SELECT * FROM photos WHERE id=4201", Conn, adOpenStatic, adLockOptimistic

    rsPoles.MoveFirst
    Dim sTemp, x As Variant
    sTemp = rsPoles.Fields("photocaption").Value
    x = rsPoles.Fields("id").Value
    rsPoles.Close
    rsPoles.Open "SELECT * FROM photos WHERE id=x", Conn, adOpenKeyset, adLockOptimistic

    MsgBox sTemp

    rsPoles!PhotoCaption = "Gollee Bob Howdy!"

    'Update the recordset -- here's where it bombs!!!!
    rsPoles.Update
    rsPoles.Close


    I've tried using all the adLock values and the adCursorTypes in the Open, but nothing seems to work.
    Could someone help me with this?

    Thanks!

    Paul

  2. #2
    Join Date
    Aug 2004
    Posts
    2

    Solved!

    Well, there's been absolutely no response from the "experts" here.

    But I found out the problem! You MUST have a primary key defined in the mysql table before the above code will work. Once an index key is defined, everything else works great.

    Hope this helps someone.

    Just FYI, here's the statement to add a primary key to a MySQL table:

    ALTER TABLE <table-name> ADD PRIMARY KEY <keyname> (<key-field>)

    and here's the DDL for the table once a key has been added:

    CREATE TABLE photos (
    photocaption text,
    photoid int(11) NOT NULL AUTO_INCREMENT,

    PRIMARY KEY (photoid),
    );

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •