General discussion

Locked

"No current record" msg Access to DB2

By BlueKnight ·
When attempting to update or delete data in a
linked DB2 table from within MS Access 97 (or
2000), Access returns a message box saying "no current record" even though the record is selected.

Records inserted by a batch process (we're running OS/390) cannot be updated or deleted in Access. If data is inserted using Access, it can be updated/deleted from either Access or batch.

Our DBA has done some testing and seems to think it is related to the timestamp column which is 26 bytes textin DB2 apps. and is type "timestamp" in Access. We have no idea why this might be happening. Anybody have any clues or solutions for this?

This conversation is currently closed to new comments.

4 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

"No current record" msg Access to DB2

by erikdr In reply to "No current record" msg A ...

I did DB2 - Access 2.0 tests some time ago, and am not aware of this problem; but lots of others happened and could, in the end, be resolved.
I guess you will have to dig deeper to analyse the root cause. Tracing the ODBC traffic between Access andDB2 is essential for this; the MS ODBC kit and even plain control panel do offer tools for this.
Try things like:
- reproducing the SQL generated by Access into a script, and running this script (through ODBC) against DB2.
- If still error, trouble is in DB2 and its ODBC driver. If no error, trouble is in Access.
- Same test without timestamp field
- Comparing the way timestamps are created by Access and by OS/390. Probably there ARE differences, which will force you to eliminate those - maybe by applying different settings in the ODBC driver or inside Access.

Hope this helps,

<Erik> - The Netherlands

Collapse -

"No current record" msg Access to DB2

by BlueKnight In reply to "No current record" msg A ...

Further research revealed that the problem was with the timestamp column. DB2 keeps timestamps down to the millisecond level. MS Access cannot handle that precision. When Access finds a timestamp column which has significant digits beyond thethird high-order position (thousands if you look at it as a decimal) it returns the "No current record found" message. Our solution is to always fill the milliseconds portion with zeros. Time will tell how Access will handle records where DB2 has automatically updated the timestamp itself as opposed to our supplying it in input.

Collapse -

"No current record" msg Access to DB2

by BlueKnight In reply to "No current record" msg A ...

Your suggestions are good. We have done enough testing to determine that ODBC is not the culprit. It appears to us now that there is something in DB2 that needs to be set differently.

I recompiled my batch application program to eliminate the timestamp column from the append process. This would let DB2 plug in its default value when the rows were appended rather than using supplied data.

I then ran the batch job which appended 5 rows to the TEST table. Next, I opened the table in Access 97 and viewed my new rows -- they all contained entries in the timestamp column (DB2 default). Next, I successfully deleted a row. I also was able to successfully change the contents of a column in another row.

I then recompiled my application so that it would append the data to the "real" table. The results were quite different. I was able to view the table properly and there were entries in the timestamp column as expected. When I attempted to delete a row, I got the "no current record" message. The same message appeared when I tried to modify the contents of a column in one of the rows.

The only difference I know of between these two tables is that the "real" table does not allow null columns. That was done so we could do our initial testing uninhibited when we did not supply data for all columns.

Collapse -

"No current record" msg Access to DB2

by BlueKnight In reply to "No current record" msg A ...

This question was closed by the author

Back to Software Forum
4 total posts (Page 1 of 1)  

Related Discussions

Related Forums