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

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),
);