Reporting Team - MS Access Summary
Per the executive committee meeting on 8/1/00-here is a summary of my findings in getting to PeopleSoft data through MS Access.
To access People Soft(PS) data through an ODBC compliant reporting tool.
PS ODBC Driver
Create a PS Query selecting the result set you want. Then in MS Access create a SQL Specific Pass Through Query calling the query created in PS Query.
We were successful in running a PS Query in MS Access. It was determined that just the resulting query is returned in Access and not data stored in tables. This process does not extract data out of PS tables and load them into MS Access for further reporting as we initially thought. It simply is another method for running reports.
Security that the PS Query was created under is carried forward in Access. It was also determined, that the full PS Client needs to be installed on the desktop in order to perform this function. Also, we found that if the user has access to the PS database ie.HRPRD and has an Oracle ODBC driver installed, then they could access any of the tables in that database without security that was set up for that user in the application.