[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 640
  • Last Modified:

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.
0
SteeleCo
Asked:
SteeleCo
  • 8
  • 4
1 Solution
 
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 8
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now