#NAME? displaying in linked tables (Access 2000) after DB2 migration.

[smd]

Hello and thanks for taking the time to read this. I've looked all
over the web and newsgroups and can't find a solution to my problem.
I've posted this question to the Access 2000 group as well - sorry for
cross posting.

Back ground:
User at remote site has Access 2000 running on XP (don't know SP of
either). Linked tables are DB2 v8 running on AIX. All of the linked
tables are in the same database and schema.

Problem:
Some linked tables, when viewed in datasheet form from within Access,
display "#NAME?"; others do not. This problem started right afer the
database was migrated from 390 to AIX.

Things I've tried:
All obvious connectivity related trouble shooting (the server and DB
can be seen from IBM DB2 Configuration assistant and Putty, the DSN is
registered ODBC, etc.) Results: nothing out of the ordinary.

Reinstalled Msowfc.dll as suggested in KB article(not sure I spelled it

correctly here, but you get the picture) Results: no effect

Checked connection string using this command typed in the Immediate
Window: ?dbengine(0)(0).TableDefs("CAP_UFPWK_DPTMDSC_SLSST K").Connect
Results: Thought I had something here when one of the connection
strings was returned with the user's current ID, but an old password.
Had the user delete the linked table in Access (highlighting and
pressing delete key) and then set up the linked table again. Now all
connection strings are the same
(ODBC;DSN=LFPPROD;MODE=SHARE;DBALIAS=LFPPROD;). Unfortunately, no
effect.

I'm stuck! Anyone have similar problems? Anyone have a suggestion on
how to fix? Thanks in advance for your help!

[polite person]

On 7 Oct 2005 08:23:23 -0700, "smd" <sd******@swbell.net> wrote:

Hello and thanks for taking the time to read this. I've looked all
over the web and newsgroups and can't find a solution to my problem.
I've posted this question to the Access 2000 group as well - sorry for
cross posting.

Back ground:
User at remote site has Access 2000 running on XP (don't know SP of
either). Linked tables are DB2 v8 running on AIX. All of the linked
tables are in the same database and schema.

Problem:
Some linked tables, when viewed in datasheet form from within Access,
display "#NAME?"; others do not. This problem started right afer the
database was migrated from 390 to AIX.

Things I've tried:
All obvious connectivity related trouble shooting (the server and DB
can be seen from IBM DB2 Configuration assistant and Putty, the DSN is
registered ODBC, etc.) Results: nothing out of the ordinary.

Reinstalled Msowfc.dll as suggested in KB article(not sure I spelled it

correctly here, but you get the picture) Results: no effect

Checked connection string using this command typed in the Immediate
Window: ?dbengine(0)(0).TableDefs("CAP_UFPWK_DPTMDSC_SLSST K").Connect
Results: Thought I had something here when one of the connection
strings was returned with the user's current ID, but an old password.
Had the user delete the linked table in Access (highlighting and
pressing delete key) and then set up the linked table again. Now all
connection strings are the same
(ODBC;DSN=LFPPROD;MODE=SHARE;DBALIAS=LFPPROD;). Unfortunately, no
effect.

I'm stuck! Anyone have similar problems? Anyone have a suggestion on
how to fix? Thanks in advance for your help!

Hi
have you tried recreating the offending tables on the db2 side? In principle you can do this
from Access by exporting the table but I've never had much luck with that and
I usually run create table and index queries from Access to make the structure. (I find
this easier than using the IBM tools because I am not familiar with them)

Are there indexes on these tables?

I read somewhere that the coming of 64 bit windows is leading to changes in the length
of some SQL types in ODBC which I expect will lead to problems in the future if different
versions of the software are used on the two systems.

[smd]

Hi, thanks for your reply! I have not rebuilt the tables because there
are no other problems accessing the DB2 tables using linked Access
tables anywhere else in the enterprise - except for this user and his
team (copied the MDB and distributed it amongst themselves).

Yes, there are indexes on the tables.

Does Access keep an entry somewhere (registry) that would hold a
connection string?

If not, is it possible that upon deletion and recreation of a linked
table, the DB object is not being refreshed (does not recognize that
the link has been deleted and recreated) so that when Access is shut
down, the old link is saved? Do you think I might need to explicitly
(from the Immediate window) referesh the DB object after deletion of
the linked table and repeat after recreation?

[Brian Wilson]

"smd" <sd******@swbell.net> wrote in message
news:11*********************@g49g2000cwa.googlegro ups.com...

Hi, thanks for your reply! I have not rebuilt the tables because there
are no other problems accessing the DB2 tables using linked Access
tables anywhere else in the enterprise - except for this user and his
team (copied the MDB and distributed it amongst themselves).

Yes, there are indexes on the tables.

Does Access keep an entry somewhere (registry) that would hold a
connection string?

If not, is it possible that upon deletion and recreation of a linked
table, the DB object is not being refreshed (does not recognize that
the link has been deleted and recreated) so that when Access is shut
down, the old link is saved? Do you think I might need to explicitly
(from the Immediate window) referesh the DB object after deletion of
the linked table and repeat after recreation?

When I read the title, I was going to suggest to delete and re-build the
Access liked tables, but read that you hve done this already. Could you try
getting them to create a brand new blank database and just creating the
linkd tables? If that works, possibly rebuild the front end.

[smd]

Yea figured it out! Apparently deleting and recreating the linked
tables during the same session from the main DB|Tables window does not
refresh Access's underly DB object. But once I had the user install
the Linked Table Manager and select all the recently deleted and
recreated linked tables, the data magically appeared upon update. You
would have thought that something this easy would appear in a KB
somewhere!
Thanks again for the help!

[polite person]

On 7 Oct 2005 12:01:02 -0700, "smd" <sd******@swbell.net> wrote:

Hi, thanks for your reply! I have not rebuilt the tables because there
are no other problems accessing the DB2 tables using linked Access
tables anywhere else in the enterprise - except for this user and his
team (copied the MDB and distributed it amongst themselves).

Yes, there are indexes on the tables.

Does Access keep an entry somewhere (registry) that would hold a
connection string?
Not so far as I am awareIf not, is it possible that upon deletion and recreation of a linked
table, the DB object is not being refreshed (does not recognize that
the link has been deleted and recreated) so that when Access is shut
down, the old link is saved? Do you think I might need to explicitly
(from the Immediate window) referesh the DB object after deletion of
the linked table and repeat after recreation?

Usually, closing and re-opening the database would force any refresh.
Does every field in the table come out as #NAME? This suggests a type mismatch.
Are fields of particular types affected?

Try to open a recordset from the table in code and copy the fields into
variables of the expected type see what error you get?

Though this shouldn't affect anything,are any of the fields renamed using the
caption property?

[polite person]

On 7 Oct 2005 13:29:39 -0700, "smd" <sd******@swbell.net> wrote:

Yea figured it out! Apparently deleting and recreating the linked
tables during the same session from the main DB|Tables window does not
refresh Access's underly DB object. But once I had the user install
the Linked Table Manager and select all the recently deleted and
recreated linked tables, the data magically appeared upon update. You
would have thought that something this easy would appear in a KB
somewhere!
Thanks again for the help!

Glad this works, though I am surprised as I have often just changed the
connection strings with FindAndReplace in cases where the linked table
manager won't work and provided I close the database (and maybe compact it)
it has worked. Admtitddly this was with local tables in Access 97 days.

[smd]

Thanks again for your help!

Would you believe this has been an open trouble ticket since January
(it came across my desk Wednesday afternoon - since it's *obviously* a
problem with the way my predecessor defined the DB2 table)!