PassThrough Query to AS400

[VegaLA]

Hi all.

I have been using PT queries from Access 2007 to SQL 2005 and it has been very helpfull, however, i'm now trying to do the same in order to pull some Data from an AS400 DB so that I can make the user's lives that much easier, however using the ODBC connection string below i'm not having any luck!

ODBC;Driver={iSeries Access ODBC Driver};Provider=IBMDA400;Data Source=MySystem;User Id=MyUser;Password=MyPassword;

I've googled various sites seeing if the syntax is wrong and although there are various versions this one is the closest i've got to a goal. Thing is when I run it it opens up an ODBC dialog box! Ideally i'd just like it to pull the data to a local access table using my credentials so they can go about generating their reports but without having to log into a DSN or AS400 loging box.

Is there a way of achieving this using a PT query?

Thanks in advance,
Vega...

 

[Pat Hartman]

Welcome aboard
Is there a reason that you don't want to link to the table? If you link the table, you won't need to run pass-through queries. You can use Access querydefs which you can bind to forms and reports.

If you insist on the pass-through approach, you can figure out the correct connection string by linking a table to the same data source and then opening the MSysObjects table and copying the connection string.

 

[ajvol01]

In the pass through query the ODBC Connect Str property needs to be set like below

ODBC;DSN=MyDSN;UID=MyUserID;PWD=MyPassword;