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

[JohnW]

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

 

[Rick Brandt]

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.

 

[JohnW]

Thanks, Rick.

I am back at that site on Wednesday and will implement what you have said.

John
--
John Whyte

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.

 

[JohnW]

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

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]

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.

User DSN is usable by the PC user who created it only
System DSN is usable by any user logged into the PC
File DSN is a DSN stored as a file on disk rather than in the registry

I would use User DSN for this.

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.

When you select ODBC the ODBC manager dialog should appear and then you would
select the DSN you previously created. That should be followed by a list of
tables to which you can connect.

 

[JohnW]

Forget the last line about control panel. I see that I get to it through
Administrative tools. Duh!!
--
John Whyte

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.

 

[JohnW]

Rick,

In Access when I select File, Get External Data, Link Tables, the Link
dialogue box opens. The moment I select ODBC Databases from Files of Type,
the Link dialogue box disappears/closes. Am I doing this correctly, or have
I missed something out?

Thanks,

John

 

[JohnW]

I've fixed it. Norton AntiVirus, Settings, Virus and Spyware Protection
Options, Miscellaneous, How to keep Microsoft Office Documents Protected,
un-check Turn on scanning for Microsoft Office Documents.

John