DB2 and MS Access 97 - ODBC help

[dschomburg]

I have no exp. with DB2 and I have been given the task to retrieve data.
I think I have everything set up correctly. When I try to use MS Access ODBC link wizard, I can see all the tables available but when I actualy try to link to a table or import it I get an "ODBC--Call Failed" error and then a "The application stat is in error" "a database connection does not exist SQLSTATE=51002" error

Has anyone had to deal with this?

Any help would help a heap.
Thanks,
Dave

 

[MarcLodge]

Dave,
I had to do this a couple of years ago and had problems, but managed to get it working eventually. Check that the ODBC drivers that you are using are correct. Do this by trying to use them from another application. I seem to recall I used Excel (!) to do this. If all is well with the driver, make sure that the ODBC parameters that you are using from within Access are correct.

Hope this helps, come back to us if it doesn't

Marc

 

[blom0344]

Hello Dave,

For me ,the best set-up was to install part of the runtime-client for DB2,in this case the client configuration assistant and ODBC components. The assistant lets you set up an System connection quite easily. I tuned the connection for connection to Access with automatic log-on in the ODBC settings. In Access I link the DB2 tables I need. I have several Access applications running stabily with this set-up. (Connection is not stable without the automatic log-on to the DB2 database, still ghave to find out why)

If you need a detailed guideline, you can reach me through my mail-account..... T. Blom
Information analyst
tbl@shimano-eu.com

 

[dschomburg]

Wow, great ideas! Thanks for your suggestions, I will get on them right away.
I'll try using Excel first and then double check my config. I did not do the install but I do have the disk so I may have to reinstall.

May I ask which version of Access you are all using? I forgot to mention that I'm on 97 with a windows 2000 machine. All other Office apps are 2000.

Thanks again,
Dave

 

[blom0344]

Hello Dave,

Both Access'97 on Windows 98 and Access'97 on Windows2000. T. Blom
Information analyst
tbl@shimano-eu.com

 

[MarcLodge]

Similar scenario for me Dave, Access 97 and 2000. One question begs.... what DB2 system are you connecting to? Reason I ask is that I was connecting to a mainframe and didn't really have the tools that my T.Blom has. When I say 'really' it's because we may have had them at the installation, but there usage was controlled by some body else.
Marc

 

[blom0344]

hello Dave,

To further expand on connections, I use either Client Access Express or StarSQL ODBC components to connect to DB2 on AS400, with preference for Client Access Express (which has nothing to do with MS Access by the way........) T. Blom
Information analyst
tbl@shimano-eu.com

 

[dschomburg]

Hello all,
I'm (not yet) connecting to an AS390. It's very strange. I can connect and "see" the tables but when I try to link or import one, I get the above errors.
Excel just hangs.
I'm wondering if my access rights are not configured for my purpose?

 

[blom0344]

Hello Dave,

What middleware are you using to set up the connection to AS400 (are you connection to AS400 / OS390 ?)
MERANT,INTERSOLV, native DB2 drivers .........? T. Blom
Information analyst
tbl@shimano-eu.com

 

[dschomburg]

I really apprciate all your help with this.

um...now that I double check, it is OS390.
I had to install DB2 Universal Database Version 7 to get the ODBC drivers. I think it's going through DB2 Client Configuration Assistant.

If that tells you anything. I wish I could communicate better but like I said, I have never worked with DB2 and don't have a very good grasp. All I know is, there is data, and I need it.
So I appologize for making you scratch your head and saying "what is this guy doing?"

 

[MarcLodge]

I have a vague memory that Excel does something daft like trys to get one of the huge sysibm tables, by default. I'm wondering whether it might be authority rights because you can see a list of the tables (which to me impplies that you have a valid connection) but cannot get at the data. Are you specifying a userid, and does this id have the correct authority?
Marc

 

[dschomburg]

Ah! Therein lies the question.
I'm not sure what type of authority rights I have. Would they be the same as the typical "read" "write" or is there some sort special authority in the IBM world?

 

[MarcLodge]

Hi again Dave,
When I have used ODBC drivers to access a mainframe (OS390)I have had to type in a userid and a password. That userid either had it's own authority or it was connected to a group that had the relevant authority. I have a feeling that blom0344 might have more info on this, as my knowledge of this area is pretty sketchy.
Oh, and by the way, don't worry about asking seemingly head scratching questions, it's just good from my point of view because it makes me think! (and that doesn't happen often )
Marc

 

[blom0344]

Alas,

I have experience with AS400 and UDB DB2, but not with mainframes. My IBM oriented collegue told me that authorization structure in OS/390 is quite a different piece of cake..........

perhaps the following link (guess) is:

http://www.idug.org

for lots of other DB2 experts. T. Blom
Information analyst
tbl@shimano-eu.com

 

[MarcLodge]

Dave,
Do you type in a userid and password when going through ODBC? If you do, can you, using the same userid and password, go into the mainframe, go into SPUFI or QMF and access the tables that you are after?

I suspect that you may need to talk to your DBA's in order to get this cleared up.

Marc