I have linked to a mainframe DB2 table and when I try to open the table or
run a query against it, the only thing that display is "#Deleted" in every
cell. I know there is data in the table as I can query using Excel and get
the correct results. Using the same simple query in Access, I retrieve the
correct number of rows but they all contain the same "#Deleted" message in
each cell.

I have links to other DB2 tables on this mainframe and the results are fine.
Anybody have any ideas? Thanks.

Have you tried relinking the table? I had the same thing happen to me once,
and that fixed it. Might work for you.

"Jim" wrote:

> I have linked to a mainframe DB2 table and when I try to open the table or
> run a query against it, the only thing that display is "#Deleted" in every
> cell. I know there is data in the table as I can query using Excel and get
> the correct results. Using the same simple query in Access, I retrieve the
> correct number of rows but they all contain the same "#Deleted" message in
> each cell.
>
> I have links to other DB2 tables on this mainframe and the results are fine.
> Anybody have any ideas? Thanks.
>

I've relinked it numerous times, even created it from scratch in a new
database with no luck.

"Lance" wrote:

> Have you tried relinking the table? I had the same thing happen to me once,
> and that fixed it. Might work for you.
>
> "Jim" wrote:
>
> > I have linked to a mainframe DB2 table and when I try to open the table or
> > run a query against it, the only thing that display is "#Deleted" in every
> > cell. I know there is data in the table as I can query using Excel and get
> > the correct results. Using the same simple query in Access, I retrieve the
> > correct number of rows but they all contain the same "#Deleted" message in
> > each cell.
> >
> > I have links to other DB2 tables on this mainframe and the results are fine.
> > Anybody have any ideas? Thanks.
> >

Jim wrote:
> I have linked to a mainframe DB2 table and when I try to open the
> table or run a query against it, the only thing that display is
> "#Deleted" in every cell. I know there is data in the table as I can
> query using Excel and get the correct results. Using the same simple
> query in Access, I retrieve the correct number of rows but they all
> contain the same "#Deleted" message in each cell.
>
> I have links to other DB2 tables on this mainframe and the results
> are fine. Anybody have any ideas? Thanks.

That is (usually) caused by having a primary key on the server that is a
DataType that doesn't map exactly to an Access/Jet DataType. For exaple if
you have a Timestamp field in your DB2 table Primary Key that will be
rounded in Access since Access DateTimes only resolve to the second whereas
DB2 Timestamps resolve to the microsecond. This seems to cause Access to
"lose track" of the PK value and when that happens it assumes that the
record was deleted by another process since the datsheet was opened.

Changing the DataType to one that Access "likes" will solve the problem. If
you are stuck with what you have I find that in some cases it is only a
datasheet problem. If I use the same link in a form it sometimes works
fine. Otherwise for read only access you can use a passthrough query
instead of a link. I have not seen the problem ever show up in a
passthrough query.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Thanks for the help, it works. I created a pass-through query and the
results were as I expected. I noticed that the DB2 table in question does
use a timestamp field as one of the Primary Keys. I'll see if my IT folks
can tweak it somehow.

"Rick Brandt" wrote:

> Jim wrote:
> > I have linked to a mainframe DB2 table and when I try to open the
> > table or run a query against it, the only thing that display is
> > "#Deleted" in every cell. I know there is data in the table as I can
> > query using Excel and get the correct results. Using the same simple
> > query in Access, I retrieve the correct number of rows but they all
> > contain the same "#Deleted" message in each cell.
> >
> > I have links to other DB2 tables on this mainframe and the results
> > are fine. Anybody have any ideas? Thanks.
>
> That is (usually) caused by having a primary key on the server that is a
> DataType that doesn't map exactly to an Access/Jet DataType. For exaple if
> you have a Timestamp field in your DB2 table Primary Key that will be
> rounded in Access since Access DateTimes only resolve to the second whereas
> DB2 Timestamps resolve to the microsecond. This seems to cause Access to
> "lose track" of the PK value and when that happens it assumes that the
> record was deleted by another process since the datsheet was opened.
>
> Changing the DataType to one that Access "likes" will solve the problem. If
> you are stuck with what you have I find that in some cases it is only a
> datasheet problem. If I use the same link in a form it sometimes works
> fine. Otherwise for read only access you can use a passthrough query
> instead of a link. I have not seen the problem ever show up in a
> passthrough query.
>
> --
> Rick Brandt, Microsoft Access MVP
> Email (as appropriate) to...
> RBrandt at Hunter dot com
>
>
>

Jim wrote:
> Thanks for the help, it works. I created a pass-through query and the
> results were as I expected. I noticed that the DB2 table in question
> does use a timestamp field as one of the Primary Keys. I'll see if
> my IT folks can tweak it somehow.

In my experience the fact that the Timestamp has higher resolution
*capability* is not so much the problem. It is only if that higher
resolution is actually utilized. In other words if all Timestamp values
actually stored are in even second resolution then Access has no problems
with it.

So, if I set the values of the Timestamp from my Access app they will not
contain any fractions of seconds, but if I set them on the server as a
default vlaue or from a Stored Procedure and the value stored contains
fractions of a second then Access will have problems with it. I avoid using
DateTimes and Timestamps in my primary keys for these reasons.


--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com