Microsoft Access Frontend to AS400 Data

[compgeek27]

Has anyone had success using Microsoft Access as a frontend to DB2 Databases on the AS400. We are wanting to use Microsoft Access to tie together several different databases. We can bring the data over just fine, query it, and use it for reports, but we have not yet been able to Add/Update/Delete the data .

I am using Access 97 & XP with a Client Access (32-bit) ODBC driver. I am pretty sure have the DSNs setup properly.

Any insight would be appreciated.

Jeff

 

[as400pro]

Are you receiving any error messages?
Does the user profile being used via the ODBC driver have authority to add, update, and delete data from the files on the AS/400?
Could there be exit programs on the AS/400 keeping ODBC data from modifying the AS/400 files?
Is the ODBC driver (Data Source) setup as read only?



T. Bishop

http://www.as400pro.com

"My agility dog is smarter than your honor student.

 

[compgeek27]

[blue]Are you receiving any error messages?[/blue]

YES:
ODBC--update on a linked table 'tablename' failed.
(IBM)(Client Access Express ODBC Driver (32-bit))(DB2/400 SQL)SQL7008 - tablename in QGPL not valid for operation. (#7008)

[blue]Does the user profile being used via the ODBC driver have authority to add, update, and delete data from the files on the AS/400?[/blue]

Pretty Sure. We have logged in as several different people including the QSECOFR.

[blue]Could there be exit programs on the AS/400 keeping ODBC data from modifying the AS/400 files?[/blue]

???

[blue]Is the ODBC driver (Data Source) setup as read only?[/blue]

NO.




Could it have something to do with a required Primary Key for Microsoft Access? The table has a key but I'm not sure Access is able to process without its own.

 

[as400pro]

I don't think it would be the key unless you are trying to write a duplicate record.
Could it be related to commitment control?

I search your SQL error at the Midrange.com Archives and saw people posted that SQL error in response to a commitment control problem.

You may want to search that site in more detail.



T. Bishop

http://www.as400pro.com

"My agility dog is smarter than your honor student.

 

[leveyp]

If you're not using latest i-series access you may need to journal the physical files to enable write/update.

 

[dschroth]

I think Access absolutely does need a unique key field to be identified when the ODBC link is created in order for updates and deletes to work properly. This should happen automatically if the table has a primary key, but if it prompts you for the unique identifier then you need to select a unique field or combination of fields.

Dick Schroth
Schroth Systems Consulting, Inc.

 

[roccorocks]

This could be the start of a new thread. How do you know whether an AS/400 has a DB2 database or not? I am currently doing screen scrapes using the WRQ Reflections software. How do I find this information out (What is the DB name, tables, etc)?

 

[flapeyre]

DB2/400 is integrated into OS/400, and the only way that any other sort of database would be there is if it were either on the IFS, or in a Linux (or whatever) partition.

De mortuis nihil nisi bonum.

 

[jmd0252]

I have sen that you can use a product call DB2Connect, that makes the DB2/400 files available to a DB2 database. It does of course require some setup etc, etc. Do you have an knowledge of the AS400 commands, and what libraries the files are in??

 

[roccorocks]

Thanks Jmd0252 and flapeyre, I have found AS400 commands on a couple sites (have not tried them yet). I have no idea where the libraries are and what the names are. Something else I have been looking at is using OLE DB and an SNA engine to access the AS/400 data. Has anyone successfully done this?

Tim R