How do I to run an Informix stored procedure using MSAccess?

[mictip]

I use MSAccess to link to an informix db to run reports I create for the users who are given user logins to the informix db. I am not a programmer but can write basic SQL statements. I created an MSAccess query that has a number of parameters - some user input (from/to date) and some fixed. The fixed ones go against columns that are not indexed and it takes a long long time to run so the DBA is asking if I am able to execute a stored procedure in informix from within MSAccess. I have never done this and do not know where to start. Is this something I can do if he gives me the name of the stored procedure? I want to extract the data and create a report that displays it when the users run the report and input the date parameters...help!

[June7]

Does this help? I see reference to informix in the attempted code.
http://visualbasic.ittoolbox.com/gro...pletion-524050

[mictip]

Thank you for replying so fast. The link you gave me did not help as it shows VB programming lingo; I am not a programmer! In MSAccess view I currently have the following syntax:

SELECT informix_rtscaseh.case_number, informix_rtsprtyh.identifying_name, informix_rtsordeh.order_type_id, informix_rtsordeh.order_status, informix_rtsordeh.[total_ordered]-[total_applied] AS balance, informix_rtsordeh.order_create_date, informix_rtsordeh.filing_type_code, informix_rtsordeh.order_change_date, informix_rtscaspd.prob_type_code
FROM (((informix_rtsordeh INNER JOIN informix_rtscaseh ON informix_rtsordeh.case_id = informix_rtscaseh.case_id) INNER JOIN informix_rtsorpad ON (informix_rtsordeh.order_line_number = informix_rtsorpad.order_line_number) AND (informix_rtsordeh.case_id = informix_rtsorpad.case_id)) INNER JOIN informix_rtscaspd ON (informix_rtscaspd.case_id = informix_rtscaseh.case_id) AND (informix_rtsorpad.payer_party_id = informix_rtscaspd.party_id) AND (informix_rtsorpad.case_id = informix_rtscaspd.case_id)) INNER JOIN informix_rtsprtyh ON informix_rtscaspd.party_id = informix_rtsprtyh.party_id
WHERE (((informix_rtsordeh.order_status)="A") AND ((informix_rtsordeh.order_create_date) Between [Enter start date] And [Enter end date]) AND ((informix_rtsordeh.filing_type_code)="UNS") AND ((informix_rtscaspd.payer_role)="Y") AND ((informix_rtscaspd.prob_type_code)="P"));

What would I insert (other than the name of the stored procedure in Informix) in order to run the procedure so it will return the data?

[June7]

I don't really know, other than I doubt can do this from an Access query. I expect VBA will be required. VB and VBA are almost the same. What works in VB usually works in VBA.

What does the stored procedure do?

[orange]

See this link, it may be helpful
http://www.xtremevbtalk.com/showthread.php?t=98237

[Doug Lawry]

If you can't execute an Informix procedure directly, use the following trick:

SELECT * FROM TABLE(FUNCTION procedure(parameters)) AS alias (columns)

Regards,
Doug Lawry