Link to AS/400

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,
I need to update a customer database and i would like to do it via Access as
it is more friendly than RPG applic.
I create a new access database. Then I went to File/Get External Data and
then Link to the AS/400 database.
My problem is that it does not allow any modifications. Can someone help me
on this matter!
Thanks for your help.
Barbara
 
Barbara said:
Hi,
I need to update a customer database and i would like to do it via
Access as it is more friendly than RPG applic.
I create a new access database. Then I went to File/Get External Data
and then Link to the AS/400 database.
My problem is that it does not allow any modifications. Can someone
help me on this matter!
Thanks for your help.
Barbara
Click to expand...

In order for an ODBC link to be updateable it must have a unique index. If the
table on the AS400 had a Primary Key or a unique index, then Access would have
seen that when you created the link and the link would be editable. When there
is no PK or unique index on the remote table then Access will prompt you when
you create the link to indicate which fields it can use to create a local unique
index. If you do that then the link will also be editable.

BUT...you MUST choose fields that actually are unique on the server. If you
choose fields that are not unique then an edit to one row in Access will
actually update multiple rows on the AS400 so you have to know what you are
doing and be familiar enough with the table so you can choose the correct
fields. If you just make a guess you could end up making a real mess of the
data on your server.

Note that on an AS400 it is not that unusual for a table to exist that has no
combination of fields that are guaranteed to be unique. Such tables cannot be
reliably updated via ODBC.
 
I think you may also find that the table needs to be journalled on the
AS/400.

Peter Kinsman
 
Peter said:
I think you may also find that the table needs to be journalled on the
AS/400.
Click to expand...

I have not found that to be universally true, but depending on how the ODBC
connection is configured and what kind of update operations you perform it will
be the case some of the time.
 
Hi again,
I'm very new on this issue (linking to Databases).
I am trying, as I told before, to connect to the AS/400. I create a new
database, ask to link to a AS/400 database, choose 3 fields that are unique.
He ask me for a username + password to access to the AS/400 but then I get :
"Reserved error (-7778) there is no message for this error".
Where did I went wrong? Any clue on this!
Thanks,
Barbara

"Rick Brandt" escreveu:
 
Barbara said:
Hi again,
I'm very new on this issue (linking to Databases).
I am trying, as I told before, to connect to the AS/400. I create a
new database, ask to link to a AS/400 database, choose 3 fields that
are unique. He ask me for a username + password to access to the
AS/400 but then I get : "Reserved error (-7778) there is no message
for this error".
Where did I went wrong? Any clue on this!
Thanks,
Barbara
Click to expand...

You might not have adequate permissions to the table on the AS400 to link to
it. Try creating a passthrough query to the same table and see if that
works.

New Query - SQL Specific - Passthrough

Set the Connect property to the same ODBC DSN you are using in your attempts
to link. Then use SQL similar to...

SELECT * FROM LibraryName.TableName
 
Back
Top