Unanswered: Is there someone can help? (Problem with updating DB2 records with VBA)
Is there someone can help?
I have Db2 v3r2 on AS4000 here. The AS400 system is named Zeus. I tried to update a db2 record with VBA.
When I use ODBC and DAO, I can retrieve the record and obtain the field value for Name field
but I cannot update the value. When .Update line is excuted, program returns an error msg
I have DONOID as primary key. I tried both Numberic (0 decimal) and Alphabetic Type for DONOID.
I cannot make it work.
Here is the code I used to open the recordset:
Set ODBCWorkspace = CreateWorkspace("ODBCDirect", "A", "", dbUseODBC)
Set MyConnection = ODBCWorkspace.OpenConnection("Zeus DSN", dbDriverNoPrompt, False)
sqlstring = "Select DONO2.* FROM QS36F.DONO2"
Set DonoTable = MyConnection.OpenRecordset(sqlstring, dbOpenDynaset, 0, dbOptimistic)
I tried to use a pass query to update the table. Query such as
"INSERT INTO QS36F.DONO2(DONOID, DONOTYPE, NAME) VALUES('4', '2', 'Name FOUR')"
"DELETE * FROM QS36F.DONO2 WHERE (DONO2.DONOID='1')"
succeeded. However queries such as
"Update QS36F.DONO Set DONO.Name = 'New Name' WHERE (DONO.DONOID = '1')"
failed. I wonder what is special for an update query for DB2 on AS400.