1 / 42

Client Access Express Features: MS Access Integration with iSeries Data

Client Access Express Features: MS Access Integration with iSeries Data. Sign on the AS400 and type crtlib DT followed by your userid. Type addlible DT youruserid. Type CPYF press F4 and complete the parameters as below then press Enter. File is CSCSTP, Library RPGLEDATA.

jillian-bernard
Download Presentation

Client Access Express Features: MS Access Integration with iSeries Data

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Client Access Express Features:MS Access Integration with iSeries Data

  2. Sign on the AS400 and type crtlib DT followed by your userid.

  3. Type addlible DTyouruserid

  4. Type CPYF press F4 and complete the parameters as below then press Enter. File is CSCSTP, Library RPGLEDATA Use your new library name *YES

  5. ODBC • Open Database Connectivity • Data-access standard created by Microsoft • Several layers: • Application • Driver manager: manages the individual ODBC drivers installed on your system • ODBC driver: programs that perform the translation between the external data source and the ODBC programming interface; offer remote database access for ODBC compliant-application programs • Data sources: DB2/400, Oracle, SQL Server

  6. Client Access Components • ODBC: Part of the base support of Client Access, a primary method used by MS Office apps to retrieve data from external data sources Two ways to get to the ODBC Administration: Control Panel Client Access Express • Data Transfer Function: can be automated

  7. Access the ODBC Administration via the Control Panel:Click Performance and Maintenance

  8. Click Administrative Tools

  9. Double Click Data Sources ODBC

  10. Note the Client Access ODBC

  11. Data sources: named connections to a database • DSN refers to a named data source. • Three different types of data source names, each allowing different access • User DSNs: available to the current user & other users with authorized access • System DSNs: available to the OS and anyone using the system • File DSNs: available to anyone using the system with the proper drivers installed

  12. One-time step to allow access to system using a Client Access ODBC Driver - - - already done!

  13. Second easier path to ODBC Administration:

  14. Create a data source that points to the AS/400 database, click User DSN tab is it is not already selected. Click the Add button.

  15. Select the Client Access ODBC Driver (32-bit) and click Finish

  16. Give the data source the name DevTrends then Connection Options

  17. Note the various choices, click OK

  18. Once back at Client Access Express, click OK

  19. Click the new DevTrends DSN, click Configure

  20. Click Server, type the name of the new in the Library List then click Advanced

  21. Change the commit mode to…

  22. Commit immediate (*NONE), click OK

  23. Live data vs. transfer data • Is online up-to-date information required? • Is the data too big to exist on the PC? • Data transfer may not provide the needed functionality • Use Data Transfer Function if: • Offline data access is required or desired • No ODBC client utilities are available to perform the transfer • ODBC security is a concern

  24. Lab : Offline & Online data using MS Access

  25. Create a new Access database called SampleDB

  26. Click File, Get External Data, Import, Files of type ODBC Databases

  27. Click Machine Data Source, Dev Trends, and OK Sign on the AS/400

  28. Select the table prefixed with your new DT library and the file name CSCSTP, click OK.

  29. Note the new table imported, double click to view in datasheet view

  30. Online data using MS Access

  31. Using the same Access database,click File, Get External Data, Link Tables

  32. Change the File of type to ODBC

  33. Click Machine Data Source, DevTrends, OK

  34. Sign on the AS400

  35. Select the DTuserid.CSCSTP and click OK.

  36. Note that a primary key is required. Click CUSTNO then OK

  37. Double click the linked table and sign on the AS400

  38. Note the Live Data

  39. Change the name of the first record CCITY and CSTATE and close the table to save

  40. Open the table again, and verify that the changes were saved.

  41. Note if the ODBC update fails It is because the Advanced Server settings were not changed.

  42. Summary: ODBC can be used to provide access to AS400 data from other applications. Decisions must be made as to whether the ODBC connection should allow updates or should be read-only. Decisions must be made as to whether the data is imported (pull type of feed) or if it is to remain on the AS400 and accessed from the application ‘live’. Both import and linked tables were created in this lab exercise.

More Related