Go Premium for a chance to win a PS4. Enter to Win

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
?
638 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

824 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