Hi
I have been asked to link the data in my SQL database to the data inputted into an AS400 database. I am totally clueless as to the table structure but figure it cannot be that difficult.
My SQL database has an Access front end. That means that all I need to do is link the tables and change the field names to what they currently are in my existing tables so that I don't have to redo all the queries.
I just need to know that this is possible and what I will need to do in order to make this happen.
Anyone able to assist?
Link SQL tables to AS400 tables
- rustypup
- Registered User
- Posts: 8872
- Joined: 13 Dec 2004, 02:00
- Location: nullus pixius demonica
- Contact:
Re: Link SQL tables to AS400 tables
if you're looking to pull data from both sources in the same query, i'm smelling linked server happiness with its concomitant security credential issues...
i haven't touched AS/400 for some time now, but i suspect you will need to upgrade the client access install on the SQL server to whatever makes the AS/400 happiest, then create an ODBC link on the SQL box pointing to the AS/400 db,then link using OLE for ODBC...
at a guess...
thereafter any queries to the AS/400 data would be prefixed with the link reference: "MYLINKREF".MyCatalog..MyTable...
or hunt around for references to linking SQL to AS/400...
i haven't touched AS/400 for some time now, but i suspect you will need to upgrade the client access install on the SQL server to whatever makes the AS/400 happiest, then create an ODBC link on the SQL box pointing to the AS/400 db,then link using OLE for ODBC...
at a guess...
thereafter any queries to the AS/400 data would be prefixed with the link reference: "MYLINKREF".MyCatalog..MyTable...
or hunt around for references to linking SQL to AS/400...
Most people would sooner die than think; in fact, they do so - Bertrand Russel
- Tribble
- Registered User
- Posts: 88465
- Joined: 08 Feb 2007, 02:00
- Processor: Intel Core i7-4770K CPU@3.50GHz
- Motherboard: ACPI x64-based PC
- Graphics card: GeForce GTX 780 Ti
- Memory: 16GB
- Location: Not here
- Contact:
Re: Link SQL tables to AS400 tables
Thanks Puppi - I am doing that now. The alternative would be for them to run a query and send me the data that I need - but that is too messy in my mind. They would then have to click buttons in my app for the data to come through. I want this to be as easy for them as possible as it seems my interim program is going to be around for a while.
-
- Registered User
- Posts: 20576
- Joined: 19 Sep 2003, 02:00
- Location: Bloodbank
Re: Link SQL tables to AS400 tables
Jip Link Server is the best way to go. Bear in mind that it could cause one huge performance knock. Especially since you are not linking a SQL server which the link server stuff was designed for.
- Spoiler (show)
- Tribble
- Registered User
- Posts: 88465
- Joined: 08 Feb 2007, 02:00
- Processor: Intel Core i7-4770K CPU@3.50GHz
- Motherboard: ACPI x64-based PC
- Graphics card: GeForce GTX 780 Ti
- Memory: 16GB
- Location: Not here
- Contact:
Re: Link SQL tables to AS400 tables
Mmmm am I going about this the wrong way then?