Create Iseries ODBC connection to strange formatted library name.

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.

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

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

You might also simply try using "G.USRLIB" (use double-quote marks).

Tom

Tom,  Do you mean in the DSN build under Server - SQL Schema?

I tried that and no luck.

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.

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?

DSN-less is generally better.  Not particularly more secure.  DB2 ODBC security is a pretty big topic.

thanks Gary.  Very helpful...