Vantage 6.1 - Best way to extract data?

I would love more info. I'm very much a novice compared to most the user group members it appears....Does access have a developer edition so as to create runtimes then? Or are you just talking standard Access databases....I have previous experience programming in foxpro, dbase, etc....but never had to worry about how to get the source data - it was always supplied to me.... Id don't know the the PCValueIn that you're both referred to is...and this Total Visual Agent sounds like something I'd be interested in as well to update the tables in Access overnight....so basically are you downloading data from Vantage into Access tables and then manipulating from there? I'm not sure where to start but I'm a pretty good self-learner by reading and testing....just need to be pointed in the right direction as far as what tools I need to start....

If your time permits, please contact me at parichter@........


--- In vantage@yahoogroups.com, "B_Stinger" <bstenglein@...> wrote:
>
> I'm not sure what you mean about SQL type programming in Crystal, everything can be done through the interface without typing SQL commands. But I only use Crystal for the simplest reports.
>
> I agree with Lon, that Access is a great tool to get info out of Vantage. We have dozens of Access applications using ODBC. I've found that linking a query or report live to ODBC isn't efficient so I use Make Table Queries or Append Table Querires to dump the data in the Access applications when necessary. We use a product called Total Visual Agent which facilitates the scheduling of Access Macros. We use that to update the data in the various applications over night, so the users don't have to wait for the data to refresh. The performance of most reports is a lot quicker than if you were linking to the live database.
>
> Let me know if you'd like more details on how to use Access with Vantage.
>
> Brian Stenglein
> Clow Stamping
>
> --- In vantage@yahoogroups.com, "richter_clements" <richter_clements@> wrote:
> >
> > We are on Vantage 6.1 and have not used the system for very much reporting ever. We are now in the process of trying to create and utilize much of the information that we input. I'm getting a good handle on all the various tables and where information is stored. But now - I need to link data and extract how to report on. I'm not sure the best way to go about. I do have Crystal reports and can link to the LIVE database through ODBC which leaves a lot of SQL type programming to be done in Crystal (to link tables, filter data, etc...), or I guess create BAQs to dump data into Excel, Access, etc and then manipulate from there. I'm not familiar with SQL and so far I keep encountering system crashes and such every time I try to design SQL querys (using Crystal SQL Designer) and run them....
> >
> > Is this the best way to go about linking various tables to produce the data I want? Or am I better off just dumping the tables through a BAQ to create PC Level databases and running queries or programming from there? I have a lot of previous experience with developing applications in DBASE, Foxpro, etc...but nothing in SQL - I assume it's all the same concepts just different terminology....
> >
> > Any suggestions? Are there various versions of SQL - so if I run out to get some books on it I can at least learn the right one?
> >
> > Does the link between crystal to vantage use standard SQL statements?
> >
> > What about XML? I see the option in BAQ - to create an XML source....
> >
>
We are on Vantage 6.1 and have not used the system for very much reporting ever. We are now in the process of trying to create and utilize much of the information that we input. I'm getting a good handle on all the various tables and where information is stored. But now - I need to link data and extract how to report on. I'm not sure the best way to go about. I do have Crystal reports and can link to the LIVE database through ODBC which leaves a lot of SQL type programming to be done in Crystal (to link tables, filter data, etc...), or I guess create BAQs to dump data into Excel, Access, etc and then manipulate from there. I'm not familiar with SQL and so far I keep encountering system crashes and such every time I try to design SQL querys (using Crystal SQL Designer) and run them....

Is this the best way to go about linking various tables to produce the data I want? Or am I better off just dumping the tables through a BAQ to create PC Level databases and running queries or programming from there? I have a lot of previous experience with developing applications in DBASE, Foxpro, etc...but nothing in SQL - I assume it's all the same concepts just different terminology....

Any suggestions? Are there various versions of SQL - so if I run out to get some books on it I can at least learn the right one?

Does the link between crystal to vantage use standard SQL statements?

What about XML? I see the option in BAQ - to create an XML source....
We've been running Vantage 6.1 for 5 years now and we use Access. We
dumped Crystal because of performance. We link to Vantage/Progress
through ODBC. We now have 75 Access databases running all using the
standard Access database engine which is called Jet. We have not yet
needed to put any data into SQL Server Express Edition. We do a nightly
dump of the PcInValue because it has 5 million records. All of our
orders are custom built which means a lot of records in that table.
That table is a mess. We actually dump it into 3 main tables, a
PcInValue for Quotes and one for Orders and one for Invoices. Each one
has just the data needed and is indexed according to our needs. We also
do a further dump into a PcInValues Orders Open which keeps the total
records to less than 100,000. All of this means we have fast response
for printing quotes, orders, invoices, production schedule, and analysis
- all done with Access. With this setup we can print an Order
acknowledgement in less than 1 second. At some future point we may move
this table for the Access Jet engine to SQL Server 2008 Express Edition
(which is free).



Lon A. Wiksell

ROM Corporation



________________________________

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of richter_clements
Sent: Tuesday, September 22, 2009 10:31 AM
To: vantage@yahoogroups.com
Subject: [Vantage] Vantage 6.1 - Best way to extract data???





We are on Vantage 6.1 and have not used the system for very much
reporting ever. We are now in the process of trying to create and
utilize much of the information that we input. I'm getting a good handle
on all the various tables and where information is stored. But now - I
need to link data and extract how to report on. I'm not sure the best
way to go about. I do have Crystal reports and can link to the LIVE
database through ODBC which leaves a lot of SQL type programming to be
done in Crystal (to link tables, filter data, etc...), or I guess create
BAQs to dump data into Excel, Access, etc and then manipulate from
there. I'm not familiar with SQL and so far I keep encountering system
crashes and such every time I try to design SQL querys (using Crystal
SQL Designer) and run them....

Is this the best way to go about linking various tables to produce the
data I want? Or am I better off just dumping the tables through a BAQ to
create PC Level databases and running queries or programming from there?
I have a lot of previous experience with developing applications in
DBASE, Foxpro, etc...but nothing in SQL - I assume it's all the same
concepts just different terminology....

Any suggestions? Are there various versions of SQL - so if I run out to
get some books on it I can at least learn the right one?

Does the link between crystal to vantage use standard SQL statements?

What about XML? I see the option in BAQ - to create an XML source....





[Non-text portions of this message have been removed]
I'm not sure what you mean about SQL type programming in Crystal, everything can be done through the interface without typing SQL commands. But I only use Crystal for the simplest reports.

I agree with Lon, that Access is a great tool to get info out of Vantage. We have dozens of Access applications using ODBC. I've found that linking a query or report live to ODBC isn't efficient so I use Make Table Queries or Append Table Querires to dump the data in the Access applications when necessary. We use a product called Total Visual Agent which facilitates the scheduling of Access Macros. We use that to update the data in the various applications over night, so the users don't have to wait for the data to refresh. The performance of most reports is a lot quicker than if you were linking to the live database.

Let me know if you'd like more details on how to use Access with Vantage.

Brian Stenglein
Clow Stamping

--- In vantage@yahoogroups.com, "richter_clements" <richter_clements@...> wrote:
>
> We are on Vantage 6.1 and have not used the system for very much reporting ever. We are now in the process of trying to create and utilize much of the information that we input. I'm getting a good handle on all the various tables and where information is stored. But now - I need to link data and extract how to report on. I'm not sure the best way to go about. I do have Crystal reports and can link to the LIVE database through ODBC which leaves a lot of SQL type programming to be done in Crystal (to link tables, filter data, etc...), or I guess create BAQs to dump data into Excel, Access, etc and then manipulate from there. I'm not familiar with SQL and so far I keep encountering system crashes and such every time I try to design SQL querys (using Crystal SQL Designer) and run them....
>
> Is this the best way to go about linking various tables to produce the data I want? Or am I better off just dumping the tables through a BAQ to create PC Level databases and running queries or programming from there? I have a lot of previous experience with developing applications in DBASE, Foxpro, etc...but nothing in SQL - I assume it's all the same concepts just different terminology....
>
> Any suggestions? Are there various versions of SQL - so if I run out to get some books on it I can at least learn the right one?
>
> Does the link between crystal to vantage use standard SQL statements?
>
> What about XML? I see the option in BAQ - to create an XML source....
>
Good point Brian on Total Visual Agent. That is a product put out by
FMS that we also use to run our extracts over night. Our PcInValue
table for orders has 4.8 million records and takes 9 mins to download.
Our open orders PcInValue has 60,000 records and takes 4.3 seconds.
Both of these are being loading into Access database tables. When I get
time we're going to test downloading these into SQL Server 2005 or 2008.



Many of the small tables such as ShipVia, Terms and Parts I will
download into local tables when my main form is opened. I run a delete
and append because each local table has been modified for performance.
In these local tables I've turned unicode compression on, changed the
Vantage decimal fields to double and created the correct indexes. FMS
also has an analyzer product that is a great help with performance tips.
I also use FMS for version control. I will make my final changes and
save it as an MDE then move it to the production folders. Next time the
use clicks on the desktop application icon FMS checks my new production
version with what's on their C drive and downloads the latest version if
it's not the same. It works slick. Running the app from the users C
drive is 2 to 3 times faster that using a network drive.



I have built into each download a performance monitoring system. I know
how long it takes to run the download and how long each query takes to
run. My order report takes 82 steps or queries and VBA code to build
and took 3.8 seconds to run on the network drive and 1.06 seconds to run
on my C drive. I also have an activity database that tracks what
applications are being used and by whom. This can be a great help in
keeping track of the many databases we run and in the importance of each
database. It keeps the users happy especially when they are on the
phone with a customer who wants to review an order.



Lon A. Wiksell

ROM Corporation



________________________________

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of B_Stinger
Sent: Wednesday, September 23, 2009 8:09 AM
To: vantage@yahoogroups.com
Subject: [Vantage] Re: Vantage 6.1 - Best way to extract data???





I'm not sure what you mean about SQL type programming in Crystal,
everything can be done through the interface without typing SQL
commands. But I only use Crystal for the simplest reports.

I agree with Lon, that Access is a great tool to get info out of
Vantage. We have dozens of Access applications using ODBC. I've found
that linking a query or report live to ODBC isn't efficient so I use
Make Table Queries or Append Table Querires to dump the data in the
Access applications when necessary. We use a product called Total Visual
Agent which facilitates the scheduling of Access Macros. We use that to
update the data in the various applications over night, so the users
don't have to wait for the data to refresh. The performance of most
reports is a lot quicker than if you were linking to the live database.

Let me know if you'd like more details on how to use Access with
Vantage.

Brian Stenglein
Clow Stamping

--- In vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ,
"richter_clements" <richter_clements@...> wrote:
>
> We are on Vantage 6.1 and have not used the system for very much
reporting ever. We are now in the process of trying to create and
utilize much of the information that we input. I'm getting a good handle
on all the various tables and where information is stored. But now - I
need to link data and extract how to report on. I'm not sure the best
way to go about. I do have Crystal reports and can link to the LIVE
database through ODBC which leaves a lot of SQL type programming to be
done in Crystal (to link tables, filter data, etc...), or I guess create
BAQs to dump data into Excel, Access, etc and then manipulate from
there. I'm not familiar with SQL and so far I keep encountering system
crashes and such every time I try to design SQL querys (using Crystal
SQL Designer) and run them....
>
> Is this the best way to go about linking various tables to produce the
data I want? Or am I better off just dumping the tables through a BAQ to
create PC Level databases and running queries or programming from there?
I have a lot of previous experience with developing applications in
DBASE, Foxpro, etc...but nothing in SQL - I assume it's all the same
concepts just different terminology....
>
> Any suggestions? Are there various versions of SQL - so if I run out
to get some books on it I can at least learn the right one?
>
> Does the link between crystal to vantage use standard SQL statements?
>
> What about XML? I see the option in BAQ - to create an XML source....
>





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