Table created in AS400 DB2 library is invible to ODBC


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
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.

Dave FordSoftware Developer / Database AdministratorCommented:
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

MurpheyApplication ConsultantCommented:
Did you check your authorisation on this new table

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
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.

bthouinAuthor Commented:
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 !
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

From novice to tech pro — start learning today.