By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,963 Members | 1,049 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,963 IT Pros & Developers. It's quick & easy.

Data Values not correct on linked table

P: n/a
Alright this is a new one to me. I have linked a table using ODBC to
our AS400. When I either open it directly or query it I get the
incorrect values for several fields. For example the query on the
linked table returns these 5 records...

CUCUST CUALPH
0188 RITA
0188 RITA
0188 RITA
0188 RITA
0188 RITA

.... But if I import the table then run the same query I get these
which are the corect values.

CUCUST CUALPH
0188 RITA
6188 RITA
7188 RITA
8188 RITA
9188 RITA

I am using Win 2k, Access 2002 w/ sp 2, IBM iseries Access for Windows
ver 5 rel 2 mod 0.
Nov 13 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
"bhieb" <bh***@alanritchey.com> wrote in message
news:29**************************@posting.google.c om...
Alright this is a new one to me. I have linked a table using ODBC to
our AS400. When I either open it directly or query it I get the
incorrect values for several fields. For example the query on the
linked table returns these 5 records...

CUCUST CUALPH
0188 RITA
0188 RITA
0188 RITA
0188 RITA
0188 RITA

... But if I import the table then run the same query I get these
which are the corect values.

CUCUST CUALPH
0188 RITA
6188 RITA
7188 RITA
8188 RITA
9188 RITA

I am using Win 2k, Access 2002 w/ sp 2, IBM iseries Access for Windows
ver 5 rel 2 mod 0.


AS400 tables often do not have Primary Keys or Unique Indexes on them.
When you create the link you are then asked to identify a column or columns
that make up unique entries so that Access can build a local index for
doing updates.

You MUST either provide an accurate set of column names or hit [Cancel] so
that no index is created. If you select columns that do not in fact
constitute unique entries in the table then one of the side effects is what
you are seeing. Another (more dangerous) side effect is that when you edit
one row you will end up making changes in more than that row on the server.

Delete the link and then recreate it making sure to press [Cancel] at that
prompt and see if it makes any difference. If it does then see if you (or
someone else) can define a PK or Unique Index on the AS400 table and
recreate the link afterwards. Then you don't have to rely on the local
index which can be dangerous if you don't select accurately.
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com


Nov 13 '05 #2

P: n/a
I have seen that behavior before, but this table does not allow me to
select a key. It does automatically assign one, but it is on a field
that I know has duplicate values. Is there anyway to prevent this
from happening, other that going to a 400 programer. I have gotten
around it by creating a pass-through query and using it as the source
(since I only need read only), but obviously I don't want to do that
each time.

AS400 tables often do not have Primary Keys or Unique Indexes on them.
When you create the link you are then asked to identify a column or columns
that make up unique entries so that Access can build a local index for
doing updates.

You MUST either provide an accurate set of column names or hit [Cancel] so
that no index is created. If you select columns that do not in fact
constitute unique entries in the table then one of the side effects is what
you are seeing. Another (more dangerous) side effect is that when you edit
one row you will end up making changes in more than that row on the server.

Delete the link and then recreate it making sure to press [Cancel] at that
prompt and see if it makes any difference. If it does then see if you (or
someone else) can define a PK or Unique Index on the AS400 table and
recreate the link afterwards. Then you don't have to rely on the local
index which can be dangerous if you don't select accurately.

Nov 13 '05 #3

P: n/a
"bhieb" <bh***@alanritchey.com> wrote in message
news:29**************************@posting.google.c om...
I have seen that behavior before, but this table does not allow me to
select a key. It does automatically assign one, but it is on a field
that I know has duplicate values. Is there anyway to prevent this
from happening, other that going to a 400 programer. I have gotten
around it by creating a pass-through query and using it as the source
(since I only need read only), but obviously I don't want to do that
each time.


I have never heard of Access not prompting you for index columns unless the ODBC
table already has a Primary Key or Unique Index in which case you should NOT see
duplicates in that field.

It might be possible in the case of an TimeStamp field which goes out to
microseconds on the AS400 but which is truncated to the second when viewed in
Access. This would "look like" there were duplicates in the field because of
the rounding.
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com
Nov 13 '05 #4

P: n/a
I agree, but this primary key is on a 4 digit text field (CUALPH) and
there are definately duplicates. Below is the actual data retrieved
when you query for RITA in the CUALPH field. Note that it does return
5 records, but just gets the first value for all of the other fields.
Below that is what should be returned (and is returned via the pass
through query).

CUCUST CUALPH
0188 RITA
0188 RITA
0188 RITA
0188 RITA
0188 RITA

CUCUST CUALPH
0188 RITA
6188 RITA
7188 RITA
8188 RITA
9188 RITA

I checked the 400 and it is not set as a unique key, rather just a
keyed field.

I have never heard of Access not prompting you for index columns unless the ODBC
table already has a Primary Key or Unique Index in which case you should NOT see
duplicates in that field.

It might be possible in the case of an TimeStamp field which goes out to
microseconds on the AS400 but which is truncated to the second when viewed in
Access. This would "look like" there were duplicates in the field because of
the rounding.

Nov 13 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.