errors in data transfer AS400-->MS Access

errors in data transfer AS400-->MS Access

Post by Renato Debias » Sat, 02 Sep 2000 03:37:13



We are trying to transfer data from AS400 to PC using MS ACCESS 7.0. To
do this we're using ODBC driver supplied with Client Express Access by IBM.
When we try to transfer very high volumes of data (up to 100K records) we
have a bad data transfer : in some records, never the same, and in different
fields often appear the message "#ELIMINATED".
Did anyone experience this sort of error or anyway can help us?
Thanks very much.

Renato Debiasi

 
 
 

errors in data transfer AS400-->MS Access

Post by Phil Castl » Sat, 02 Sep 2000 08:25:11


MS Access accesses DB2 data in a strange (and not very efficient) way. Eg.
it:
a) reads all the rows
b) for each row it will determine the primary key columns
c) it will then re-read all the rows to see if they are still there and
haven't been deleted since they were first read.

It does all this in case you want to update the data in the grid.

Problems happen if the re-read in step c) fails. This has been the subject
of other posts to this newsgroup (and I have experienced it too - had  me
baffled for years) - if your key column is a number or a timestamp the
re-read will always fail as Access will have lost some of the precision so
will never find the row. In this case the rows get marked as #Deleted#.

I have never seen #Eliminated# but I suspect that it is probably due to a
similar cause. If the re-read fails for some reason (eg. data transfer
problem) then Access will think the row is no longer valid.

Hope this points you in the right direction. Maybe you could use a tool
other than Access.

Phil Castle.


Quote:> We are trying to transfer data from AS400 to PC using MS ACCESS 7.0. To
> do this we're using ODBC driver supplied with Client Express Access by
IBM.
> When we try to transfer very high volumes of data (up to 100K records) we
> have a bad data transfer : in some records, never the same, and in
different
> fields often appear the message "#ELIMINATED".
> Did anyone experience this sort of error or anyway can help us?
> Thanks very much.

> Renato Debiasi


 
 
 

errors in data transfer AS400-->MS Access

Post by Renato Debias » Sun, 03 Sep 2000 07:46:42


I was worried to receive this answer like this!!#Eliminated instead
#Deleted message is due to my translation from Italian , nothing else.
Thank you very much.
Renato Debiasi



> MS Access accesses DB2 data in a strange (and not very efficient) way. Eg.
> it:
> a) reads all the rows
> b) for each row it will determine the primary key columns
> c) it will then re-read all the rows to see if they are still there and
> haven't been deleted since they were first read.

> It does all this in case you want to update the data in the grid.

> Problems happen if the re-read in step c) fails. This has been the subject
> of other posts to this newsgroup (and I have experienced it too - had  me
> baffled for years) - if your key column is a number or a timestamp the
> re-read will always fail as Access will have lost some of the precision so
> will never find the row. In this case the rows get marked as #Deleted#.

> I have never seen #Eliminated# but I suspect that it is probably due to a
> similar cause. If the re-read fails for some reason (eg. data transfer
> problem) then Access will think the row is no longer valid.

> Hope this points you in the right direction. Maybe you could use a tool
> other than Access.

> Phil Castle.



> > We are trying to transfer data from AS400 to PC using MS ACCESS 7.0. To
> > do this we're using ODBC driver supplied with Client Express Access by
> IBM.
> > When we try to transfer very high volumes of data (up to 100K records)
we
> > have a bad data transfer : in some records, never the same, and in
> different
> > fields often appear the message "#ELIMINATED".
> > Did anyone experience this sort of error or anyway can help us?
> > Thanks very much.

> > Renato Debiasi