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

#1 (**permalink**) 28-Jul-2006, 08:11 AM

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/10269-get-error-when-use-odbc-link.html
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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10269
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...

#2 (**permalink**) 28-Jul-2006, 08:11 AM

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

"Kobe1974" wrote in message
news:35B89D84-7E1B-4814-AD91-352E86A35D7A@microsoft.com...
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10269
> 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10269
> 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...
>

#3 (**permalink**) 28-Jul-2006, 08:11 AM

Kobe1974 wrote:
> 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10269
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.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10269

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

#4 (**permalink**) 28-Jul-2006, 08:12 AM

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10269
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 !!!

"Rick Brandt" wrote:

> Kobe1974 wrote:
> > 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10269
> >
> > 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, Microsoft Access MVP
> Email (as appropriate) to...
> RBrandt at Hunter dot com
>
>
>