Solved

Create Iseries ODBC connection to strange formatted library name.

Posted on 2014-04-23
10
820 Views
Last Modified: 2014-04-24
Hi. I am trying to create a new DSN over an ISERIES library called G.USRLIB and link an object in that library to an access database.  I can create the DSN ok and see the library objects listed when I try to import into Access but when I select the file object, the link fails with the error "The Microsoft Access Database engine could not find the object 'G.USRLIB.FILENAME'.  Make sure the object exists and that you spell its name and the path correctly.  If G.USRLIB.FILENAME' is not a local object, check your network connection or contact the server administrator.

I believe this has to do with the "G." precursor but I'm wondering if there is some way to pull this data in directly from that library/file.  I'd rather not put some update system in place to copy data to a more readable naming convention but that is a last ditch option..

thanks
0
Comment
Question by:valmatic
  • 5
  • 4
10 Comments
 
LVL 34

Expert Comment

by:Gary Patterson
ID: 40018201
That dot in the lib name is a problem.  

A few ideas:

1) Use SQL create an alias (CREATE ALIAS) to the file in a different library that doesn't have an embedded dot in the lib name.

2) What naming convention are you using in your DSN?  *SYS or *SQL?  *SYS requires that you use a slash: G.USERLIB/TABLENAME.  That might resolve the problem for you.  *SQL requires a dot, and is going to be confused by the "G."  If you are allowing Access to compose the fully-qualified table name for you, it will probably only handle *SQL - so you'll probably need to use the alias technique.  If you are writing VBA code, you can use *SYS, which is more likely to let you get away with the dot in the lib name.

3) Try *SQL naming convention setting the Default SQL Schema or Library on the ODBC Setup Server tab to G.USERLIB, and then use an unqualified reference to the table in your Access code.  Just TABLENAME by itself.  With *SQL naming, the system will automatically search the default schema.lib whenever given an unqualified table name.  *SQL doesn't use the library list.

4) Try *SYS naming convention setting the first library in the Library List (ODBC Setup - Server tab) to G.USERLIB, then use an unqualified reference to the table in your Access code.  *SYS doesn't use the Default SQL Schema/Lib.

If you can't get it worked out, post back and explain exactly how you are accessing the table so I can recreate the problem on my system.
0
 
LVL 7

Author Comment

by:valmatic
ID: 40018468
Hi Gary,  I didn't even think about linking the data in VBA.  I have no idea where to even begin there.  I'm just using the external database tool in Access to pick up my DSN.  I linked the screens through to the error.  The DSN itself is pretty basic.  I'm using *SQL naming and I've attached screens for my DSN build too.  ODBCProblem.pdfODBCProblemDSN.pdf

Any screens I did not include in the DSN build I left as default.

thanks
0
 
LVL 34

Accepted Solution

by:
Gary Patterson earned 500 total points
ID: 40018612
With VBA in Access, you can make *SYS naming work.  but just using the Access GUI you're probably stuck with *SQL naming.

Best way to make this work is the alias technique:

Create a lib with no dot in it.  GUSRLIB for example.

Use green-screen STRSQL command and CREATE ALIAS GUSRLIB/FILENAME ON G.USRLIB/FILENAME

Use GUSRLIB as your default schema in your DSN setup.
0
 
LVL 7

Author Comment

by:valmatic
ID: 40018654
Hi.  I've not done too much with SQL on the ISeries yet.   Can I just enter that statement as one line in STRSQL?  Examples online show as two separate lines and use the word FOR in place of ON.  

Now that I have a table linked back to my ISeries data, do i need to refresh this data manually or if Iseries data is updated, will users see change automatically?

If I create a System DSN,  do I need to create the DSN on each client PC?  How will users see linked tables otherwise?

thanks
0
 
LVL 27

Expert Comment

by:tliotta
ID: 40020189
You might also simply try using "G.USRLIB" (use double-quote marks).

Tom
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 7

Author Comment

by:valmatic
ID: 40020350
Tom,  Do you mean in the DSN build under Server - SQL Schema?

I tried that and no luck.
0
 
LVL 34

Expert Comment

by:Gary Patterson
ID: 40020700
Can I just enter that statement as one line in STRSQL?  Examples online show as two separate lines and use the word FOR in place of ON.  

Number of lines doesn't matter.  Use correct syntax, that was just a quick and dirty example.

Now that I have a table linked back to my ISeries data, do i need to refresh this data manually or if Iseries data is updated, will users see change automatically?

A linked table in Access is just that - a link to the iSeries table.  Not a local copy.   Generally each type you execute a query against a linked table, the request is sent to the iSeries host and a new result set us sent to Access.

If I create a System DSN,  do I need to create the DSN on each client PC?  How will users see linked tables otherwise?

A system DSN is specific to the one system where it is created.  If you plan to use static DSNs this way, you'll have to create them on every client where they will be needed.  you can script the creation of system DSN entries.

Creating static system DSNs is not a good practice.  Not only do you have to deal with creating/updating the DSNs on each machine (which may require privileges your users don't have in a lot of shops), also if something changes later, you've got to go out and update all those DSNs.  You also have to deal with new users and new or reloaded equipment, etc.  

A better way:  DSN-less connections.  The example is for SQL Server, but the same basic process applies to DB2.

http://support.microsoft.com/kb/892490

Access is not a great tool for giving PC users access to DB2 data - at least not outside of an Access application that follows specific rules and prevents users from performing unauthorized actions.

For example, be careful about allowing anything but read-only access - I've had to clean up a lot of messes because someone opened a connection with read/write access and the user updated/deleted/cleared data in the file without going through the correct program interfaces.  I've seen people in your position get fired when that happened.

Even if you set up a read-only DSN, it is still dangerous, since some users may have profiles that have rights to update iSeries DB2 tables so that they can run iSeries line-of-business programs.  It just takes one power user that knows how to create a DSN (or can follow your example) to create a DSN that allows read/write access, and you have a potential data disaster on your hands.

Make sure you have a complete handle on the huge exposure you are creating when you open up the door to you database with tools like this.  The minute an ODBC/JDBC/OLEDB/ADO.NET driver is installed on any end user system, you've opened up your system to a world of potential problems.

The more sophisticated shops I work with strictly limit incoming ODBC connection requests, and only allow authorized applications and/or users to connect this way - due to the risks of unauthorized access to sensitive data, increased risk of accidental data corruption, the possibility of locking issues interfering with line-of-business applications, and other similar concerns.  

If you don't understand all of the ramifications, you may want to bring in an iSeries consultant who does to help you design a safe, effective mechanism for accessing iSeries DB2 data.  

It isn't a trivial task.
0
 
LVL 7

Author Comment

by:valmatic
ID: 40020947
Hi  Gary,  Thanks for that info.  I was under the impression I'd be safe if DSNs were all read only but yeah,  users could fiddle with the settings or start creating their own once installed on their PCs.  It sounds like the DSN-Less approach would be a more secure option then?
0
 
LVL 34

Expert Comment

by:Gary Patterson
ID: 40021105
DSN-less is generally better.  Not particularly more secure.  DB2 ODBC security is a pretty big topic.
0
 
LVL 7

Author Closing Comment

by:valmatic
ID: 40021386
thanks Gary.  Very helpful...
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

707 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now