Link to AS/400 (Access 2002)

Topic

New Reply

I am embarking on a new project which will require me to use MS Access as a front-end reporter to a live AS400 database. I plan to connect through ODBC, unless there is a better way. I am pretty much a newby with it comes to AS400. I have quite a bit of experience at linking to external SQL Server tables. Would there be a significant difference?

1. Does anyone have any experience with this? As far as Access is concerned, is it much different that linking to other external tables?
2. I am particularly concerned about degrading performance on the AS400 due to the ODBC calls.
3. Can anyone point me to a primer or whitepaper on ODBC connectivity with an AS/400?

Thanks

Reply | Quote

Replies

Randall

I connect to an AS/400 database through ODBC and use Access as a report writer. The table linking process is the same as other external table however care needs to be taken over opening tables due to the amount of records involved. What I tend to do is create a make table query of the records I require and then run the report from this local table.

Graham

Reply | Quote

Graham–

Thanks for the information. I’ll keep your Make Table solution handy. I am interested in your “care needs to be taken when opening tables” comment. Is this because Access has a hard time handling the number of records (which I’m sure is true–one of the files in this database has over 8M records), OR are you saying this causes a performance degradation in the AS/400. Does querying the AS/400 cause any performance issues? My marching orders are that my solution should have little or preferrably no decernable performance degradation on the AS/400.

Randy

Reply | Quote

Randall

There are no noticeable performance issues on the AS/400 in my experience – the limitation is Access. You definitely do not want to be reading 8M records into Access.

Graham

Reply | Quote

Randall

There are no noticeable performance issues on the AS/400 in my experience – the limitation is Access. You definitely do not want to be reading 8M records into Access.

Graham

Reply | Quote

Graham–

Thanks for the information. I’ll keep your Make Table solution handy. I am interested in your “care needs to be taken when opening tables” comment. Is this because Access has a hard time handling the number of records (which I’m sure is true–one of the files in this database has over 8M records), OR are you saying this causes a performance degradation in the AS/400. Does querying the AS/400 cause any performance issues? My marching orders are that my solution should have little or preferrably no decernable performance degradation on the AS/400.

Randy

Reply | Quote

Randall

I connect to an AS/400 database through ODBC and use Access as a report writer. The table linking process is the same as other external table however care needs to be taken over opening tables due to the amount of records involved. What I tend to do is create a make table query of the records I require and then run the report from this local table.

Graham

Reply | Quote