We are thinking about connecting to an Access database for one of our interfaces. Here is some PeopleCode that I threw together to make sure that it was possible.
I tested the code in a VMWare instance with HCM8.9/Tools 8.46, Windows Server 2003. The instance did not have Microsoft Access installed, and I changed the path (c:\temp\AccessTest.mdb below) to a network (UNC) path where the computer had Access installed.
Local JavaObject &class;
Local JavaObject &connection;
Local JavaObject &driverManager;
Local JavaObject &statement;
&class = GetJavaClass("java.lang.Class");
&class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
&driverManager = GetJavaClass("java.sql.DriverManager");
&connection = &driverManager.getConnection("jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=c:\temp\AccessTest.mdb;DriverID=22;READONLY=false");
&statement = &connection.createStatement();
&statement.executeUpdate("insert into tblTest(TestMessage) values ('testing')");
Hope it helps.
This is a very good post demonstrating how to use JDBC from PeopleCode. Since you are using a JDBC driver, you don’t actually have to have Microsft Access installed on the app server. You just need to have the JDBC driver in your classpath ($PS_HOME/class or %PS_HOME%class).
Another alternative is to use JDBC in a Custom Target Connector created using the Integration Broker SDK. A very handy target connector would be a JDBCTargetConnector. The advantage of using Integration Broker over a plain PeopleCode/Java solution is that it would allow you to send the contents of delivered PeopleSoft integration points to a non-PeopleSoft database, like a Microsoft Access database.