Multiple Use of the Same Parameter, and Macros as well

[MC_FRx_99]

As an overall viewpoint: I'm using MS-Access as the "Data Bridge" to the MAS
200 Accounting system which uses the Providex Database.

What I want to do is run a series of queries from within Access which use
the same period. I don't want to have to enter this information more than
once as well.

I freely admit that I'm somewhat of a newbie when it comes to MS-Access.

In addition, I'd like to have the system run the following from just 1
click, and a selection of a Year & Period from a Combo box:

1. Clear out the Local Table Inventory Item Master
2. Update the Inventory Item Master from the Accounting System
3. Delete any records in the Activity Summary Table for the EP
(Entered Period Parameter)
4. Generate new records in the Activity Summary Table for the EP
5. Summarize Sales Activity for the EP
6. Update the Activity Summary Table for the Sales Activity in the EP
7. Summarize Accounts Payable Activity for the EP
8. Update the Activity Summary Table for the Purchasing Activity in the EP
9. Run a report showing the Profit by Item/Salesperson

I currently have queries that I've successfully designed for steps 2, 4, 5,
6, 7, 8

The issue is how to run them Automatically in Sequence using the same
parameter.

Another issue is that I've found that I have to enter my Company, User Name
and Password for each link back to the Accounting System. I'd certainly be
interested in avoiding this repetitive task.

I'm also going to most likely use Crystal Reports to create the report.

Please Note that due to issues of the system timing out, I'm also bringing
the data down to a local MS-Access table.

In addition, as of right now the ODBC Driver I'm using:
MAS 90 4.0 ODBC Driver
[pvxodbc.dll ver 4.10.1001] does NOT allow me to write-back to the database.

I'd be interested to see if others have been successful with another driver.

Thanks in advance for any help you can provide.

 

[MGFoster]

As an overall viewpoint: I'm using MS-Access as the "Data Bridge" to the MAS
200 Accounting system which uses the Providex Database.

What I want to do is run a series of queries from within Access which use
the same period. I don't want to have to enter this information more than
once as well.

I freely admit that I'm somewhat of a newbie when it comes to MS-Access.

In addition, I'd like to have the system run the following from just 1
click, and a selection of a Year & Period from a Combo box:

1. Clear out the Local Table Inventory Item Master
2. Update the Inventory Item Master from the Accounting System
3. Delete any records in the Activity Summary Table for the EP
(Entered Period Parameter)
4. Generate new records in the Activity Summary Table for the EP
5. Summarize Sales Activity for the EP
6. Update the Activity Summary Table for the Sales Activity in the EP
7. Summarize Accounts Payable Activity for the EP
8. Update the Activity Summary Table for the Purchasing Activity in the EP
9. Run a report showing the Profit by Item/Salesperson

I currently have queries that I've successfully designed for steps 2, 4, 5,
6, 7, 8

The issue is how to run them Automatically in Sequence using the same
parameter.

Another issue is that I've found that I have to enter my Company, User Name
and Password for each link back to the Accounting System. I'd certainly be
interested in avoiding this repetitive task.

I'm also going to most likely use Crystal Reports to create the report.

Please Note that due to issues of the system timing out, I'm also bringing
the data down to a local MS-Access table.

In addition, as of right now the ODBC Driver I'm using:
MAS 90 4.0 ODBC Driver
[pvxodbc.dll ver 4.10.1001] does NOT allow me to write-back to the database.

I'd be interested to see if others have been successful with another driver.

Thanks in advance for any help you can provide.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

It'd probably be best to use a VBA routine to run all the queries - use
a CommandButton OnClick event procedure.

You can set up a criteria form that holds the ComboBox for the
Year/Period and the CommandButton. The queries can read the Year/Period
from the ComboBox by using a form reference, syntax like this:

Forms!FormName!ComboBoxName

You'd put it in the SQL View of the query like this;

PARAMETERS Forms!FormName!ComboBoxName Text;
SELECT ... etc. ...

And use it in the query criteria like this:

....
WHERE some_column = Forms!FormName!ComboBoxName

Use whatever comparator expression you require. If you want to use the
query's Design Grid, set the Parameters using the menu item Query >
Parameters; set the criteria by putting the form reference in the
Criteria cell under the appropriate column.

1. To clear out a table use a DELETE query:

DELETE * FROM table_name

9. I'd use Access' report function to run the report instead of Crystal
Reports. You can run a report from the VBA routine using the
DoCmd.OpenReport method. Read the Access VBA Help article on the
OpenReport method for more info (how to read: Open the Debug window
[ctrl-G]; type in OpenReport; keep the cursor on the word & hit the F1
key. If you have the VBA Help files on your computer the article will
appear.).

Your connection to the Providex DB probably just needs the user ID &
password included in the connection string. Usually, in ODBC
connections, this is indicated in the string by parameters like this:

ODBC;DSN=Providex;User=harry;PWD=OpenSesame

This may not be correct, since I've never seen a Providex connection
string. This connection string is in the linked ODBC table's
Description property (open the table in Design View and click the
View > Properties menu item).

According to the Providex web site there are two versions of the ODBC
driver: one a stand-alone; the other a client/server version. The
stand-alone seems to be read-only and the client/server seems to be a
complete transactional driver (read/write). See:

http://www.pvx.com/products/#prod-odbc-details

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSc0SdYechKqOuFEgEQL+ugCeL9BM6NdLVZK2A4udDSGXluBTgxQAn21O
VXKDmD8CPncvA/zKH28sVVq2
=lMoW
-----END PGP SIGNATURE-----