adp files

[Kenln]

Can an ADP database connect to both MSSQL and AS-400 (iSeries) at the same time?

Currently I am using (trying to) connect to MSSQL via linked tables and the AS-400 via pass-through queries.

Thank you,

Ken

 

[LPurvis]

You'll not get passthroughs in an ADP.
You could look to create a linked server from the SQL Server to your AS-400, driver depending.
Or if the data doesn't need to be joined per se and you're only performing disjointed lookups - you're free to make code lookups (you'll need such drivers on the client machine in that case) fetching data back into recordsets.

 

[Kenln]

Sounds promising...

How could I create a linked server from MSSQL? That would be great and solve additional security issues.

 

[LPurvis]

Hi - sorry for the delay, lost track of this one... remembered about it just now.
Umm so yes, linked servers. You can create them easily (i.e. graphically) in Enterprise Manager / Management Studio.
Or with the sp_addlinkedserver stored procedure.
You'll need the OLEDB connection credentials as you'd normally require for a connection string.
As always - Books Online is your friend here. Details a plenty lie therein.

 

[nIGHTmAYOR]

If you have "AS-400 Client Access" installed on your system you can easily create an odbc connection from control pannel and then link tables out of it as easily as done with SQL Server.

 

[nIGHTmAYOR]

PS you can make as many links to as many odbcs connections setuped on your system via simultainiously via access (tried and tested)

 

[LPurvis]

In an ADP? :-s

 

[nIGHTmAYOR]

Last time i experimented with adp it never restricted me from using linking to any sorts of odbcs yes , i find the thought odd that adp is restricted to microsoft sql server only .

 

[LPurvis]

AFAIK tables "linked" in an ADP are using either a Linked Server or an ah hoc connection (just as on the SQL Server you can use OpenRowsource instead of creating a linked table). But both would be using an OLEDB connection. (Whether or not it's wrapping an ODBC provider or not).
How well that wrapping works remains to be seen - but (again, AFAIK as I don't claim to be an authority on ADPs) it's the server doing the linking, not Access per se (as Jet isn't present to create them).

Any permission restrictions on the server preventing ad hoc connections would mean that a linked server would have to be the method of choice.

 

[nIGHTmAYOR]

Thank you for noteing this yet the user question was if it's possiable and the answer i believe is "Yes" if you have "AS-400 Client Access" installed and a valied odbc to it is running

 

[LPurvis]

Indeed - but only by effectively using a Linked Server - as I'd already stated.

 

[Kenln]

I have and am using Access with Pass-Though to AS-400 (ODBC Client Access) and Linked Tables to MSSQL. Prior to that I used linked-tables to AS-400 (ODBC Client Access).

Linking tables directly to AS-400 caused PK problems. The AS-400 had more PKs (in a given table) than Access could handle. Hence the Pass-Through.

However, any ODBC connections exposes (security) more of the AS-400 than I wish. Any ODBC connection (and driver) can be used in Excel or any other ODBC compliant program. After reading Leigh Purvis' original response I was thinking that an easier and more secure method might be linked-server.

Not my original direction but could be a good solution.