473,321 Members | 1,667 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,321 software developers and data experts.

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

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

Nov 12 '05 #1
8 8071

"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
Nov 12 '05 #2
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

Nov 12 '05 #3
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


Nov 12 '05 #4
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/
Nov 12 '05 #5
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/

Nov 12 '05 #6
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/
Nov 12 '05 #7
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/


Nov 12 '05 #8
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.
Nov 18 '05 #9

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

20
by: Neil | last post by:
I have an Access 2000 MDB file with a SQL 7 back end. I have a main table with 50,000 records; and I have a selections table with 50,000 records for each machine that uses the database (about...
2
by: Klemens | last post by:
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. Are there any other solutions? Thanks Klemens
1
by: GGerard | last post by:
Hello I am working with Access 2000 I have two tables joined on a one to many relationship between two fields: Table1:FieldID (one) is joined to Table2:FieldMyID (many) Field Properties...
8
by: Bri | last post by:
Greetings, I'm having a very strange problem in an AC97 MDB with ODBC Linked tables to SQL Server 7. The table has an Identity field and a Timestamp field. The problem is that when a new record...
0
by: Igor | last post by:
An error "ODBC connection to failed" is raised when I am trying to get data from a table in Microsoft Access (97, or 2000) which is actually a linked table to another table in SQL Server 7 . The...
0
by: Murali | last post by:
In MsAccess project I Create the Link table with Sqlserver 2000. I am facing the multiuser Problem in my application. The problem describes below: If one user is updating one record and at that...
2
by: eilob | last post by:
Hi, I have a linked table in Access which is linked to an Inbox Folder in Microsoft Outlook. The table will reflect the emails that come in into the Inbox. I need that every time an email comes in...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.