There is another option in Access.
Like Jasper says, if you are using Excel to query Vantage, then your
ODBC drivers should be already set up on your pc.
Creating Linked tables in Microsoft Access is one option; but, if you
instead use Microsoft Access Pass-Through Queries, then you will be
pulling ONLY exactly the limited amount of data that you need into
your SQL Query instead of all data for all records.
Here is the method to set up a Pass-through query:
1. Open Microsoft Access.
2. Go to Queries Tab
3. New Query
4. Design View (don't use the wizards.)
5. Close the selection window for tables & queries. You don't need
it because you will use SQL to make your selections.
6. On the View menu --> select SQL View (You will now be looking at
a SQL Select Statement.)
7. On the Query menu --> select SQL Specific --> then select Pass
Through
8. Click on Properties and type your ODBC Connect String. Typing in
the string complete with password will save you from the password
prompt each time you run the query. Here is an example of the
syntax for an ODBC Connect String:
ODBC;DSN=your_dsn_name;HOST=your_file_server_name;PORT=portname;DB=dbn
ame;UID=SYSPROGRESS;PWD=password
9. Set the ODBC Timeout to 0.
10. Start by writing a very simple SQL query to test your ODBC
connection and syntax. Write something with only a couple of fields
like this:
SELECT pub.tablename."fieldname1", pub.tablename."fieldname2"
FROM pub.tablename
WHERE pub.tablename."fieldname" = 'constant'
After you get some pass-through queries working, you may find you
need to perform more complicated tasks upon the query data. You can
use simple Access Select Queries and Reports to create more
complicated calculations BASED UPON the Pass-Through Queries you have
written.
Simply treat any of your Pass-Through Queries as though they were an
ordinary Access Table.
When you run any Microsoft Access simple query or Access Report or
Microsoft Excel file which is based upon the Pass-through queries,
the Pass-through query is refreshed and run by the Access or Excel
object which is calling it without you needing to refresh it
separately.
Hope this is of more help.
Lynn
Like Jasper says, if you are using Excel to query Vantage, then your
ODBC drivers should be already set up on your pc.
Creating Linked tables in Microsoft Access is one option; but, if you
instead use Microsoft Access Pass-Through Queries, then you will be
pulling ONLY exactly the limited amount of data that you need into
your SQL Query instead of all data for all records.
Here is the method to set up a Pass-through query:
1. Open Microsoft Access.
2. Go to Queries Tab
3. New Query
4. Design View (don't use the wizards.)
5. Close the selection window for tables & queries. You don't need
it because you will use SQL to make your selections.
6. On the View menu --> select SQL View (You will now be looking at
a SQL Select Statement.)
7. On the Query menu --> select SQL Specific --> then select Pass
Through
8. Click on Properties and type your ODBC Connect String. Typing in
the string complete with password will save you from the password
prompt each time you run the query. Here is an example of the
syntax for an ODBC Connect String:
ODBC;DSN=your_dsn_name;HOST=your_file_server_name;PORT=portname;DB=dbn
ame;UID=SYSPROGRESS;PWD=password
9. Set the ODBC Timeout to 0.
10. Start by writing a very simple SQL query to test your ODBC
connection and syntax. Write something with only a couple of fields
like this:
SELECT pub.tablename."fieldname1", pub.tablename."fieldname2"
FROM pub.tablename
WHERE pub.tablename."fieldname" = 'constant'
After you get some pass-through queries working, you may find you
need to perform more complicated tasks upon the query data. You can
use simple Access Select Queries and Reports to create more
complicated calculations BASED UPON the Pass-Through Queries you have
written.
Simply treat any of your Pass-Through Queries as though they were an
ordinary Access Table.
When you run any Microsoft Access simple query or Access Report or
Microsoft Excel file which is based upon the Pass-through queries,
the Pass-through query is refreshed and run by the Access or Excel
object which is calling it without you needing to refresh it
separately.
Hope this is of more help.
Lynn
--- In vantage@yahoogroups.com, Jasper Recto <jrecto@...> wrote:
>
> First, make sure you have your ODBC set up. If your using excel to
query Vantage you should be all set.
>
> Second, in Access go to INSERT > TABLE and choose LINK TABLE for
the option.
> You should get a window to select a file. For the File Type,
select ODBC.
>
> You should get a Select Data Source window.
>
> Pick you ODBC that you created earlier.
>
> It will ask for the Vantage userid and password. This is the ODBC
userid and password. By default the userid is sysprogress.
>
> Once you enter that in, the list of Vantage tables will pop up and
you just need to pick the ones you want.
>
> One suggestion is that when you pick the tables, select the 'Save
Password' option. This way it won't prompt you to enter in the
password everytime you open up Access.
>
> Good Luck,
>
> Jasper
>
>
> -----Original Message-----
> From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com]On
Behalf Of bbelzer42
> Sent: Wednesday, June 04, 2008 6:05 PM
> To: vantage@yahoogroups.com
> Subject: [Vantage] Microsoft Access
>
>
>
> Hello,
>
> I was wondering if anyone has a quick tutorial on linking microsoft
> access to the progress database.
>
> I currently use excel queries, but I am looking for a way to
automate
> an email every morning with previous day's activities, and I came
> across software that will email a report from access.
>
> I've never used access but I have about 500+ reports/queries through
> excel showing me almost anything I can think of from the vantage
> database... I'm just not able to automate a report yet.
>
> Thanks in advance,
> Ben
>
>
>
>
>
>
>
> [Non-text portions of this message have been removed]
>