Table created in AS400 DB2 library is invible to ODBC

Posted on 2013-09-11
Medium Priority
Last Modified: 2013-09-16

I have a very strange problem happening with Access (2007), the ODBC iDriver for AS400 and a library in an AS400 DB.

I have various existing Access applications that link over the iDriver to tables in an AS400 DB in different libraries. 2 of these tables have even been created especially for my applications. So far, so good, no problems whatsoever, everything works fine.

I now needed yet another new table to store data on the AS400. So my colleague, who alreay created the 2 tables I am using every day, has created a new table in the same library as he created the ones that I already use (QGPL).

Well, when I try to link that new table, regardless of how I do it, that table is NOT in the table list of that library !

And I can use the original DSN, or delete the existing one and create a new one, or create a file DSN instead of a user DSN, or even create a new Access DB and try to link, log off and log in again... NO way, the (new) table remains invisible !!!

My colleague looked at everything, giving the same "attributes" to the new table as the ones of the tables I use. Didn't change anything. He even created yet another table, totally public. I can't see that one either !

Another strange thing is that I can use a connection string to establish a connection directly to that table, I can connect, I cann issue SQL statements (DELETE, INSERT), I don't get ANY errors, but nothing happens, i.e. I cannot store data in that table !

Any idea what this could be due to ?

Thanks for help
Question by:bthouin
LVL 18

Assisted Solution

by:Dave Ford
Dave Ford earned 450 total points
ID: 39483539
Just for testing, try hard-coding the library (a.k.a. schema) in your SQL.

Depending on which naming convention you're using (*SQL or *SYS), you'd use a dot or a slash to separate the schema-name from the table-name.

So, instead of this:

select someStuff
  from MyTable

Open in new window

You’d use this:

select someStuff
  from MySchema.MyTable

Open in new window

(assuming you’re using *SQL naming convention)

Of course, if you’re using *SYS naming convention, you’d replace the dot with a slash:

select someStuff
  from MySchema/MyTable

Open in new window

LVL 17

Accepted Solution

Murphey earned 600 total points
ID: 39489392
Did you check your authorisation on this new table
LVL 27

Assisted Solution

tliotta earned 450 total points
ID: 39489934
Please show us the 'File Description Header' section from the output of a DSPFD command for that table. It's the first segment of output that shows some attributes of the table. We need to know actual details of the file rather than descriptions of how it was created.

After seeing those, there will probably be other questions.


Author Closing Comment

ID: 39497440
Don't know who had the best idea, but in any case I was very happy to see that 3 people has a serious suggestion about the problem. This indeed shows that ExpertsExchange has the experts !

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
What to do if a split doesn't fit? Or a bunch of invoice lines must be rounded while the sum must match a total? It takes a little, but - when done - it is extremely easy to implement.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question