Linking problem with Z/OS DB2 tables in MS Access

I am trying to link host based (IBM DB2 V8) user tables in a MS Access DB (2003) which is running in a Citrix terminal server system (W2k3). When I try to link the table I want (VOIA06.tabname) I mark it and click the OK to link it. This is where the fun starts, Access starts linking tables from a different schema with different names. These have nothing to do with the one table I want. If my table ever comes up (or will come up) is unknown to me, I have to kill Access because it wants to link hundreds of other tables. I tried to recreate the problem on a local Access installation (same version Access and DB2 Connect ODBC software - V8 FP 12) and here I don't get this problem. One interesting item is that this problem seems to occur only on tables that the user has created himself using QMF on the host system. On 'normal tables' this error doesn't seem to occur.
DSchatAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

momi_sabagCommented:
Hi

maybe this is a bit far fetched but did you check that there is an actual table with that name ?
maybe it's a view and when access try to link to it, it also links to the base tables ?
0
Kent OlsenData Warehouse Architect / DBACommented:

Hi DSchat,

Are the table names that you're seeing really tables or perhaps they are associated aliases/nicknames?  It could be that DB2 is following the alias/nickname to the target table.

If not, check to make sure that you have the latest ODBC driver on your Windows server.  Simply uninstalling it and reinstalling it should be fine.


Good Luck,
Kent
0
DSchatAuthor Commented:
Hi,
The table actually exists, it is not a view. I've never tried to link a view, but what is happening isn't consistent with that theory, because Access is trying to link literally hundreds of tables which are all being listed.
The ODBC driver is DB2 Connect version 8 with FP 12. Not the newest but only six months old. As far as DB2 is concerned, I believe that the level is relatively actual. The terminal server where the software is running was just recently installed. I have the same software configuration on my local computer and there the problem does not occur. I can link the table I want.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

momi_sabagCommented:
Hi

that sounds like a bug or something
anyways, lets try a different approach
what happens if you create a new user and only give it select authorization on the table you want to link to, that is, it does not have any autorization on any other table
now connect with that user and try to link the table,
access should not be able to link to other tables the the user does not have select authorization to

what happens ?

momi
0
DSchatAuthor Commented:
Hi Momi,

not a bad idea, but I can't do it. I do not have the enough rights on our host system to try that. One other point along the same lines. This problem happens when I try to link the table with my sort of admin user and also several users have complained that they having it as well with their reduced rights. Theoretically the users are the creators of the problem tables and then the only authorized readers except for admins on the system.
0
DSchatAuthor Commented:
We found the error. I set the AttachableObjects key in the the Jet Engine section of the windows Registry to 'TABLES'. Apparently Access has a problem when the number of object too high is.

Please close question and refund the points.
0
Computer101Commented:
PAQed with points refunded (125)

Computer101
EE Admin
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
DB2

From novice to tech pro — start learning today.