Writing to Access Databases

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.

Resources

Advertisements

One Response to “Writing to Access Databases”

  1. Jim Marion Says:

    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.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: