473,320 Members | 1,993 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,320 software developers and data experts.

Data Values not correct on linked table

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
4 2072
"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
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
"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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Megan | last post by:
I have 2 tables, CASE and ISSUE. My table, CASE, stores the issues pertaining to that particular case. The table, CASE, was designed with 2 fields, IssueID and IssueDesc. Right now, all of the...
0
by: Alistair | last post by:
Hi all, I am creating a database based site that keeps track of books, who has read them and the comments they have. After a little help in M.P.I.asp.DB I managed to create a database (access...
7
by: Darren | last post by:
I have been attempting to create a reservation planning form in excel that imports Data from an Access database and inserts that information automaticly into the correct spreed sheet and the...
2
by: headware | last post by:
I'm relatively new to ASP.NET and ADO.NET, but I have a basic design question regarding the use of web services and APS.NET applications. Right now we have an application that uses web services to...
0
by: Grip | last post by:
Hi, I have gone throught the group and Microsoft's online help and have seen many suggestions but I am still seeking clarity: 1. I have an excel spreadsheet. Column A contains text that may...
8
by: Wingot | last post by:
Hey, I have a program I am trying to write using Visual C#, SQL Server 2005/2008, and Visual Studio 2008, and one part of it includes a Schema called Client. Inside this schema, three tables...
2
by: kl | last post by:
Hi, I'm trying to learn some STL using map or hash_map would maybe even better to use but I don't really know how to find a specific struct depending on a DWORD value that is in range of two...
1
by: Maria DiGiano | last post by:
I am using Access to organize data from a survey which uses a Likert scale to measure response- the scale is 3 points- "I agree", "I don't know" and "I disagree". The numerical value of each...
1
by: steven_nospam at Yahoo! Canada | last post by:
I am a UNIX person (not much experience with MS Access) who during a recent upgrade on an IBM RS/6000 server had to convert our system due to an upgrade to a new software revision. The old...
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...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
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)...
0
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...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
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.