Re: Beginner: How do I link via ODBC from a PC into an AS400?
- From: JohnW <JohnW@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Wed, 21 Nov 2007 06:00:02 -0800
Forget the last line about control panel. I see that I get to it through
Administrative tools. Duh!!
--
John Whyte
"Rick Brandt" wrote:
JohnW wrote:.
Hi,
I would like some basic help to set up a ODBC link across the network
from ACC2003 on a PC to an AS400 which has DB2 tables on it. I
haven't done this before, so basic steps would be most helpful,
please.
I have been asked to provided more user friendly on screen
information than the current AS400 based package can provide. It is
a custom package using text input and text line reports and was
installed in 1999, I think.
I'm OK handling the data in Access, I think, if I can make a live
link to its tables. I can then base forms, reports, charts etc. on
them and on queries. However, I am totally clueless about the AS400
machine. Is it a bit like the old foxpro with table files and index
files?
Any help is very much appreciated.
Thanks,
John
Install the IBM ODBC driver for the AS400 on every PC that needs to connect.
Then create an ODBC DSN in control panel on every PC that needs to connect that
specifies the AS400 server and database to connect to. Then in Access use...
File
Get External Data
Link Tables
(type = ODBC)
(Choose your DSN)
(choose your tables)
It is not unusual for tables on an AS400 to not have primary keys defined. In
those cases you will need to specify the fields that Access can use to specify a
unique local index. Otherwise the link will not be updateable.
You CANNOT just guess on which fields to use! If you specify a combination of
fields that is not actually unique on the server then when your users update one
row they will unknowingly be updating multiple rows. You can quickly trash the
data on the AS400 if you don't know what you are doing.
If you want your Access app to be read only then just hit [Cancel] when you are
prompted to select the unique field combination and the link will be read only.
Any tables that have uniques indexes or PKs on the server will automatically be
editable unless you control that with user permissions on the AS400.
Using passthrough queries instead of table links is another way to ensure that
everything is read only, but creating local Access queries that use multiple
passthrough queries as inputs will not be efficient. In that case you should
put the joins into the passthrough queries. That will require a thorough
knowledge of UDB400 SQL syntax.
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
- References:
- Beginner: How do I link via ODBC from a PC into an AS400?
- From: JohnW
- Re: Beginner: How do I link via ODBC from a PC into an AS400?
- From: Rick Brandt
- Beginner: How do I link via ODBC from a PC into an AS400?
- Prev by Date: Re: Beginner: How do I link via ODBC from a PC into an AS400?
- Next by Date: RE: data conundrum
- Previous by thread: Re: Beginner: How do I link via ODBC from a PC into an AS400?
- Next by thread: Re: Runtime Delete Query Problem
- Index(es):
Relevant Pages
|
