ODBC Link error too many indexes with AS400

[C3 Dave]

When linking to an AS400 file the link is unsuccesful due to too many indexes
- how do you correct this problem and link to the file. Same prob if try
importing

 

[Rick Brandt]

When linking to an AS400 file the link is unsuccesful due to too many
indexes - how do you correct this problem and link to the file. Same
prob if try importing

You cannot. Very often there is a Logical File or View on the AS400 that
presents the same data as the physical file you are trying to use now. If you
link to the LF or View then Access will not "see" all of the other indexes and
the error is avoided.

If no LF or View exists then you need to create one (or have someone else create
it). If that is not an option then your only alternative is to use a
passthrough query instead of linking to the table. Of course that will be read
only. If you need to do edits then you are stuck unless you want to do them via
passthrough queries that issue INSERT, UPDATE, and DELETE statements.

 

[Leslie]

I use an as/400 physical file in several of my applications. Here recently
changes have been made to the file, which as caused my apps to get the "too
many indexes" msg. Our IT dept. has about 87 logical files built on the
physical file in question. Also, I have no concrete way to tell how the
logicals are built and the description no longer matches. Our IT dept hates
Access and our two-man group and therefore refuses to give us access to the
file definitions. Me mostly, I don't even have access to "sign-off" on the
majority of the screens and we have a custimized menu system, which they have
locked us out of using the command line. Do you happen to know how I
retrieve file definitions via vba or some other method?

 

[Rick Brandt]

I use an as/400 physical file in several of my applications. Here
recently changes have been made to the file, which as caused my apps
to get the "too many indexes" msg. Our IT dept. has about 87 logical
files built on the physical file in question. Also, I have no
concrete way to tell how the logicals are built and the description
no longer matches. Our IT dept hates Access and our two-man group
and therefore refuses to give us access to the file definitions. Me
mostly, I don't even have access to "sign-off" on the majority of the
screens and we have a custimized menu system, which they have locked
us out of using the command line. Do you happen to know how I
retrieve file definitions via vba or some other method?

Well you can always just link to them and then examine the field list and
the number of rows in the link. If they are the same as the physical file
then the logical is likely only establishing a key for sequencing. That
should work for you in Access.

 

[Leslie]

You are correct by linking and checking row count, just thought there might
be a better way. The record count is close to a mil and is going to take
some time checking all the logicals. Thanks for your help.

 

[Rick Brandt]

You are correct by linking and checking row count, just thought there
might be a better way. The record count is close to a mil and is
going to take some time checking all the logicals. Thanks for your
help.

Passthrough query...

SELECT Count(*) FROM LogicalFileName