Get error when use ODBC to link AS/400 physical file through Acces

[Guest]

Hello everybody,

When I use ODBC to link AS/400 physical file through Access,
it display a error message "The operation failed. There are too many indexes
on table 'ANDYTEST_S5C00'. Delete some of the indexes on the table and
try the operation again."

I've already checked the limitation of access on the number of indexes,
-Number of indexes in a table 32
-Number of fields in an index 10

I've already checked my physical file(S5C00) in AS/400, this physical has
124 logical files and the keyed fields not exceeded 10 in all of the logical
files. Since
IBM said if logical file has a keyed field, it is considered as index.

My windows version is XP SP2 and access is 2002 SP3 and MDAC is 2.8 SP1.

Do anyone can help me on this trouble ??

Many thanks !!!

Andy...

 

[Arvin Meyer [MVP]]

You may need to convert the text from the AS/400 to Windows ASCII. Unix
based text terminates with a LineFeed, while DOS uses a CarriageReturn and a
LineFeed. Try importing the text then running something like this:

Public Function UNIX2DOS(ByVal str As String) As String
UNIX2DOS = Replace(str, Chr(10), Chr(13) & Chr(10))
End Function
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[Arvin Meyer [MVP]]

You may need to convert the text from the AS/400 to Windows ASCII. Unix
based text terminates with a LineFeed, while DOS uses a CarriageReturn and a
LineFeed. Try importing the text then running something like this:

Public Function UNIX2DOS(ByVal str As String) As String
UNIX2DOS = Replace(str, Chr(10), Chr(13) & Chr(10))
End Function
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[Rick Brandt]

Hello everybody,

When I use ODBC to link AS/400 physical file through Access,
it display a error message "The operation failed. There are too many
indexes on table 'ANDYTEST_S5C00'. Delete some of the indexes on the
table and
try the operation again."

I've already checked the limitation of access on the number of
indexes,
-Number of indexes in a table 32
-Number of fields in an index 10

I've already checked my physical file(S5C00) in AS/400, this physical
has 124 logical files and the keyed fields not exceeded 10 in all of
the logical files. Since
IBM said if logical file has a keyed field, it is considered as index.

My windows version is XP SP2 and access is 2002 SP3 and MDAC is 2.8
SP1.

Do anyone can help me on this trouble ??

To Access every one of those logical files is an index. Link to one of them
instead of the physical file. Just make sure you pick one that returns all rows
(assuming that's what you need). The other logical files are not seen as
indexes by Access when you link to a logical file, but they are if you link to
the physical file.

 

[Rick Brandt]

Hello everybody,

When I use ODBC to link AS/400 physical file through Access,
it display a error message "The operation failed. There are too many
indexes on table 'ANDYTEST_S5C00'. Delete some of the indexes on the
table and
try the operation again."

I've already checked the limitation of access on the number of
indexes,
-Number of indexes in a table 32
-Number of fields in an index 10

I've already checked my physical file(S5C00) in AS/400, this physical
has 124 logical files and the keyed fields not exceeded 10 in all of
the logical files. Since
IBM said if logical file has a keyed field, it is considered as index.

My windows version is XP SP2 and access is 2002 SP3 and MDAC is 2.8
SP1.

Do anyone can help me on this trouble ??

To Access every one of those logical files is an index. Link to one of them
instead of the physical file. Just make sure you pick one that returns all rows
(assuming that's what you need). The other logical files are not seen as
indexes by Access when you link to a logical file, but they are if you link to
the physical file.

 

[Guest]

Hello Rick Brandt & Arvin Meyer,

Many thanks your reply...
If access every one of those logical files is an index, but I've tried to link
one physical files which has 40 logical files through ODBC. It can be linked
successfully, so I'm quite confused the limitation of access whether is true
??

The limitation of access on the number of indexes,
-Number of indexes in a table 32
-Number of fields in an index 10

Is there any way or tools which I can use to monitor which index is hit the
limitation ??

Many thanks your big help !!!