Re: Spurious record locking with ODBC

> We have developed a medium sized app under NT using MS Access, > Intersolv ODBC 3.11 drivers and Informix SE under SCO Open Server 5. > > The first live trials have revealed a problem with spurious record > locks which appear to maintain state after rebooting both client and > server. The "locked" records can be modifed without problems from the > server using ISQL/DBAccess but attempting to modify them from within > Access at table level produces the message "This record has been > modified by another user...". > > When we delibertaely attempt to modify the same record from two > different clients, we get a similar but not identical error message. > > Can anyone help or point me to where I can get UK commercial support > for the Intersolv ODBC drivers? I suspect that you have at least one FLOAT-type column, and that you have declared your table with a primary key inside the CREATE TABLE statement. Also, you have no other UNIQUE indexes. Correct so far? I could be wrong, I don't know if SE supports PRIMARY KEY clauses. Assuming that I'm right, the problem is in the way Access interprets Informix's catalog table information. When you define your table like I described, Informix creates a unique index to support the primary key. It names the index with a system-generated name, and to insure that you don't name an index the same name, it uses a space for the first character of the index name. Trust me, you can't name an index that way yourself. Now, when Access looks at the system catalog, it doesn't like this strangely-named index and ignores it, so he can't determine the primary key for the table. No problem, he tries to find any other unique index so that each row can be uniquely identified. Actually, I read somewhere that Access uses the first unique index it finds (instead of trying to identify the actual primary key), based on the alphabetical order of the index name, but it ignores those that don't start with a valid letter. Well, as I said, I bet that you don't have one of those. Finally, in desparation, Access tries to identify rows by the values that each column contains. This results in SQL statements like: UPDATE table SET column1 = 'new value' WHERE column1 = 'old value' AND column2 = 'old col2 value' AND column3 = 'old col3 value' ... This would work except that FLOATs are defined as approximate numbers and are subject to rounding. If Access rounds differently than Informix, you'll never find a row that matches on all columns. As a result, Access gets a bad return code from the UPDATE statement and reports that someone else changed the row. Does that fit your situation? If so, your next question is, "How do I get around this?" It will require a little re-work of your table definition. Basically, create the table with no primary key, then build a unique index on the columns of the primary key, and finally alter the table to add the primary key. Informix will recognize your index as suitable for use in enforcing the primary key constraint, and Access will recognize it because it doesn't have a blank for the first character of the name. Let me know if this fixes the problem. Again, not knowing SE, this may not apply. Mark Collins mcollins@us.dhl.com