Linked DB2 table in MSAccess 2000 shows #Deleted on every record

"Klemens" <me@privacy.net> wrote in message
news:2n************@uni-berlin.de...

The linked table has a bigint in primary key columns.
I've read that service pack 8 on Jet should solve this but it didn't.
On patch1 options I only found to map timestamp to char(26) entry
for similar problems on timestamp fields but nothing for Bigint.
DB2 is UDB8.1 Fix 6 on Win2000
Are there any other solutions?

I don't understand your problem. What are you trying to do? What error
message are you getting? Are you sure it's a DB2 problem and not a problem
with Jet or MS Access?

Rhino

I'm asking for a solution or workaround for beeing able to to work in
msaccess with linked DB2 tables having bigint column in primary key. Access
shows #Deleted on every column.
The better way would be a solution in access or jet but may be there is a
workaround, like that for timestamp columns, in DB2.

Thanks
Klemens

"Rhino" <rh****@NOSPAM.sympatico.ca> schrieb im Newsbeitrag
news:NG********************@news20.bellglobal.com. ..

"Klemens" <me@privacy.net> wrote in message
news:2n************@uni-berlin.de...

The linked table has a bigint in primary key columns.
I've read that service pack 8 on Jet should solve this but it didn't.
On patch1 options I only found to map timestamp to char(26) entry
for similar problems on timestamp fields but nothing for Bigint.
DB2 is UDB8.1 Fix 6 on Win2000
Are there any other solutions?

I don't understand your problem. What are you trying to do? What error
message are you getting? Are you sure it's a DB2 problem and not a problem
with Jet or MS Access?

Rhino

It sounds like your problem is with MS Access and/or Jet, not DB2, but I
could be wrong. I don't have MS Access myself, otherwise I'd create a little
DB2 table with a bigint primary key and try to do what you are doing to see
what the problem might be.

You could try posting to an MS Access newsgroup or maybe someone else on
this newsgroup will have some DB2/MS Access experience. Sorry I couldn't be
more help.

Rhino

"Klemens" <me@privacy.net> wrote in message
news:2n************@uni-berlin.de...

I'm asking for a solution or workaround for beeing able to to work in
msaccess with linked DB2 tables having bigint column in primary key. Access shows #Deleted on every column.
The better way would be a solution in access or jet but may be there is a
workaround, like that for timestamp columns, in DB2.

Thanks
Klemens

"Rhino" <rh****@NOSPAM.sympatico.ca> schrieb im Newsbeitrag
news:NG********************@news20.bellglobal.com. ..

"Klemens" <me@privacy.net> wrote in message
news:2n************@uni-berlin.de...

The linked table has a bigint in primary key columns.
I've read that service pack 8 on Jet should solve this but it didn't.
On patch1 options I only found to map timestamp to char(26) entry
for similar problems on timestamp fields but nothing for Bigint.
DB2 is UDB8.1 Fix 6 on Win2000
Are there any other solutions?

I don't understand your problem. What are you trying to do? What error
message are you getting? Are you sure it's a DB2 problem and not a problem with Jet or MS Access?

Rhino

This problem happens because Access doesn't have a data type that
corresponds to Bigint. When you display a table, Access will re-read every
row (by the primary key) to see if they are still there (and haven't been
deleted by an external app). Because of the data type problem, Access will
not be able to read the rows correctly so does not find them. It will
therefore think the rows have been deleted and marks them as such. As
mentioned, this problem also happens with timestamp cols. IBM have put a
workaround option so that the DB2 Client returns timestamp cols as
character. Access can handle char strings OK, so is able to display and re-
read them OK. It looks like there is no workaround option for Bigint, so
there is no simple resolution for your problem. What does work however is
to create a view on your table with the bigint col converted to character -
eg. "create view bigintview (bigintcol, col2) as select char(bigintcol),
col2 from biginttab". You can display and update this view in Access OK
(and this updates your table).

Phil Castle

On Mon, 9 Aug 2004 18:27:39 +0200, Klemens <me@privacy.net> wrote:

The linked table has a bigint in primary key columns.
I've read that service pack 8 on Jet should solve this but it didn't.
On patch1 options I only found to map timestamp to char(26) entry
for similar problems on timestamp fields but nothing for Bigint.
DB2 is UDB8.1 Fix 6 on Win2000
Are there any other solutions?

Thanks
Klemens

--
Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/

The workaround works.
I had to do some rework on forms that where linked on each other on these
columns.

Do you know why this problem didnt happen with UDB V7.2?

Thanks
Klemens

"Phil Castle" <ph**@querytool.com> schrieb im Newsbeitrag
news:opscjclwoly8iall@localhost...

This problem happens because Access doesn't have a data type that
corresponds to Bigint. When you display a table, Access will re-read every
row (by the primary key) to see if they are still there (and haven't been
deleted by an external app). Because of the data type problem, Access will
not be able to read the rows correctly so does not find them. It will
therefore think the rows have been deleted and marks them as such. As
mentioned, this problem also happens with timestamp cols. IBM have put a
workaround option so that the DB2 Client returns timestamp cols as
character. Access can handle char strings OK, so is able to display and re- read them OK. It looks like there is no workaround option for Bigint, so
there is no simple resolution for your problem. What does work however is
to create a view on your table with the bigint col converted to character - eg. "create view bigintview (bigintcol, col2) as select char(bigintcol),
col2 from biginttab". You can display and update this view in Access OK
(and this updates your table).

Phil Castle

On Mon, 9 Aug 2004 18:27:39 +0200, Klemens <me@privacy.net> wrote:

The linked table has a bigint in primary key columns.
I've read that service pack 8 on Jet should solve this but it didn't.
On patch1 options I only found to map timestamp to char(26) entry
for similar problems on timestamp fields but nothing for Bigint.
DB2 is UDB8.1 Fix 6 on Win2000
Are there any other solutions?

Thanks
Klemens

--
Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/

Don't know.... I am running DB2 v7.2 here and the problem happens for me.
There might be a dependency on your actual data values.

Phil Castle.
On Wed, 11 Aug 2004 11:42:11 +0200, Klemens <me@privacy.net> wrote:

The workaround works.
I had to do some rework on forms that where linked on each other on these
columns.

Do you know why this problem didnt happen with UDB V7.2?

Thanks
Klemens

"Phil Castle" <ph**@querytool.com> schrieb im Newsbeitrag
news:opscjclwoly8iall@localhost...

This problem happens because Access doesn't have a data type that
corresponds to Bigint. When you display a table, Access will re-read
every
row (by the primary key) to see if they are still there (and haven't
been
deleted by an external app). Because of the data type problem, Access
will
not be able to read the rows correctly so does not find them. It will
therefore think the rows have been deleted and marks them as such. As
mentioned, this problem also happens with timestamp cols. IBM have put a
workaround option so that the DB2 Client returns timestamp cols as
character. Access can handle char strings OK, so is able to display and

re-

read them OK. It looks like there is no workaround option for Bigint, so
there is no simple resolution for your problem. What does work however
is
to create a view on your table with the bigint col converted to

character -

eg. "create view bigintview (bigintcol, col2) as select char(bigintcol),
col2 from biginttab". You can display and update this view in Access OK
(and this updates your table).

Phil Castle

On Mon, 9 Aug 2004 18:27:39 +0200, Klemens <me@privacy.net> wrote:

> The linked table has a bigint in primary key columns.
> I've read that service pack 8 on Jet should solve this but it didn't.
> On patch1 options I only found to map timestamp to char(26) entry
> for similar problems on timestamp fields but nothing for Bigint.
> DB2 is UDB8.1 Fix 6 on Win2000
> Are there any other solutions?
>
> Thanks
> Klemens
>
>
>
>

--
Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/

--
Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/

I have to correkt. It dosn't work. I get SQLCODE 151 on insert. It only
works for update and select.

Klemens

"Klemens" <me@privacy.net> schrieb im Newsbeitrag
news:2n************@uni-berlin.de...

The workaround works.
I had to do some rework on forms that where linked on each other on these
columns.

Do you know why this problem didnt happen with UDB V7.2?

Thanks
Klemens

"Phil Castle" <ph**@querytool.com> schrieb im Newsbeitrag
news:opscjclwoly8iall@localhost...

This problem happens because Access doesn't have a data type that
corresponds to Bigint. When you display a table, Access will re-read every row (by the primary key) to see if they are still there (and haven't been deleted by an external app). Because of the data type problem, Access will not be able to read the rows correctly so does not find them. It will
therefore think the rows have been deleted and marks them as such. As
mentioned, this problem also happens with timestamp cols. IBM have put a
workaround option so that the DB2 Client returns timestamp cols as
character. Access can handle char strings OK, so is able to display and

re-

read them OK. It looks like there is no workaround option for Bigint, so
there is no simple resolution for your problem. What does work however is to create a view on your table with the bigint col converted to

character -

eg. "create view bigintview (bigintcol, col2) as select char(bigintcol),
col2 from biginttab". You can display and update this view in Access OK
(and this updates your table).

Phil Castle

On Mon, 9 Aug 2004 18:27:39 +0200, Klemens <me@privacy.net> wrote:

The linked table has a bigint in primary key columns.
I've read that service pack 8 on Jet should solve this but it didn't.
On patch1 options I only found to map timestamp to char(26) entry
for similar problems on timestamp fields but nothing for Bigint.
DB2 is UDB8.1 Fix 6 on Win2000
Are there any other solutions?

Thanks
Klemens

--
Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/

Please, refer to this forum (in German):

http://www.ruban.de/cgi-bin/yabb/YaB...num=1107953134

It works for me (DB2 8.1 ODBC client and DB2 7.1 database on z/OS)
In few words:

1. On the client side, add these two lines in db2cli.ini:
[in Database Section]
MapTimestampDescribe=1
MapTimestampCDefault=1

2. On DB2 (7 or 8) for z/OS (OS390) side, apply:
APAR/Fix PQ96188 / UQ96410
APAR/Fix PQ83561/ UQ87586

They also say there could be some errors on z/OS side when running INSERT
statements from MS Access that involve timestamps without microseconds or
with minutes less than 10. I didn't test these issues yet. If you only
read timestamps, it should be fine.