Solved

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

Posted on 2009-07-08
12
613 Views
Last Modified: 2013-12-06
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.
0
Comment
Question by:SteeleCo
  • 8
  • 4
12 Comments
 
LVL 35

Expert Comment

by:Gary Patterson
ID: 24808845
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
 

Author Comment

by:SteeleCo
ID: 24813027
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
 

Author Comment

by:SteeleCo
ID: 24813055
How would I remove the alias? Thanks.
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

Author Comment

by:SteeleCo
ID: 24813904
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
 
LVL 35

Expert Comment

by:Gary Patterson
ID: 24817025
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
 

Author Comment

by:SteeleCo
ID: 24817343
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
 

Author Comment

by:SteeleCo
ID: 24817408
Here are the DNS tabs:

A.jpg
B.jpg
C.jpg
D.jpg
E.jpg
F.jpg
G.jpg
H.jpg
0
 
LVL 35

Accepted Solution

by:
Gary Patterson earned 500 total points
ID: 24817613
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
 
LVL 35

Expert Comment

by:Gary Patterson
ID: 24817712
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
 

Author Comment

by:SteeleCo
ID: 24818242
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
 

Author Comment

by:SteeleCo
ID: 24818307
I created a view from one of the table and that is working fine.

Thanks for your help!
0
 

Author Closing Comment

by:SteeleCo
ID: 31601337
Thanks for all the help!
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Just about everyone has an old PC laying around.  Ask anyone in the IT industry, whether they are a professional or play in it as a hobby.  From outdated Desktops to cheap "throwaway" laptops, they are all around and not as hard to "fix up" as you m…
I. Introduction There's an interesting discussion going on now in an Experts Exchange Group — Attachments with no extension (http://www.experts-exchange.com/discussions/210281/Attachments-with-no-extension.html). This reminded me of questions tha…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

821 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