Linking Access to a .CSV file (exported from Vantag e) (WAS: Pr

Never have. I was beginning to use it a bit in a test environment, but in
light of recent discoveries, I probably never will.

As you said, retrieving data from the db to the workstation is much, much
faster with 4GL Code than with ODBC, or even Report Builder. The only
reason I ever considered using ODBC was to be able to access Vantage data
with Crystal and visual basic.

With the recently posted export code, and CSV-Access linking methods, I can
get data to a crystal friendly format in less than 10 lines of 4GL, and 2
minutes of linking. So I spend an additional 5 minutes per simple report,
and in many cases the payoff is hours per week in execution time.

I'll post my first example in the next couple of days, once I get a chance
to test it and everything.

Thaddeus


-----Original Message-----
From: Troy Funte [mailto:tfunte@...]
Sent: Wednesday, January 16, 2002 4:18 PM
To: vantage@yahoogroups.com
Subject: Re: [Vantage] Linking Access to a .CSV file (exported from
Vantage) (WAS: Progress/dbf)


Do you not use ODBC at all then?

Troy
----- Original Message -----
From: Thad Jacobs
To: 'vantage@yahoogroups.com'
Sent: Wednesday, January 16, 2002 7:16 PM
Subject: RE: [Vantage] Linking Access to a .CSV file (exported from
Vantage) (WAS: Progress/dbf)


In alot of cases, especially with aggregate accounting reports, a day old
is
fine.

But in some cases, such as a just-entered BOM, a shortage list that is
being
reprinted to verify that a shortage has been eliminated, a bag label for a
part that has just been issued in vantage and needs to be handed to
someone
waiting at the shipping gate, real-time data is needed.

Now that I think about it more, until setting up reports on users machines
becomes a real bottleneck, I probably won't even look at the web component
server for use with real-time reports.

Thaddeus

-----Original Message-----
From: Troy Funte [mailto:tfunte@...]
Sent: Wednesday, January 16, 2002 4:01 PM
To: vantage@yahoogroups.com
Subject: Re: [Vantage] Linking Access to a .CSV file (exported from
Vantage) (WAS: Progress/dbf)


Is your concern with the file being locked that a user may try to access a
report on the WCS at the same time as the export is happening? You must
want to do multiple exports during a day. In our case, the export happens
overnight when no one is on. I've never had a user need more current data
than that on our few exported reports. In your case, if you're talking
about exporting ALL of your reports (to eliminate ODBC completely), then I
could see where you may need to work out the locking files situation.

Out of curiousity... What kind of reports are you wanting to deploy this
way
(via export-access-crystal on each machine)?

Troy Funte
Liberty Electronics

----- Original Message -----
From: Thad Jacobs
To: 'vantage@yahoogroups.com'
Sent: Wednesday, January 16, 2002 6:47 PM
Subject: RE: [Vantage] Linking Access to a .CSV file (exported from
Vantage) (WAS: Progress/dbf)


The approach I'm going to be using is probably this:

1)write a progress program that exports necessary records to CSV file(s)
in
the vntgwrk directory, copies the linked access database to the vntgwrk
directory, and launches an EXE file.
2) the EXE file will be a crystal report bundled in a visual basic
application, in some cases the application will contain multiple
reports,
and code to export to Excel.
3) The crystal report will be connected to the linked Access-CSV query.


The above approach allows me to use a pretty minimal amount of code for
simple reports, though if I want, I have the flexibility of manipulating
records in progress or visual basic before the crystal report is run.

With this approach, though, comes deployment issues. You have to setup
at
least one bundled report on every machine so the user has the
appropriate
dll's. I've run into problems with this when distributing report
packages
over multiple operating systems. We've just made the determination that
all
report users have windows 2000, so distribution shouldn't be too
difficult,
though installing on every machine is a step worth eliminating.

If in the CSV export code there was a way to wait until there no lock on
the
file to export it, and some sort of way to tell crystal component web
server
to wait until the export is finished before refreshing, you could then
safely have a crystal-access-CSV report deployed over the crystal
component
web server for multi-user use.

Then, for my intents and purposes, OBDC would be history.

I would appreciate any questions or comments on my reporting methods, as
I
am trying to improve them to be as programmatically flexible and
efficient
as possible, as well as cost effective.

~Thaddeus


-----Original Message-----
From: Troy Funte [mailto:tfunte@...]
Sent: Wednesday, January 16, 2002 3:15 PM
To: vantage@yahoogroups.com
Subject: Re: [Vantage] Linking Access to a .CSV file (exported from
Vantage) (WAS: Progress/dbf)


Thad,
I just did a quick check. With Crystal Opened and viewing data, I
can
open the .csv file in Excel with no problem. However, with the .csv
file
open in Excel, I can't refresh the Crystal report until Excel let's go
of
the file.

So, Excel definitely locks the file. I would think that Crystal locks
the
file only while it is reading the file to refresh, and then releases the
file.

Troy
----- Original Message -----
From: Thad Jacobs
To: 'vantage@yahoogroups.com'
Sent: Wednesday, January 16, 2002 5:35 PM
Subject: RE: [Vantage] Linking Access to a .CSV file (exported from
Vantage) (WAS: Progress/dbf)


Troy,

If a user has one of these Crystal-Access-CSV reports opened, is the
CSV
file locked in anyway, based on your experience?

Thaddeus

-----Original Message-----
From: Troy Funte [mailto:tfunte@...]
Sent: Wednesday, January 16, 2002 2:24 PM
To: vantage@yahoogroups.com
Subject: [Vantage] Linking Access to a .CSV file (exported from
Vantage)
(WAS: Progress/dbf)


There are several steps to linking Access to a .CSV file and running a
Crystal Report against it. Once set up, however, you can simply
export
the
.CSV to the same file name, and then run the Crystal Report. (NOTE:
This
was all done in Access 2000. I can't vouch for other versions of
Access
and
how they handle long fields like "PartDescription").

1. Export the .csv file. I'll assume it's called
"c:\vntgwork\PO.CSV".
2. Open Access to a new blank database and give it a meaningful name
and
path.
A. Click File/Get External Data> /Link Tables...
B. Select File type "Text Files (*.txt;*.csv;*.tab;*.asc), then
find
"c:\vntgwork\po.csv", click Link.
C. Walk through the wizard. If you put Field names at the top,
check
that box.. etc. Click Next, Click Finish.
D. With the new PO table selected, Click the Queries Button.
E. Click "Create Query by using Wizard"
F. Select all fields show (if desired), then click Next, Next,
Finish
(if you like the name offered "po query".
3. Open Crystal Reports.
A. Click on File/New/ ... As Blank Report
B. In Data Explorer, Click Database Files.
-- If you see your Access File ?.mdb, then double-click.
-- If you don't see your Access File, click "Find Database
File"
and
find it.
C. Once you double-click on your file, you should see the "PO
Query"
that you created in Access.
D. Select the query just like you would a Vantage table. Click
Add.
Click Close.
E. You can link multiple queries from other Access tables if
desired.
F. You should now see the fields in the table just as you do with
a
Vantage Table.
G. Create your report.

NOTE: By update your .csv file, your Crystal Report will
automatically
see
the new data the next time you refresh it.

Have Fun.

Troy Funte
Liberty Electronics

----- Original Message -----
From: Thad Jacobs
To: 'vantage@yahoogroups.com'
Sent: Wednesday, January 16, 2002 4:05 PM
Subject: RE: [Vantage] Progress/dbf


>Let me know if you want to see how to do it.

I'm all ears!

-----Original Message-----
From: Troy Funte [mailto:tfunte@...]
Sent: Wednesday, January 16, 2002 12:48 PM
To: vantage@yahoogroups.com
Subject: Re: [Vantage] Progress/dbf


I'm not sure what you are hoping to do, but I link .csv files in
Access
all
the time, and then run Crystal Reports off the .csv. We also have
an
extensive Excel-based program that runs off our .csv files
automatically.
Let me know if you want to see how to do it. We just update the
.csv
files
nighty using the progress export like you are and Crystal is ready
to
be
run
off the Access database that is Linked to the .csv files.
These reports run much faster than through ODBC/Progress, which is
why
we're
doing it. Speed. (Translated seconds compared to hours).

Troy Funte
Liberty Electronics

----- Original Message -----
From: lptingley
To: vantage@yahoogroups.com
Sent: Wednesday, January 16, 2002 12:29 PM
Subject: [Vantage] Progress/dbf


This 4GL coding newbie was hoping that changing my exports to dbf
from csv would be as simple as changing the file extensions. Sure.

I've found some information in the Files section on Progress and
dbf,
but I'm relatively sure that I'm not ready for that. Second
"stretch"
of the day. Is there a way to setup a .P file that will run an
export
that was created using the export utility? These dbf files are to
be

linked to MS Access. I don't want to use ODBC, and I don't want
the
end user to have access to the export utility. Any words of
wisdom?

Thanks,

Les Tingley
MIS
Decorated Products


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]



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/


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]


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]



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/
Troy,

If a user has one of these Crystal-Access-CSV reports opened, is the CSV
file locked in anyway, based on your experience?

Thaddeus

-----Original Message-----
From: Troy Funte [mailto:tfunte@...]
Sent: Wednesday, January 16, 2002 2:24 PM
To: vantage@yahoogroups.com
Subject: [Vantage] Linking Access to a .CSV file (exported from Vantage)
(WAS: Progress/dbf)


There are several steps to linking Access to a .CSV file and running a
Crystal Report against it. Once set up, however, you can simply export the
.CSV to the same file name, and then run the Crystal Report. (NOTE: This
was all done in Access 2000. I can't vouch for other versions of Access and
how they handle long fields like "PartDescription").

1. Export the .csv file. I'll assume it's called "c:\vntgwork\PO.CSV".
2. Open Access to a new blank database and give it a meaningful name and
path.
A. Click File/Get External Data> /Link Tables...
B. Select File type "Text Files (*.txt;*.csv;*.tab;*.asc), then find
"c:\vntgwork\po.csv", click Link.
C. Walk through the wizard. If you put Field names at the top, check
that box.. etc. Click Next, Click Finish.
D. With the new PO table selected, Click the Queries Button.
E. Click "Create Query by using Wizard"
F. Select all fields show (if desired), then click Next, Next, Finish
(if you like the name offered "po query".
3. Open Crystal Reports.
A. Click on File/New/ ... As Blank Report
B. In Data Explorer, Click Database Files.
-- If you see your Access File ?.mdb, then double-click.
-- If you don't see your Access File, click "Find Database File" and
find it.
C. Once you double-click on your file, you should see the "PO Query"
that you created in Access.
D. Select the query just like you would a Vantage table. Click Add.
Click Close.
E. You can link multiple queries from other Access tables if desired.
F. You should now see the fields in the table just as you do with a
Vantage Table.
G. Create your report.

NOTE: By update your .csv file, your Crystal Report will automatically see
the new data the next time you refresh it.

Have Fun.

Troy Funte
Liberty Electronics

----- Original Message -----
From: Thad Jacobs
To: 'vantage@yahoogroups.com'
Sent: Wednesday, January 16, 2002 4:05 PM
Subject: RE: [Vantage] Progress/dbf


>Let me know if you want to see how to do it.

I'm all ears!

-----Original Message-----
From: Troy Funte [mailto:tfunte@...]
Sent: Wednesday, January 16, 2002 12:48 PM
To: vantage@yahoogroups.com
Subject: Re: [Vantage] Progress/dbf


I'm not sure what you are hoping to do, but I link .csv files in Access
all
the time, and then run Crystal Reports off the .csv. We also have an
extensive Excel-based program that runs off our .csv files automatically.
Let me know if you want to see how to do it. We just update the .csv
files
nighty using the progress export like you are and Crystal is ready to be
run
off the Access database that is Linked to the .csv files.
These reports run much faster than through ODBC/Progress, which is why
we're
doing it. Speed. (Translated seconds compared to hours).

Troy Funte
Liberty Electronics

----- Original Message -----
From: lptingley
To: vantage@yahoogroups.com
Sent: Wednesday, January 16, 2002 12:29 PM
Subject: [Vantage] Progress/dbf


This 4GL coding newbie was hoping that changing my exports to dbf
from csv would be as simple as changing the file extensions. Sure.
I've found some information in the Files section on Progress and dbf,
but I'm relatively sure that I'm not ready for that. Second "stretch"
of the day. Is there a way to setup a .P file that will run an export
that was created using the export utility? These dbf files are to be
linked to MS Access. I don't want to use ODBC, and I don't want the
end user to have access to the export utility. Any words of wisdom?

Thanks,

Les Tingley
MIS
Decorated Products


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]



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/
The approach I'm going to be using is probably this:

1)write a progress program that exports necessary records to CSV file(s) in
the vntgwrk directory, copies the linked access database to the vntgwrk
directory, and launches an EXE file.
2) the EXE file will be a crystal report bundled in a visual basic
application, in some cases the application will contain multiple reports,
and code to export to Excel.
3) The crystal report will be connected to the linked Access-CSV query.


The above approach allows me to use a pretty minimal amount of code for
simple reports, though if I want, I have the flexibility of manipulating
records in progress or visual basic before the crystal report is run.

With this approach, though, comes deployment issues. You have to setup at
least one bundled report on every machine so the user has the appropriate
dll's. I've run into problems with this when distributing report packages
over multiple operating systems. We've just made the determination that all
report users have windows 2000, so distribution shouldn't be too difficult,
though installing on every machine is a step worth eliminating.

If in the CSV export code there was a way to wait until there no lock on the
file to export it, and some sort of way to tell crystal component web server
to wait until the export is finished before refreshing, you could then
safely have a crystal-access-CSV report deployed over the crystal component
web server for multi-user use.

Then, for my intents and purposes, OBDC would be history.

I would appreciate any questions or comments on my reporting methods, as I
am trying to improve them to be as programmatically flexible and efficient
as possible, as well as cost effective.

~Thaddeus


-----Original Message-----
From: Troy Funte [mailto:tfunte@...]
Sent: Wednesday, January 16, 2002 3:15 PM
To: vantage@yahoogroups.com
Subject: Re: [Vantage] Linking Access to a .CSV file (exported from
Vantage) (WAS: Progress/dbf)


Thad,
I just did a quick check. With Crystal Opened and viewing data, I can
open the .csv file in Excel with no problem. However, with the .csv file
open in Excel, I can't refresh the Crystal report until Excel let's go of
the file.

So, Excel definitely locks the file. I would think that Crystal locks the
file only while it is reading the file to refresh, and then releases the
file.

Troy
----- Original Message -----
From: Thad Jacobs
To: 'vantage@yahoogroups.com'
Sent: Wednesday, January 16, 2002 5:35 PM
Subject: RE: [Vantage] Linking Access to a .CSV file (exported from
Vantage) (WAS: Progress/dbf)


Troy,

If a user has one of these Crystal-Access-CSV reports opened, is the CSV
file locked in anyway, based on your experience?

Thaddeus

-----Original Message-----
From: Troy Funte [mailto:tfunte@...]
Sent: Wednesday, January 16, 2002 2:24 PM
To: vantage@yahoogroups.com
Subject: [Vantage] Linking Access to a .CSV file (exported from Vantage)
(WAS: Progress/dbf)


There are several steps to linking Access to a .CSV file and running a
Crystal Report against it. Once set up, however, you can simply export
the
.CSV to the same file name, and then run the Crystal Report. (NOTE: This
was all done in Access 2000. I can't vouch for other versions of Access
and
how they handle long fields like "PartDescription").

1. Export the .csv file. I'll assume it's called "c:\vntgwork\PO.CSV".
2. Open Access to a new blank database and give it a meaningful name and
path.
A. Click File/Get External Data> /Link Tables...
B. Select File type "Text Files (*.txt;*.csv;*.tab;*.asc), then find
"c:\vntgwork\po.csv", click Link.
C. Walk through the wizard. If you put Field names at the top, check
that box.. etc. Click Next, Click Finish.
D. With the new PO table selected, Click the Queries Button.
E. Click "Create Query by using Wizard"
F. Select all fields show (if desired), then click Next, Next, Finish
(if you like the name offered "po query".
3. Open Crystal Reports.
A. Click on File/New/ ... As Blank Report
B. In Data Explorer, Click Database Files.
-- If you see your Access File ?.mdb, then double-click.
-- If you don't see your Access File, click "Find Database File"
and
find it.
C. Once you double-click on your file, you should see the "PO Query"
that you created in Access.
D. Select the query just like you would a Vantage table. Click Add.
Click Close.
E. You can link multiple queries from other Access tables if desired.
F. You should now see the fields in the table just as you do with a
Vantage Table.
G. Create your report.

NOTE: By update your .csv file, your Crystal Report will automatically
see
the new data the next time you refresh it.

Have Fun.

Troy Funte
Liberty Electronics

----- Original Message -----
From: Thad Jacobs
To: 'vantage@yahoogroups.com'
Sent: Wednesday, January 16, 2002 4:05 PM
Subject: RE: [Vantage] Progress/dbf


>Let me know if you want to see how to do it.

I'm all ears!

-----Original Message-----
From: Troy Funte [mailto:tfunte@...]
Sent: Wednesday, January 16, 2002 12:48 PM
To: vantage@yahoogroups.com
Subject: Re: [Vantage] Progress/dbf


I'm not sure what you are hoping to do, but I link .csv files in Access
all
the time, and then run Crystal Reports off the .csv. We also have an
extensive Excel-based program that runs off our .csv files
automatically.
Let me know if you want to see how to do it. We just update the .csv
files
nighty using the progress export like you are and Crystal is ready to be
run
off the Access database that is Linked to the .csv files.
These reports run much faster than through ODBC/Progress, which is why
we're
doing it. Speed. (Translated seconds compared to hours).

Troy Funte
Liberty Electronics

----- Original Message -----
From: lptingley
To: vantage@yahoogroups.com
Sent: Wednesday, January 16, 2002 12:29 PM
Subject: [Vantage] Progress/dbf


This 4GL coding newbie was hoping that changing my exports to dbf
from csv would be as simple as changing the file extensions. Sure.
I've found some information in the Files section on Progress and dbf,
but I'm relatively sure that I'm not ready for that. Second "stretch"
of the day. Is there a way to setup a .P file that will run an export
that was created using the export utility? These dbf files are to be
linked to MS Access. I don't want to use ODBC, and I don't want the
end user to have access to the export utility. Any words of wisdom?

Thanks,

Les Tingley
MIS
Decorated Products


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]



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/


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/
In alot of cases, especially with aggregate accounting reports, a day old is
fine.

But in some cases, such as a just-entered BOM, a shortage list that is being
reprinted to verify that a shortage has been eliminated, a bag label for a
part that has just been issued in vantage and needs to be handed to someone
waiting at the shipping gate, real-time data is needed.

Now that I think about it more, until setting up reports on users machines
becomes a real bottleneck, I probably won't even look at the web component
server for use with real-time reports.

Thaddeus

-----Original Message-----
From: Troy Funte [mailto:tfunte@...]
Sent: Wednesday, January 16, 2002 4:01 PM
To: vantage@yahoogroups.com
Subject: Re: [Vantage] Linking Access to a .CSV file (exported from
Vantage) (WAS: Progress/dbf)


Is your concern with the file being locked that a user may try to access a
report on the WCS at the same time as the export is happening? You must
want to do multiple exports during a day. In our case, the export happens
overnight when no one is on. I've never had a user need more current data
than that on our few exported reports. In your case, if you're talking
about exporting ALL of your reports (to eliminate ODBC completely), then I
could see where you may need to work out the locking files situation.

Out of curiousity... What kind of reports are you wanting to deploy this way
(via export-access-crystal on each machine)?

Troy Funte
Liberty Electronics

----- Original Message -----
From: Thad Jacobs
To: 'vantage@yahoogroups.com'
Sent: Wednesday, January 16, 2002 6:47 PM
Subject: RE: [Vantage] Linking Access to a .CSV file (exported from
Vantage) (WAS: Progress/dbf)


The approach I'm going to be using is probably this:

1)write a progress program that exports necessary records to CSV file(s)
in
the vntgwrk directory, copies the linked access database to the vntgwrk
directory, and launches an EXE file.
2) the EXE file will be a crystal report bundled in a visual basic
application, in some cases the application will contain multiple reports,
and code to export to Excel.
3) The crystal report will be connected to the linked Access-CSV query.


The above approach allows me to use a pretty minimal amount of code for
simple reports, though if I want, I have the flexibility of manipulating
records in progress or visual basic before the crystal report is run.

With this approach, though, comes deployment issues. You have to setup at
least one bundled report on every machine so the user has the appropriate
dll's. I've run into problems with this when distributing report packages
over multiple operating systems. We've just made the determination that
all
report users have windows 2000, so distribution shouldn't be too
difficult,
though installing on every machine is a step worth eliminating.

If in the CSV export code there was a way to wait until there no lock on
the
file to export it, and some sort of way to tell crystal component web
server
to wait until the export is finished before refreshing, you could then
safely have a crystal-access-CSV report deployed over the crystal
component
web server for multi-user use.

Then, for my intents and purposes, OBDC would be history.

I would appreciate any questions or comments on my reporting methods, as I
am trying to improve them to be as programmatically flexible and efficient
as possible, as well as cost effective.

~Thaddeus


-----Original Message-----
From: Troy Funte [mailto:tfunte@...]
Sent: Wednesday, January 16, 2002 3:15 PM
To: vantage@yahoogroups.com
Subject: Re: [Vantage] Linking Access to a .CSV file (exported from
Vantage) (WAS: Progress/dbf)


Thad,
I just did a quick check. With Crystal Opened and viewing data, I can
open the .csv file in Excel with no problem. However, with the .csv file
open in Excel, I can't refresh the Crystal report until Excel let's go of
the file.

So, Excel definitely locks the file. I would think that Crystal locks the
file only while it is reading the file to refresh, and then releases the
file.

Troy
----- Original Message -----
From: Thad Jacobs
To: 'vantage@yahoogroups.com'
Sent: Wednesday, January 16, 2002 5:35 PM
Subject: RE: [Vantage] Linking Access to a .CSV file (exported from
Vantage) (WAS: Progress/dbf)


Troy,

If a user has one of these Crystal-Access-CSV reports opened, is the CSV
file locked in anyway, based on your experience?

Thaddeus

-----Original Message-----
From: Troy Funte [mailto:tfunte@...]
Sent: Wednesday, January 16, 2002 2:24 PM
To: vantage@yahoogroups.com
Subject: [Vantage] Linking Access to a .CSV file (exported from Vantage)
(WAS: Progress/dbf)


There are several steps to linking Access to a .CSV file and running a
Crystal Report against it. Once set up, however, you can simply export
the
.CSV to the same file name, and then run the Crystal Report. (NOTE:
This
was all done in Access 2000. I can't vouch for other versions of Access
and
how they handle long fields like "PartDescription").

1. Export the .csv file. I'll assume it's called "c:\vntgwork\PO.CSV".
2. Open Access to a new blank database and give it a meaningful name and
path.
A. Click File/Get External Data> /Link Tables...
B. Select File type "Text Files (*.txt;*.csv;*.tab;*.asc), then find
"c:\vntgwork\po.csv", click Link.
C. Walk through the wizard. If you put Field names at the top,
check
that box.. etc. Click Next, Click Finish.
D. With the new PO table selected, Click the Queries Button.
E. Click "Create Query by using Wizard"
F. Select all fields show (if desired), then click Next, Next,
Finish
(if you like the name offered "po query".
3. Open Crystal Reports.
A. Click on File/New/ ... As Blank Report
B. In Data Explorer, Click Database Files.
-- If you see your Access File ?.mdb, then double-click.
-- If you don't see your Access File, click "Find Database File"
and
find it.
C. Once you double-click on your file, you should see the "PO
Query"
that you created in Access.
D. Select the query just like you would a Vantage table. Click Add.
Click Close.
E. You can link multiple queries from other Access tables if
desired.
F. You should now see the fields in the table just as you do with a
Vantage Table.
G. Create your report.

NOTE: By update your .csv file, your Crystal Report will automatically
see
the new data the next time you refresh it.

Have Fun.

Troy Funte
Liberty Electronics

----- Original Message -----
From: Thad Jacobs
To: 'vantage@yahoogroups.com'
Sent: Wednesday, January 16, 2002 4:05 PM
Subject: RE: [Vantage] Progress/dbf


>Let me know if you want to see how to do it.

I'm all ears!

-----Original Message-----
From: Troy Funte [mailto:tfunte@...]
Sent: Wednesday, January 16, 2002 12:48 PM
To: vantage@yahoogroups.com
Subject: Re: [Vantage] Progress/dbf


I'm not sure what you are hoping to do, but I link .csv files in
Access
all
the time, and then run Crystal Reports off the .csv. We also have an
extensive Excel-based program that runs off our .csv files
automatically.
Let me know if you want to see how to do it. We just update the .csv
files
nighty using the progress export like you are and Crystal is ready to
be
run
off the Access database that is Linked to the .csv files.
These reports run much faster than through ODBC/Progress, which is why
we're
doing it. Speed. (Translated seconds compared to hours).

Troy Funte
Liberty Electronics

----- Original Message -----
From: lptingley
To: vantage@yahoogroups.com
Sent: Wednesday, January 16, 2002 12:29 PM
Subject: [Vantage] Progress/dbf


This 4GL coding newbie was hoping that changing my exports to dbf
from csv would be as simple as changing the file extensions. Sure.
I've found some information in the Files section on Progress and
dbf,
but I'm relatively sure that I'm not ready for that. Second
"stretch"
of the day. Is there a way to setup a .P file that will run an
export
that was created using the export utility? These dbf files are to be

linked to MS Access. I don't want to use ODBC, and I don't want the
end user to have access to the export utility. Any words of wisdom?

Thanks,

Les Tingley
MIS
Decorated Products


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]



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/


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]


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/