Linked servers

  • Thread starter Thread starter DrEvil
  • Start date Start date
D

DrEvil

Good morning,
I'm looking for some direction in creating new Ms Access database that would
have linked tables to MSSQL 2005 ent. and in addition would be able to take
advantage of DB2 database that is linked in MSSQL. What I would like to do is
look up some values in linked DB2 tables while user is creating record in
MSSQL to validate it and then at the end of the batch(bunch of related
records) I would "push" that batch to temporary processing table on Mainframe
where DB2 folks would do something with these batches/records and send me a
response back. I have created linked server to DB2 using IBM OLE DB provider
for DB2 (IBMDADB2) and that is working, what I'm not sure of doing is linking
DB2 tables in MS Access DB.
I'm trying to all this in order to avoid installing DB2 Connect software on
each machine and configuring it on each machine that would need to use this
database. I would rather that server that has MSSQL talks to Mainframe
instead each individual computer.
I hope I explained it well... also if MS Access wouldn't be able to do this
please let me know if VB would.

Thanks

Amir
 
I have created linked server to DB2 using IBM OLE DB provider
for DB2 (IBMDADB2) and that is working, what I'm not sure of doing is linking
DB2 tables in MS Access DB.
Click to expand...

Have you tried linking to the linked-DB2 tables in SQL Server? I
don't think Access would know the difference.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com
 
Morning,
Thanks for replying Armen.
I'm not sure how to set-up ODBC connection to MSSQL 2005 ent. to show linked
server tables... I cannot select it from Default Database option under MSSQL
Native driver, maybe using Attach Database filename option can be utilized
for this?
Once I can get those DB2 tables to link in MS Access then its downhill from
there.

Thanks

Amir
 
I'm not sure how to set-up ODBC connection to MSSQL 2005 ent. to show linked
server tables
Click to expand...

So you're saying you can link to normal SQL tables from Access, but
you can't see the linked SQL tables in the same database? I don't
understand. Maybe someone else can clarify why this wouldn't work.

Another approach is to build SQL views on the remote DB2 tables and
link to those from Access instead...

Armen Stein
Microsoft Access MVP
www.JStreetTech.com
 
You are correct, I cannot seem to find DB2 linked tables in the list. I was
going to try passthrough query, maybe that would work.

I'll take a look at the SQL views also.

Thanks


Amir
 
I got this done using pass-through query, I'm now able to retrieve data from
DB2 and speed seems good. My pass-through query looks like this:

SELECT *
FROM OPENQUERY(TSTESA5, 'SELECT * FROM DON1.ETAX_W2')
where W2_FIDN = '68052889600'

TSTESA5 is the linked server name
ETAX_W2 is the table
W2_FIDN is column


Amir
 
Back
Top