How to connect a native SharePoint list to AS400 table data?

You can connect native SharePoint lists directly with external data from AS/400 database tables or views using the Layer2 Business Data List Connector. You can set the connection string, the select statement and the primary keys (if any) in the SharePoint list settings dialog for bi-directional synchronization in background.

Take care, that the SharePoint Business Data List Connector (BDLC) feature is installed and enabled. The AS400 driver for OLEDB must be installed on your SharePoint server(s) - WFE and Application Servers. 

IBMDA400 is the OLEDB Driver provided by IBM System i Access for windows:

http://www-03.ibm.com/systems/i/software/access/windows/database.html

This is no free download, but licensed with AS/400 software. You can just download the service pack on web site. Use 32 bit driver for 32 bit SharePoint and 64 bit driver for 64 bit SharePoint (e.g. 2010 or higher).

Then follow this example:

Create your target list and follow the connect link in the list settings dialog:

Fig.: Simply click "Connect to external data source" to connect your SharePoint list.

In the configuration dialog, select the provider, enter the connection string and the SQL statement.

Fig.: BDLC Configuration dialog to enter the settings. Thats all you have to do.

Don't forget to set your primary key for advanced updates and write-back.

 Fig.: Your SharePoint list, directly connected to an AS400 data query with write-back (if enabled).

Fig.: The AS400 side. Updates in SharePoint are immediately available on AS400. 

Changes in AS400 are synchronized by a timer job, e.g. every hour or 15 mins. Modified data are trasferred only. So you can start notifications and workflows on data change in SharePoint. Or you can add additional fields, attachments...

READY TO GO NEXT STEPS?