Mass update of part unit price [5.1]

--- In vantage@yahoogroups.com, "Rob Willhite" <rwillhite@...> wrote:
>
> Mark,
>
> As I said in an earlier post, you can use VBScript to update any table
> in Vantage. Here is an example that update the PartOpr Table.
>
> Set conn = CreateObject("ADODB.Connection")
> Set rs = CreateObject("ADODB.Recordset")
> conn.Open ("dsn=VantageLive_Update;uid=sysprogress;pwd=sysprogress")
> SQL = "Update pub.PartOpr Set WCCODE = '101C' Where WCCODE = '103A'"
> rs.Open SQL, conn, 3, 3
> msgbox("Update Complete")
>
> If you insist on using Access (Which I wouldn't) You have to relink
> the table using a query statnebt excluding the field that is giving
> your difficulty.
>

Rob,
I don't have any preference as to how to do this, I just need to find
a way that I can achieve, so thank you for the example. I don't
really have any VBScript experience, however, I don't have any
experience using access in this manner either. Its sounds like I will
be on a VBscript learning crash course.
> rs.Open SQL, conn, 3, 3
What do the 3's do in the above line?
Mark
We are looking to update the unit price on our parts. From searching
the message archive it seems people have used OBDC to update prices but
none of the posts actually showed how. I've really only gotten as far
as being able to connect to the database with MS Access through ODBC. I
tried importing the part table into Access but got an error saying the
MfgComment has a value exceeding its max length. Do I need to import
the tables or can I just create a table with the updated pricing and
then Link Tables. Also, I'm not sure what the update query would be.
Can anyone help clarify things for me?

Thanks,

Mark



[Non-text portions of this message have been removed]
--- In vantage@yahoogroups.com, "Mark Caldwell" <mcaldwell@...> wrote:
>
> We are looking to update the unit price on our parts. From
searching
> the message archive it seems people have used OBDC to update prices
but
> none of the posts actually showed how. I've really only gotten as
far
> as being able to connect to the database with MS Access through
ODBC. I
> tried importing the part table into Access but got an error saying
the
> MfgComment has a value exceeding its max length. Do I need to
import
> the tables or can I just create a table with the updated pricing and
> then Link Tables. Also, I'm not sure what the update query would
be.
> Can anyone help clarify things for me?
>
> Thanks,
>
> Mark
>
>
>
> [Non-text portions of this message have been removed]
>

Yes you can update the part table using ODBC. The problem you are
having with MS Access is because of an underlying problem with the
SQL Width as defined in the table. If you are using Progress there
is a DB tool to help fix that. Also, if you are not on the .400
version yet you can use SQL explorer to create a view of the part
table that you can use in MS Access if the DB tool does not fix the
problem. If you know anything about programming I would suggest
writing a program rather than using MS Access to update the table.
Access has many limitations than can create problems. If you need
more information email me and I will send you what I have on the DB
tool and/or the instructions on how to create a view. I can also
send you a sample VB6.0 program if you would like.

Charles
Charles,



I suspect I have come across this issue with the SQL width and while I have
managed to resolve all my problems I am wondering just exactly what goes on
here, Is this some problem with the initial setup of the Progress database
that the tool corrects and the issue is resolved forever or is it worse than
that?



Tim Stevenson

Hufcor Australia



_____

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of
charles06072006
Sent: Monday, 21 January 2008 1:55 AM
To: vantage@yahoogroups.com
Subject: [Vantage] Re: mass update of part unit price [5.1]



--- In vantage@yahoogroups <mailto:vantage%40yahoogroups.com> .com, "Mark
Caldwell" <mcaldwell@...> wrote:
>
> We are looking to update the unit price on our parts. From
searching
> the message archive it seems people have used OBDC to update prices
but
> none of the posts actually showed how. I've really only gotten as
far
> as being able to connect to the database with MS Access through
ODBC. I
> tried importing the part table into Access but got an error saying
the
> MfgComment has a value exceeding its max length. Do I need to
import
> the tables or can I just create a table with the updated pricing and
> then Link Tables. Also, I'm not sure what the update query would
be.
> Can anyone help clarify things for me?
>
> Thanks,
>
> Mark
>
>
>
> [Non-text portions of this message have been removed]
>

Yes you can update the part table using ODBC. The problem you are
having with MS Access is because of an underlying problem with the
SQL Width as defined in the table. If you are using Progress there
is a DB tool to help fix that. Also, if you are not on the .400
version yet you can use SQL explorer to create a view of the part
table that you can use in MS Access if the DB tool does not fix the
problem. If you know anything about programming I would suggest
writing a program rather than using MS Access to update the table.
Access has many limitations than can create problems. If you need
more information email me and I will send you what I have on the DB
tool and/or the instructions on how to create a view. I can also
send you a sample VB6.0 program if you would like.

Charles





[Non-text portions of this message have been removed]
Tim,
I suspect that Progress (Open Edge)was not really designed to be used
with ODBC. I know Epicor does try to get most users to use the BAQ
reports, etc. I don't think they expect many users to use Access
against the database either. Epicor blames the SQL width problem with
the Progress design. I am sure Progress would blame Epicor. The only
tables I have a real problem with or the OrderHed, OrderDtl, and the
PartOpr tables. I cannot get to them using Access. I have not tried
creating a view for them since the problem is not too many indexes as
it is with the Customer table.

When you create the view for a table and bring it into access don't
forget to specify a unique index for it if you want to be able to
update any data.

--- In vantage@yahoogroups.com, "Tim Stevenson" <tim.s@...> wrote:
>
> Charles,
>
>
>
> I suspect I have come across this issue with the SQL width and while
I have
> managed to resolve all my problems I am wondering just exactly what
goes on
> here, Is this some problem with the initial setup of the Progress
database
> that the tool corrects and the issue is resolved forever or is it
worse than
> that?
>
>
>
> Tim Stevenson
>
> Hufcor Australia
>
>
>
> _____
>
> From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On
Behalf Of
> charles06072006
> Sent: Monday, 21 January 2008 1:55 AM
> To: vantage@yahoogroups.com
> Subject: [Vantage] Re: mass update of part unit price [5.1]
>
>
>
> --- In vantage@yahoogroups <mailto:vantage%40yahoogroups.com> .com,
"Mark
> Caldwell" <mcaldwell@> wrote:
> >
> > We are looking to update the unit price on our parts. From
> searching
> > the message archive it seems people have used OBDC to update prices
> but
> > none of the posts actually showed how. I've really only gotten as
> far
> > as being able to connect to the database with MS Access through
> ODBC. I
> > tried importing the part table into Access but got an error saying
> the
> > MfgComment has a value exceeding its max length. Do I need to
> import
> > the tables or can I just create a table with the updated pricing and
> > then Link Tables. Also, I'm not sure what the update query would
> be.
> > Can anyone help clarify things for me?
> >
> > Thanks,
> >
> > Mark
> >
> >
> >
> > [Non-text portions of this message have been removed]
> >
>
> Yes you can update the part table using ODBC. The problem you are
> having with MS Access is because of an underlying problem with the
> SQL Width as defined in the table. If you are using Progress there
> is a DB tool to help fix that. Also, if you are not on the .400
> version yet you can use SQL explorer to create a view of the part
> table that you can use in MS Access if the DB tool does not fix the
> problem. If you know anything about programming I would suggest
> writing a program rather than using MS Access to update the table.
> Access has many limitations than can create problems. If you need
> more information email me and I will send you what I have on the DB
> tool and/or the instructions on how to create a view. I can also
> send you a sample VB6.0 program if you would like.
>
> Charles
>
>
>
>
>
> [Non-text portions of this message have been removed]
>
Charles,



Thanks, I usually manage to get what I need with msquery and or Access by
excluding the odd field, I am generally only doing this to check results I
will get using SQL via VB6 so it is not a great inconvenience. I have not
yet had an insurmountable problem accessing data via VB6 but I have a
feeling I will hit some limitations as my requirements become more complex,
only doing pretty basic stuff at the moment. I have had to modify some
query's that should have worked and for, as yet, undefined reasons would
not. What I have seen so far gives me no confidence to actually write data
back to progress using this method, I also feel Progress is not really happy
to work with ODBC and problems would likely occur.



Tim



_____

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of
charles06072006
Sent: Monday, 21 January 2008 11:24 PM
To: vantage@yahoogroups.com
Subject: [Vantage] Re: mass update of part unit price [5.1]



Tim,
I suspect that Progress (Open Edge)was not really designed to be used
with ODBC. I know Epicor does try to get most users to use the BAQ
reports, etc. I don't think they expect many users to use Access
against the database either. Epicor blames the SQL width problem with
the Progress design. I am sure Progress would blame Epicor. The only
tables I have a real problem with or the OrderHed, OrderDtl, and the
PartOpr tables. I cannot get to them using Access. I have not tried
creating a view for them since the problem is not too many indexes as
it is with the Customer table.

When you create the view for a table and bring it into access don't
forget to specify a unique index for it if you want to be able to
update any data.

--- In vantage@yahoogroups <mailto:vantage%40yahoogroups.com> .com, "Tim
Stevenson" <tim.s@...> wrote:
>
> Charles,
>
>
>
> I suspect I have come across this issue with the SQL width and while
I have
> managed to resolve all my problems I am wondering just exactly what
goes on
> here, Is this some problem with the initial setup of the Progress
database
> that the tool corrects and the issue is resolved forever or is it
worse than
> that?
>
>
>
> Tim Stevenson
>
> Hufcor Australia
>
>
>
> _____
>
> From: vantage@yahoogroups <mailto:vantage%40yahoogroups.com> .com
[mailto:vantage@yahoogroups <mailto:vantage%40yahoogroups.com> .com] On
Behalf Of
> charles06072006
> Sent: Monday, 21 January 2008 1:55 AM
> To: vantage@yahoogroups <mailto:vantage%40yahoogroups.com> .com
> Subject: [Vantage] Re: mass update of part unit price [5.1]
>
>
>
> --- In vantage@yahoogroups <mailto:vantage%40yahoogroups.com> .com,
"Mark
> Caldwell" <mcaldwell@> wrote:
> >
> > We are looking to update the unit price on our parts. From
> searching
> > the message archive it seems people have used OBDC to update prices
> but
> > none of the posts actually showed how. I've really only gotten as
> far
> > as being able to connect to the database with MS Access through
> ODBC. I
> > tried importing the part table into Access but got an error saying
> the
> > MfgComment has a value exceeding its max length. Do I need to
> import
> > the tables or can I just create a table with the updated pricing and
> > then Link Tables. Also, I'm not sure what the update query would
> be.
> > Can anyone help clarify things for me?
> >
> > Thanks,
> >
> > Mark
> >
> >
> >
> > [Non-text portions of this message have been removed]
> >
>
> Yes you can update the part table using ODBC. The problem you are
> having with MS Access is because of an underlying problem with the
> SQL Width as defined in the table. If you are using Progress there
> is a DB tool to help fix that. Also, if you are not on the .400
> version yet you can use SQL explorer to create a view of the part
> table that you can use in MS Access if the DB tool does not fix the
> problem. If you know anything about programming I would suggest
> writing a program rather than using MS Access to update the table.
> Access has many limitations than can create problems. If you need
> more information email me and I will send you what I have on the DB
> tool and/or the instructions on how to create a view. I can also
> send you a sample VB6.0 program if you would like.
>
> Charles
>





[Non-text portions of this message have been removed]
I have had great success doing mass updates using ODBC by creating a
VBScript that uses the ODBC connection then issues an SQL statement
dirctly to the Progress database, bypassing any of the problems that
you will run into in Access. The main problem is that the Jet engine
cannot handle the volume of records being transacted against.
Mark,

As I said in an earlier post, you can use VBScript to update any table
in Vantage. Here is an example that update the PartOpr Table.

Set conn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
conn.Open ("dsn=VantageLive_Update;uid=sysprogress;pwd=sysprogress")
SQL = "Update pub.PartOpr Set WCCODE = '101C' Where WCCODE = '103A'"
rs.Open SQL, conn, 3, 3
msgbox("Update Complete")

If you insist on using Access (Which I wouldn't) You have to relink
the table using a query statnebt excluding the field that is giving
your difficulty.