Kbase P156152: Error: "Can't access record." editing a query in Microsoft Query
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
19/11/2009 |
|
Status: Unverified
SYMPTOM(s):
SQL: Error: "Can't access record. More columns are needed to identify it, or another user changed it." editing result set in Microsoft Query
Can't access record. More columns are needed to identify it, or another user changed it.
The error is returned when editing a 'blank' field using Microsoft Query via EXCEL or ACCESS.
FACT(s) (Environment):
Windows
Progress 9.x
OpenEdge 10.x
Excel 2003
Excel 2007
The error does not occur when editing non blank fields with the UNKNOWN ? value or data other than the empty string "".
CAUSE:
Microsoft Query generates an UPDATE statement that fails when editing blank fields whose value is different from the NULL value or the UNKNOWN value ?.
For example, when a user edits the field Address2 of the Customer table using the Microsoft Query, the Microsoft Query generates the following UPDATE statement:
"UPDATE PUB.Customer SET Address2 = ? WHERE Address2 IS NULL AND CustNum = ?\ 0"
The above statement fails when using Microsoft Query because the empty string "" is not equal to NULL.
FIX:
This error is generated by Microsoft Query whenever the user edits a ?blank? field whose current value the empty string "" and is not NULL (in SQL lingo) i.e. not UNKNOWN ? (in 4GL/ABL lingo).
The solution to this issue to:
1. Change the schema to assign the UNKNOWN value ? to the INITIAL attribute of all the user database fields whose current INITIAL ATTRIBUTE is set to ?blank?. This ensures that all records created in the future will not have any empty string ?blank? field values that could trigger this Microsoft Query error. The attached AssignFieldBlankInitialValuesTheUnknownValues.p procedure does this job for the connected database.
2. Change the values of all existing blank user database fields must to the UNKNOWN value ? or some other non empty string to prevent the existing data from triggering this Microsoft Query error . The attached ChangeAllBlankFieldsValuesToUnkownValues.p procedure does this job for the connected database.
Please note:
A. The attached AssignFieldBlankInitialValuesTheUnknownValues.p procedure, or a variation thereof, must be executed against all tables that will be edited using Microsoft Query regardless whether these tables have data or not. This will ensure that records created in the future will not generate this error when edited using Microsoft Query.
B. The attached ChangeAllBlankFieldsValuesToUnkownValues.p procedure, or a variation thereof, must be executed against all tables that will be edited using Microsoft Query only if these tables have data. This will ensure that existing data will not generate this error when edited using Microsoft Query.