5.2 ODBC error with Access on customer table only

Yahoo Archive
1
Sarah,

I'm wondering if you've found a way around this problem yet. I too
use a lot of ODBC to Access applications and the customer table is
heavily involved. I'm still on 5.0 for the time being, but may be
forced to move forward soon. While working with Virginia from
support on a different ODBC problem, I mentioned the problem you were
having. This was her reply.

Yes, it is true that Access is not robust enough to handle the
indexes in the Customer table. There are a couple of ways around the
issue:
1. Use SQL
2. Follow the instructions in the attached document to create a view
of the customer table - then use Access to connect to this view.
Virginia

I have the instructions for creating the view if you need it, e-mail
me off list and I will send it to you. I'd really like to know how
your going to work around this problem.

Thanks,

Brian Stenglein
Clow Stamping Co.

--- In vantage@yahoogroups.com, sarah.vareschi@n... wrote:
>
> I am attempting to convert my ODBC reports from Vantage 5.0 to
Vantage 5.2.
> I am using Microsoft Access 2002 and the Vantage 5.2 driver (Merant
3.6
> SQL), with the progress database.
>
> I am able to reconnect all my tables, except the 'Customer' table.
When I
> try to link from Access to the Vantage 5.2 Customer table, I get the
> following error: damsgtoomanyindexesm "The operation failed. There
are too
> many indexes on table 'PUB_Customer'. Delete some of the indexes
on the
> table and try the operation again. (Error 3626)"
>
> The description in Access help of the error is as follows: "A
table in a
> Microsoft Jet database <JavaScript:hhobj_1.Click()> can have no
more than
> 32 indexes <JavaScript:hhobj_2.Click()> . Delete one or more
indexes from
> the named table and try the operation again."
>
> I do not know how to proceed, does anyone else know how to fix this?
>
>
> [Non-text portions of this message have been removed]
2
I am attempting to convert my ODBC reports from Vantage 5.0 to Vantage 5.2.
I am using Microsoft Access 2002 and the Vantage 5.2 driver (Merant 3.6
SQL), with the progress database.

I am able to reconnect all my tables, except the 'Customer' table. When I
try to link from Access to the Vantage 5.2 Customer table, I get the
following error: damsgtoomanyindexesm "The operation failed. There are too
many indexes on table 'PUB_Customer'. Delete some of the indexes on the
table and try the operation again. (Error 3626)"

The description in Access help of the error is as follows: "A table in a
Microsoft Jet database <JavaScript:hhobj_1.Click()> can have no more than
32 indexes <JavaScript:hhobj_2.Click()> . Delete one or more indexes from
the named table and try the operation again."

I do not know how to proceed, does anyone else know how to fix this?


[Non-text portions of this message have been removed]
3
Yup, there's 34 indexes on the Customer table. A major project for me this year is accessing Customers for our Access based quote system so I would be interested too in knowing how to "ignore" some of the indexes.
-Todd C.

-----Original Message-----
From: sarah.vareschi@... [mailto:sarah.vareschi@...]
Sent: Tuesday, January 21, 2003 10:35 AM
To: vantage@yahoogroups.com
Subject: [Vantage] 5.2 ODBC error with Access on customer table only



I am attempting to convert my ODBC reports from Vantage 5.0 to Vantage 5.2.
I am using Microsoft Access 2002 and the Vantage 5.2 driver (Merant 3.6
SQL), with the progress database.

I am able to reconnect all my tables, except the 'Customer' table. When I
try to link from Access to the Vantage 5.2 Customer table, I get the
following error: damsgtoomanyindexesm "The operation failed. There are too
many indexes on table 'PUB_Customer'. Delete some of the indexes on the
table and try the operation again. (Error 3626)"

The description in Access help of the error is as follows: "A table in a
Microsoft Jet database <JavaScript:hhobj_1.Click()> can have no more than
32 indexes <JavaScript:hhobj_2.Click()> . Delete one or more indexes from
the named table and try the operation again."

I do not know how to proceed, does anyone else know how to fix this?


[Non-text portions of this message have been removed]



Yahoo! Groups Sponsor

ADVERTISEMENT
<http://rd.yahoo.com/M=241773.2861422.4212389.1925585/D=egroupweb/S=1705007183:HM/A=1394045/R=0/*http://www.hgtv.com/hgtv/pac_ctnt/text/0,,HGTV_3936_5802,FF.html> HGTV Dream Home Giveaway
<http://us.adserver.yahoo.com/l?M=241773.2861422.4212389.1925585/D=egroupmail/S=:HM/A=1394045/rand=638751648>

Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and Crystal Reports and other 'goodies', please goto: http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto: http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto: http://groups.yahoo.com/group/vantage/links

Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service <http://docs.yahoo.com/info/terms/> .




[Non-text portions of this message have been removed]
4
One solution I use with Access is to export the table to a text file via 4gl
code and then link/import the file into access.
I am on 5.1 but I have a similar problem accessing comment fields via odbc.
If you launch the access report from vantage it is simple to add the code to
export the data and then open access. If you are running the report as a
stand alone access program create a macro of vb function that executes the
4gl code. An example of a 4gl file that exports the data then opens access
is below.
I know its not pretty but if you can't find another way this will work. You
will also find that more often then not it is faster to export the file and
import it into vantage then using ODBC.
Thanks
Jeremy Leonard
IT Manager
K-T Corporation


DEFINE VARIABLE ProgName AS CHARACTER NO-UNDO.
DEFINE VARIABLE ProgOption AS CHARACTER NO-UNDO.
DEFINE VARIABLE hInstanceNum AS INTEGER NO-UNDO.

OUTPUT TO c:\vntgwork\DMRCorAc.txt BINARY NO-CONVERT.
FOR EACH DMRCorAct:
EXPORT DELIMITER "~011" DMRCorAct.
END.
OUTPUT CLOSE.

ASSIGN ProgName = 'msaccess.exe'
ProgOption = 'v:\vantage\ud\dmrlkup\access\dmrlkup.mde'.

RUN v:\vantage\lib\ShellExec.r (INPUT ProgName,
INPUT ProgOption,
OUTPUT hInstanceNum).



-----Original Message-----
From: sarah.vareschi@... [mailto:sarah.vareschi@...]
Sent: Tuesday, January 21, 2003 11:35 AM
To: vantage@yahoogroups.com
Subject: [Vantage] 5.2 ODBC error with Access on customer table only



I am attempting to convert my ODBC reports from Vantage 5.0 to Vantage 5.2.
I am using Microsoft Access 2002 and the Vantage 5.2 driver (Merant 3.6
SQL), with the progress database.

I am able to reconnect all my tables, except the 'Customer' table. When I
try to link from Access to the Vantage 5.2 Customer table, I get the
following error: damsgtoomanyindexesm "The operation failed. There are too
many indexes on table 'PUB_Customer'. Delete some of the indexes on the
table and try the operation again. (Error 3626)"

The description in Access help of the error is as follows: "A table in a
Microsoft Jet database <JavaScript:hhobj_1.Click()> can have no more than
32 indexes <JavaScript:hhobj_2.Click()> . Delete one or more indexes from
the named table and try the operation again."

I do not know how to proceed, does anyone else know how to fix this?


[Non-text portions of this message have been removed]



Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have
already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and
Crystal Reports and other 'goodies', please goto:
http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto:
http://groups.yahoo.com/group/vantage/links

Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
5
One other option that I neglected to mention. If you have SQL Server 7.0 or
2000 then you can create a DTS Package to do the same.
I have a DTS package that runs the progress 4gl export code then imports the
data into SQL tables. I use this on our document imaging programs. It runs
every 30 minutes. The users also have an option in the vantage menu to run
the DTS Package remotely so that if they are entering a PO for example they
can attach a document to it immediately. The entire process of exporting
our Job, Part, OrderHed, RMAHEad, POHead, Customer and Vendor tables only
takes 5 sec on the server.

I use the export method because its much faster then ODBC but if you have
SQL Server you can use the Progress ODBC driver to import the data to SQL
Server via the Linked Server option. SQL Server doesn't have a 32 index
limit.

Just another idea I thought I would share.
Thanks
Jeremy Leonard
IT Manager
K-T Corporation


-----Original Message-----
From: Todd Caughey [mailto:caugheyt@...]
Sent: Tuesday, January 21, 2003 11:37 AM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] 5.2 ODBC error with Access on customer table only


Yup, there's 34 indexes on the Customer table. A major project for me this
year is accessing Customers for our Access based quote system so I would be
interested too in knowing how to "ignore" some of the indexes.
-Todd C.

-----Original Message-----
From: sarah.vareschi@... [mailto:sarah.vareschi@...]
Sent: Tuesday, January 21, 2003 10:35 AM
To: vantage@yahoogroups.com
Subject: [Vantage] 5.2 ODBC error with Access on customer table only



I am attempting to convert my ODBC reports from Vantage 5.0 to Vantage 5.2.
I am using Microsoft Access 2002 and the Vantage 5.2 driver (Merant 3.6
SQL), with the progress database.

I am able to reconnect all my tables, except the 'Customer' table. When I
try to link from Access to the Vantage 5.2 Customer table, I get the
following error: damsgtoomanyindexesm "The operation failed. There are too
many indexes on table 'PUB_Customer'. Delete some of the indexes on the
table and try the operation again. (Error 3626)"

The description in Access help of the error is as follows: "A table in a
Microsoft Jet database <JavaScript:hhobj_1.Click()> can have no more than
32 indexes <JavaScript:hhobj_2.Click()> . Delete one or more indexes from
the named table and try the operation again."

I do not know how to proceed, does anyone else know how to fix this?


[Non-text portions of this message have been removed]



Yahoo! Groups Sponsor

ADVERTISEMENT

<http://rd.yahoo.com/M=241773.2861422.4212389.1925585/D=egroupweb/S=17050071
83:HM/A=1394045/R=0/*http://www.hgtv.com/hgtv/pac_ctnt/text/0,,HGTV_3936_580
2,FF.html> HGTV Dream Home Giveaway

<http://us.adserver.yahoo.com/l?M=241773.2861422.4212389.1925585/D=egroupmai
l/S=:HM/A=1394045/rand=638751648>

Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have
already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and
Crystal Reports and other 'goodies', please goto:
http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto:
http://groups.yahoo.com/group/vantage/links

Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service
<http://docs.yahoo.com/info/terms/> .




[Non-text portions of this message have been removed]


Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have
already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and
Crystal Reports and other 'goodies', please goto:
http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto:
http://groups.yahoo.com/group/vantage/links

Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
6
Thanks, Jeremy. This is going to be a pretty big issue for us, since we
have many reports and add-on apps that our users run through Access. Nearly
all of them are linked to the customer table, so it looks like I'll have a
lot of work cut out for me making these useable before we can move to 5.2!
I'm going to try your suggestion of using VB macro within access to export
the data tables first. The only things is, we're accessing thousands of
records in 15-20 different Tables per report, so I'm worried about how long
this is going to take and how much space this is going to take up -I don't
know if it'll work for us. Jeez, at a time like this I'm VERY glad I did
the test upgrade!



[Non-text portions of this message have been removed]
7
Sarah,
Perhaps you just need to export the Customer table and not all the others. Just a thought.

Troy Funte
Liberty Electronics

----- Original Message -----
From: sarah.vareschi@...
To: vantage@yahoogroups.com
Sent: Tuesday, January 21, 2003 6:08 PM
Subject: RE: [Vantage] 5.2 ODBC error with Access on customer table only


Thanks, Jeremy. This is going to be a pretty big issue for us, since we
have many reports and add-on apps that our users run through Access. Nearly
all of them are linked to the customer table, so it looks like I'll have a
lot of work cut out for me making these useable before we can move to 5.2!
I'm going to try your suggestion of using VB macro within access to export
the data tables first. The only things is, we're accessing thousands of
records in 15-20 different Tables per report, so I'm worried about how long
this is going to take and how much space this is going to take up -I don't
know if it'll work for us. Jeez, at a time like this I'm VERY glad I did
the test upgrade!



[Non-text portions of this message have been removed]


Yahoo! Groups Sponsor
ADVERTISEMENT




Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and Crystal Reports and other 'goodies', please goto: http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto: http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto: http://groups.yahoo.com/group/vantage/links

Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.


[Non-text portions of this message have been removed]
8
There is no solution using Access and Progress. The easiest solution is to
create a linked server in SQL2000, if available, and re-link your Access
tables to that server. We had the same problem with data conversions, the
other tables we found, and I think all that are unlinkable in Access, is
Partsug and ECORev. With the linked server you can read all of the data for
your reports. You can't however, update data back to the progress database.

Thanks,
Mark S. LaRosa
MIS Manager
R&R Manufacturing Co., Inc.
Phone: 203.888.2133
Fax: 203.888.9860

-----Original Message-----
From: sarah.vareschi@... [mailto:sarah.vareschi@...]
Sent: Tuesday, January 21, 2003 11:35 AM
To: vantage@yahoogroups.com
Subject: [Vantage] 5.2 ODBC error with Access on customer table only



I am attempting to convert my ODBC reports from Vantage 5.0 to Vantage
5.2.
I am using Microsoft Access 2002 and the Vantage 5.2 driver (Merant 3.6
SQL), with the progress database.

I am able to reconnect all my tables, except the 'Customer' table. When I
try to link from Access to the Vantage 5.2 Customer table, I get the
following error: damsgtoomanyindexesm "The operation failed. There are too
many indexes on table 'PUB_Customer'. Delete some of the indexes on the
table and try the operation again. (Error 3626)"

The description in Access help of the error is as follows: "A table in a
Microsoft Jet database <JavaScript:hhobj_1.Click()> can have no more than
32 indexes <JavaScript:hhobj_2.Click()> . Delete one or more indexes from
the named table and try the operation again."

I do not know how to proceed, does anyone else know how to fix this?


[Non-text portions of this message have been removed]


Yahoo! Groups Sponsor
ADVERTISEMENT




Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must
have already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and
Crystal Reports and other 'goodies', please goto:
http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto:
http://groups.yahoo.com/group/vantage/links

Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.


[Non-text portions of this message have been removed]
9
Although not suggest, you can update Progress via linked server.

Jeremy Leonard
IT Manager
K-T Corporation


-----Original Message-----
From: Mark S. LaRosa [mailto:mark@...]
Sent: Wednesday, January 22, 2003 1:26 PM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] 5.2 ODBC error with Access on customer table only


There is no solution using Access and Progress. The easiest solution is to
create a linked server in SQL2000, if available, and re-link your Access
tables to that server. We had the same problem with data conversions, the
other tables we found, and I think all that are unlinkable in Access, is
Partsug and ECORev. With the linked server you can read all of the data for
your reports. You can't however, update data back to the progress database.

Thanks,
Mark S. LaRosa
MIS Manager
R&R Manufacturing Co., Inc.
Phone: 203.888.2133
Fax: 203.888.9860

-----Original Message-----
From: sarah.vareschi@... [mailto:sarah.vareschi@...]
Sent: Tuesday, January 21, 2003 11:35 AM
To: vantage@yahoogroups.com
Subject: [Vantage] 5.2 ODBC error with Access on customer table only



I am attempting to convert my ODBC reports from Vantage 5.0 to Vantage
5.2.
I am using Microsoft Access 2002 and the Vantage 5.2 driver (Merant 3.6
SQL), with the progress database.

I am able to reconnect all my tables, except the 'Customer' table. When I
try to link from Access to the Vantage 5.2 Customer table, I get the
following error: damsgtoomanyindexesm "The operation failed. There are too
many indexes on table 'PUB_Customer'. Delete some of the indexes on the
table and try the operation again. (Error 3626)"

The description in Access help of the error is as follows: "A table in a
Microsoft Jet database <JavaScript:hhobj_1.Click()> can have no more than
32 indexes <JavaScript:hhobj_2.Click()> . Delete one or more indexes from
the named table and try the operation again."

I do not know how to proceed, does anyone else know how to fix this?


[Non-text portions of this message have been removed]


Yahoo! Groups Sponsor
ADVERTISEMENT




Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must
have already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and
Crystal Reports and other 'goodies', please goto:
http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto:
http://groups.yahoo.com/group/vantage/links

Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.


[Non-text portions of this message have been removed]


Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have
already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and
Crystal Reports and other 'goodies', please goto:
http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto:
http://groups.yahoo.com/group/vantage/links

Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
10
Jeremy,
I stand corrected, you can update through the linked server. But, it's not
recommened. And I must also confess that I owe all of this knowledge to you
about the linked servers.
Thanks,
Mark S. LaRosa
MIS Manager
R&R Manufacturing Co., Inc.
Phone: 203.888.2133
Fax: 203.888.9860

-----Original Message-----
From: Leonard, Jeremy [mailto:jleonard@...]
Sent: Wednesday, January 22, 2003 2:03 PM
To: 'vantage@yahoogroups.com'
Subject: RE: [Vantage] 5.2 ODBC error with Access on customer table only


Although not suggest, you can update Progress via linked server.

Jeremy Leonard
IT Manager
K-T Corporation


-----Original Message-----
From: Mark S. LaRosa [mailto:mark@...]
Sent: Wednesday, January 22, 2003 1:26 PM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] 5.2 ODBC error with Access on customer table only


There is no solution using Access and Progress. The easiest solution is
to
create a linked server in SQL2000, if available, and re-link your Access
tables to that server. We had the same problem with data conversions, the
other tables we found, and I think all that are unlinkable in Access, is
Partsug and ECORev. With the linked server you can read all of the data
for
your reports. You can't however, update data back to the progress
database.

Thanks,
Mark S. LaRosa
MIS Manager
R&R Manufacturing Co., Inc.
Phone: 203.888.2133
Fax: 203.888.9860

-----Original Message-----
From: sarah.vareschi@... [mailto:sarah.vareschi@...]
Sent: Tuesday, January 21, 2003 11:35 AM
To: vantage@yahoogroups.com
Subject: [Vantage] 5.2 ODBC error with Access on customer table only



I am attempting to convert my ODBC reports from Vantage 5.0 to Vantage
5.2.
I am using Microsoft Access 2002 and the Vantage 5.2 driver (Merant 3.6
SQL), with the progress database.

I am able to reconnect all my tables, except the 'Customer' table. When
I
try to link from Access to the Vantage 5.2 Customer table, I get the
following error: damsgtoomanyindexesm "The operation failed. There are
too
many indexes on table 'PUB_Customer'. Delete some of the indexes on the
table and try the operation again. (Error 3626)"

The description in Access help of the error is as follows: "A table in
a
Microsoft Jet database <JavaScript:hhobj_1.Click()> can have no more
than
32 indexes <JavaScript:hhobj_2.Click()> . Delete one or more indexes
from
the named table and try the operation again."

I do not know how to proceed, does anyone else know how to fix this?


[Non-text portions of this message have been removed]


Yahoo! Groups Sponsor
ADVERTISEMENT




Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must
have already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder
and
Crystal Reports and other 'goodies', please goto:
http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto:
http://groups.yahoo.com/group/vantage/links

Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.


[Non-text portions of this message have been removed]


Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must
have
already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and
Crystal Reports and other 'goodies', please goto:
http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto:
http://groups.yahoo.com/group/vantage/links

Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/


Yahoo! Groups Sponsor
ADVERTISEMENT




Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must
have already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and
Crystal Reports and other 'goodies', please goto:
http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto:
http://groups.yahoo.com/group/vantage/links

Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.


[Non-text portions of this message have been removed]