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

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

Create Iseries ODBC connection to strange formatted library name.

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
valmatic
Asked:
valmatic
  • 5
  • 4
1 Solution
 
Gary PattersonVP Technology / Senior Consultant Commented:
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
 
valmaticAuthor Commented:
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
 
Gary PattersonVP Technology / Senior Consultant Commented:
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
Industry Leaders: 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!

 
valmaticAuthor Commented:
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
 
tliottaCommented:
You might also simply try using "G.USRLIB" (use double-quote marks).

Tom
0
 
valmaticAuthor Commented:
Tom,  Do you mean in the DSN build under Server - SQL Schema?

I tried that and no luck.
0
 
Gary PattersonVP Technology / Senior Consultant Commented:
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
 
valmaticAuthor Commented:
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
 
Gary PattersonVP Technology / Senior Consultant Commented:
DSN-less is generally better.  Not particularly more secure.  DB2 ODBC security is a pretty big topic.
0
 
valmaticAuthor Commented:
thanks Gary.  Very helpful...
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

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