ODBC link to iSeries tables - Index problem

[Daniel Carollo]

Good morning everyone!

I have the following situation in an application:

For security reasons, linked tables are created at startup of the
application (tabledef is created in the Access DB and then linked following
something similar to what's described in article
http://msdn.microsoft.com/archive/d...nnectionStringParametersinMicrosoftAccess.asp :
Set dbsLocal = OpenDatabase("DB1.mdb")
Set tdfLink = dbsLocal.CreateTableDef("AuthorsTable")
tdfLink.Connect = "ODBC;DATABASE=pubs;UID=sa;PWD=;DSN=Publishers"
tdfLink.SourceTableName = "authors"
dbsLocal.TableDefs.Append tdfLink

In Access 2000 on NT4, this worked fine, and the index fields defined in the
DB2 tables were reflected in Access.
We are now migrating to Access 2002 on Windows XP, and the index aren't seen
by Access.
This has two major impacts: performance impact, and update queries that used
one of the DB2 tables (even for updates to local Access data) return the
error "Must use updateable query".

If I link the tables "by hand" (using the wizard), I get prompted to define
the index in XP (but not in 2000/NT).

I found a temporary workaround by creating a local index (executing a DDL
query "CREATE INDEX...") but that's not satisfying (several hundred
databases to modify, with several tables per DB).

My question can be summarised as: is there a setting somewhere to force
Access 2002 to recognise the index on DB2 automatically? I haven't been able
to isolate where the failure occurs: in my mind it could be Access or the
iSeries drivers.

Best regards to all, thanks for reading so far, and thanks in advance for
any tips.

Daniel

Computing Technologies International - www.computing-tech.com - We
provide solutions...

 

[Rick Brandt]

I have the following situation in an application:

For security reasons, linked tables are created at startup of the
application [snip]

In Access 2000 on NT4, this worked fine, and the index fields defined in the
DB2 tables were reflected in Access.
We are now migrating to Access 2002 on Windows XP, and the index aren't seen
by Access.[snip]

If I link the tables "by hand" (using the wizard), I get prompted to define
the index in XP (but not in 2000/NT).[snip]

I will definitely need to test to see if I can duplicate your experience. AFAIK
Access should pick up the PK of any ODBC linked table and in my experience
(Access 97) it has always picked up all of the indexes as well. I even have a
few cases where I am forced to link to logical files on our ISeries because the
Physical file has more than 32 indexes on it and Access won't let me link to
those.

I have Access 2000 files that are "using" links to our ISeries under Windows
2003 Server, but those links were created in Access 97 and then the file
converted for distribution to users with A2K installed. We definitely have
power users linking to our ISeries with Access 2002 and Access 2003 under WinXP,
but they are seldom attempting to do edits so perhaps this has been occurring to
them and I just haven't heard about it.

I will attempt to test and post back.

 

[Daniel Carollo]

Hello Rick!

Thanks for looking into it.

If I remember correctly, we're using version 5.2 of iSeries, in case that
matters (it probably does).

 

[Rick Brandt]

Hello Rick!

Thanks for looking into it.

If I remember correctly, we're using version 5.2 of iSeries, in case that
matters (it probably does).

I just remoted into my office PC (Windows XP) and created links to our ISeries
using Access 2002 (XP) and Access 2003. In both cases I did so using both a
file in Access 2000 format and in the 2002/2003 format. All of the links picked
up all of the indexes.

I did not test creating the links in code, but I was not prompted to create
unique local indexes when creating the links and upon opening them in design
view all of the indexes on the server are shown.

 

[Daniel Carollo]

Hello Rick!

Thanks for checking!

I guess the answer is here:

I did not test creating the links in code, but I was not prompted to create
unique local indexes when creating the links and upon opening them in design
view all of the indexes on the server are shown.

I need to find out what in my environment prevents Access 2002 on XP from
seeing the indexes. It might be an option in the iSeries ODBC drivers, but
where do I find out? My visits to the IBM website weren't the most
successful so far...

Thanks again for your help, Rick.

 

[Guest]

Hello Rick!

In case this might be of help to anybody, here's the resolution we applied:

we replaced version 9.0.0 of cwbodbc.dll by version 9.0.8, and that solved
the problem. The index are now seen by Access...

That file is part of a patch for iSeries released by IBM. We could not apply
the patch (for reasons I won't go into here), so replacing that dll was the
next best solution.

Thanks to all that poured over this.

Regards.

Daniel