Re: Beginner: How do I link via ODBC from a PC into an AS400?

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Hi, Rick.

Feeling a little stupid here. I have installed IBM AS400 Client Access
Express to my PC. (It has installed an add-in in Excel and through the Excel
Data menu I can transfer csv files from the AS400 into Excel, so there is a
connection).

Through the Windows\Start\Programs menu I can view the ODBC data source
administrator dialogue window using the IBM AS400 Client Access Express
software. The ODBC Data source administrator has tabs: User DSN, System DNS,
File DNS, Drivers, Tracing, Connection, About. I have added a User DSN based
on the Client Access Driver, but I do not really know the significance of
each of these tabs.

At this stage, when I go into MSAccess, nothing happens after I select the
type = ODBC. I am not sure what to do to next. I guess that the ODBC driver
is installed but I have not correctly added the DSN. When you said 'Control
Panel' did you mean the ODBC administrator or the WinXP control panel from
the Start Menu? I would appreciate a bit more hand-holding through this.

Thanks,

John
--
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








.

Relevant Pages

  • Inno User Input Form and ODBC DSN
    ... I have a third party application that we currently install to hundreds of ... It is an ODBC System DSN. ... we manually create the ODBC and select the default database ... DSNs and the client will choose the DSN that related to his database. ...
    (microsoft.public.vb.general.discussion)
  • Inno User Input Form and ODBC DSN
    ... I have a third party application that we currently install to hundreds of ... It is an ODBC System DSN. ... we manually create the ODBC and select the default database ... DSNs and the client will choose the DSN that related to his database. ...
    (microsoft.public.vb.database)
  • RE: Perl DBI and DBD::ODBC for Oracle on Windows
    ... By adding sid=xxxx in the DSN, you should be able to point to the correct SID. ... If you already installed Oracle InstantClient SQLPLUS package, then I would recommend you to download the components: Basic and ODBC and extract them in the same folder as sqlplus. ... I've been trying to connect to a remote Oracle database through Windows ... Install ActivePerl on Windows. ...
    (perl.dbi.users)
  • Re: Open Database Connectivity Error
    ... e.g. one is an upgrade and the other is a fresh install ... >> for an ODBC data source name which does not exist. ... and that a Data Source Name (DSN) has been set up for Access ...
    (microsoft.public.word.mailmerge.fields)
  • Re: Word 2000/2002 - Proper Mail Merge steps for ODBC?
    ... > Document to connect to a Secured Access Database, ... using the ODBC provider for OLEDB. ... they have a .dsn extension. ... directly (in the Open/Select Data Source dialog box). ...
    (microsoft.public.word.mailmerge.fields)