How can I link to an iSeries file that has a period in it with Microsoft Access

I'm trying to link to some "tables" on an iSeries. But the name of the files are something.something.

So, I can connect to the library fine, see all of the files listed but it won't let me because of the period in the files. I can't change the names of the files but would really like to be able to link to them using Microsoft Access.

Any ideas? Thank you.
SteeleCoAsked:
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.

Gary PattersonVP Technology / Senior Consultant Commented:
If you mean that the file name, itself, contains an embedded "." character, then the easiest way to deal with this is to create aliases on the AS/400 for these tables with good names:

From STRSQL (green screen), Navigator's Run SQL Statements window, a QMQUERY, or any other native DB2 interface, use the SQL "CREATE ALIAS"  DDL statement to create an alias without the offending characters.

- Gary Patterson
0
SteeleCoAuthor Commented:
I created an alias for one of the files. I see it in Access when I try to link to it. It has something_something.

But, when it tries to link it still says it can't connect to something.something. It still see's the period.
0
SteeleCoAuthor Commented:
How would I remove the alias? Thanks.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

SteeleCoAuthor Commented:
I know I can use the DROP ALIAS but I was in the iSeries Navigator and I see the Alias listed under the ALIASES portion.

But, when I click DELETE it says: CONFIRM OBJECT DELETION.

I would assume that means JUST the alias but wanted to make sure that it wasn't deleting something else as well. :-)
0
Gary PattersonVP Technology / Senior Consultant Commented:
I just use DROP ALIAS, not the Navigator interface.  Just create a test file and alias if you want to be sure what happens through the Navigator interface.

Try creating a link to a table that doesn't have the "." in the name and see if that succeeds.

What do you mean by "but it won't let me" - do the dotted files not appear in the list?  Do you get an error if you select a dotted file, or what?

If the alias didn't work (it really should), CREATE a VIEW based on (select * from badtablename) and use the view for the link.  

BTW: I created a table named "TEST.THIS" and an alias called "TEST_THIS" and I was able to link to them both just fine using Access 2007.

If you like, post screen prints of the ODBC DSN that you are using (all pages), and a screen print of the specific error message(s) and where you are receiving it(them).  

Also, what version of the AS/400 ODBC driver are you using?

- Gary Patterson
0
SteeleCoAuthor Commented:
Gary,

I can connect to other tables just fine. It is just the ones I REALLY need to connect to that have a name structure of NAME.NAME that I can't connect to.

The version of the ODBC drive is version 11.00.00.00.

Attached is the error I'm getting.

ODBC.jpg
0
SteeleCoAuthor Commented:
Here are the DNS tabs:

A.jpg
B.jpg
C.jpg
D.jpg
E.jpg
F.jpg
G.jpg
H.jpg
0
Gary PattersonVP Technology / Senior Consultant Commented:
ODBC config looks fine.  

QS36F lib shouldn't be a problem, but I don't have S36 Environment to test with.  Possible this is creating an issue, but I don't think it likely.

My ODBC driver version on this machine is 11.00.05.00, are you running V5R4 without the latest service pack?  Might try applying the latest Client Access service pack for V5R4, or updating to a new release of the ODBC driver, if possible.  

I'm running MS Access 2007 (12.0.6423.1000) SP2 MSO (12.0.36425.1000) under Vista Business.  Apply Office SP2 if you haven't already.

If all else fails, create a view over the file and link to the view.

- Gary Patterson
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
Gary PattersonVP Technology / Senior Consultant Commented:
One other thought:

What method are you using to create your linked table in Access?  Are you using the External Data, ODBC Linked Table Wizard, or are you creating it in VB code?  If you are using code to create the linked table, please post the VB.  (Also try creating the link using the wizard and see if you have the same problem).

- Gary Patterson
0
SteeleCoAuthor Commented:
Gary,

I updated Access 2007 with SP2 and I also update my ODBC driver to 11.00.05.00 and I still received the same error.

I created the ODBC connection in ODBC Administration on my Windows Box.

I'll have to try and create a view and see how that goes.
0
SteeleCoAuthor Commented:
I created a view from one of the table and that is working fine.

Thanks for your help!
0
SteeleCoAuthor Commented:
Thanks for all the help!
0
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
Operating Systems

From novice to tech pro — start learning today.