Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2009-07-08
12
Medium Priority
?
631 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

670 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