Creating an AS/400 passthrough query in MS Access

Passthrough queries can be efficient for AS/400 ODBC connections because they let you bypass the JET engine and take advantage of the AS/400's query processor.

To create a passthrough query in Access, open the Queries windows and then double-click the "Create query in design view" icon. Close the New Tables dialog box that automatically appears. Then, on the Query menu, select SQL Specific and then Pass-Through option. Next, set the ODBC connection string by selecting the Properties icon in the toolbar and then clicking on the ellipsis next to the ODBC Connect Str property. You're then prompted to select an existing data source to the AS/400, or you can create a new data source if an AS/400 data source doesn't already exist. After setting the connection properties, type your passthrough query in the SQL Pass-Through Query window and click the Execute icon to run the query

Typical settings for MS OLE DB Provider for IBM DB2

Example settings for configuring MS OLE DB provider for DB2 for as400 iseries

Affiliate Application
Alternate TP Name
APPC Local LU Alias
APPC Mode Name              QPCSUPP
APPC Remote LU Alias
APPC Security Type          Program
Auth Encrypt    False
Cache Authentication        False
Client Application Name 
Connection Pooling          False
Data Source                 myas400.mydomain.com
DateTime As Char            False
DateTime as Date            False
DBMS Platform               DB2/AS400
Default Qualifier
Default Schema              MYDATA (note, this is the library name containing the files to access on as400)
Defer Prepare               False
Derive Parameters           False
Extended Properties  
Host CCSID                  37
Initial Catalog             MY400
Integrated Security
Mode                        ReadWrite
Network Address             myipaddress
Network Port                446
Network Transport Library   TCPIP
New Password
Package Collection          SQLPKG (library to contain the packages created for pulling/pushing data from/to this box)
PC Code Page                1252
Persist Security Info       False
Principle Name
Process Binary as Character false
Rowset Cache Size           0
Units of Work               RUW
Use Early Metadata          False
Locale Identifier           1033
Mode                        3
Protection Level            1
Connection Pooling          false

More as400 settings to follow

as400 database connection strings

Connection string examples when connecting to an as400, iseries, system i, os400, i5os

as400 JDBC connection string

jdbc:as400://mySystem;user=myUid;password=myPwd;

IBM DB2 for i .NET data provider

DataSource=myServerAddress;UserID=myUsername;Password=myPassword; DataCompression=True;

IBM Client Access OLE DB provider

Provider=IBMDA400;Data Source=MY_SYSTEM_NAME;User Id=myUsername;Password=myPassword;

.NET Framework Data Provider for OLE DB

Provider=IBMDA400;Data Source=MY_SYSTEM_NAME;User Id=myUsername;Password=myPassword;

Client Access ODBC driver

Driver={Client Access ODBC Driver (32-bit)};System=my_system_name;Uid=myUsername;Pwd=myPassword

iSeries Access ODBC driver

Driver={iSeries Access ODBC Driver};System=my_system_name;Uid=myUsername;Pwd=myPassword;

.NET Framework Data Provider for ODBC

Driver={Client Access ODBC Driver (32-bit)};System=my_system_name;Uid=myUsername;Pwd=myPassword;

More as400 database connection strings to follow