Heya.

My issue is yet another one of those issues that causes the error:

"Insufficient Key Column Information to Update or Refresh"

Fortunately, I have been able to reproduce this issue on a very basic level. It may just be happening on my machine, or it could be a problem that is more prevalent. It is an issue only related to the Data Environment useage. If I attempt the same things in pure code I have no issues.

I have a Linux machine running MySQL. I am connecting to this via MyODBC.

My tables/views are defined in MySQL as
Code:
test1 (table)
-------
id (integer/autoincrement)
name1 (varchar(45))
test2(integer/Foreign Key to test2 table)

test2 (table)
-------
id (integer/autoincrement)
name2 (varchar(45))

vw_test (view)
------
Select * from test1 left join test2 on test1.test2 = test2.id
If I execute the following code, it updates the first record with no problem.

Code:
Dim conn as ADODB.Connection
Dim rs as ADODB.Recordset

Set conn = new ADODB.Connection
Set rs = new ADODB.Recordset

conn.connectionString = "Driver={MySQL ODBC 3.51 Driver};Server=111.111.111.111;Port=3306;Database=myDatabaseName;Uid=myUserName;Pwd=myPassword;Option=3;"
conn.open

rs.open "Select * from vw_test order by name1, name2",conn,adOpenStatic,adLockOptimistic

rs("Name1") = rs("Name1") & "-Edited"
rs.update

rs.close
conn.close
Now, if I do what SHOULD be the same thing using a Data Environment, I run into trouble.

Code:
I define the Data Environment connection w/ connection string:
Provider=MSDASQL.1;Persist Security Info=False;Extended Properties="Driver={MySQL ODBC 3.51 Driver};Server=111.111.111.111;Port=3306;Database=myDatabaseName;Uid=myUserName;Pwd=myPassword;Option=3;"

I define the command within the Data Environment as:
"Select * from vw_test order by name1, name2"
If I go into "design" and run the query. The data comes up, but if I try to change any data, I get the error mentioned above.

I Found that if I only order by fields from test1, I am able to update all that I want. Unfortunately, if I order by test2 fields, I cannot update anything.

This issue stems from a much larger program that I am working on. I was able to order a view by any of the main table (the table that the other tables were left joined to) with no problem, but as soon as I ordered by a field from another table in the view, it became non-updatable.

What is it that is different between the Data Environment and the regular code method that is causing this error?

Any thoughts are greatly appreciated.

Thanks,
Enigma