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.
4  2102 
"bhieb" <bh***@alanritc hey.com> wrote in message
news:29******** *************** ***@posting.goo gle.com... 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
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.
"bhieb" <bh***@alanritc hey.com> wrote in message
news:29******** *************** ***@posting.goo gle.com... 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
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. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
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 issues are
stord in the field, IssueDesc. BUT I'd like to change that so that the
issues are stored in the IssueID field.
On my form, the "Control Source" was linked to IssueDesc. So, the
issues the user selected are stored in the field, IssueDesc,...
|
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
2000) as follows
USERS TABLE
|
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 correct cells within
that worksheet. The Excel Book is made up of 12 worksheets named
Jan-Dec. Each worksheet has columns labeled as each day of that month.
Column 'A' is reserved for 19 rows named "room1 - room19". The data I
am importing from the...
|
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 access the
database layer. However, the code works in a pretty cumbersome and
ungeneric way. Basically every query, update, and insert has its own
function. So you see a lot of functions like
webService.InsertCustomer(name, age, phone);
or...
|
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 be
greater than 255 characters.
2. I have an access database. I link (not import) to the contents of
the excel spreadsheet. In the design view in access, Column A has the
data type "memo".
| | |
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 exist named Country,
MedicalCondition, and Customer.
|
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 DWORD values (see
below for more).
So what I trying to achieve is creating a look up table of a IP adress
with a start adress (a DWORD value) and a end adress (a DWORD
value) which I would like to look up to the get the Country name
|
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 response (1, 2 or
3) varies- such that for some questions "I agree" is = to 3 points,
and other times "I agree" is = to 1. I created an "answer key" table,
such that each record is a question, and for the fields of "I agree",
"I disagree" and "I...
|
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 software was ISAM-based and relied on a product called
Transoft U/SQL to create "simulated" ODBC connections to the data for
querying and reporting. The new software is using an Informix RDBMS,
and so the ODBC driver must be flipped to use the Informix...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
| | |
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 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 a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| | |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
