Access ADP Stored Procedure and TransferText
Posted on 2005-05-16
We have an Access database that was converted to an Access data project (ADP). An existing query accepted parameters whose values were taken from a form, like:
SELECT * FROM salespeople WHERE id = Forms!frmSales!cboSalesPerson
This query was then used in a TransferText statement to output the results to an Excel file.
Here is the situation, we can recreate the query as a parameterized stored procedure but we cannot use the Forms! syntax for the parameters. In the end, the stored procedure is something more like:
SELECT * FROM salespeople WHERE id = @SalesPersonID
We need to be able to pass the procedure name to the TransferText method, but there doesn't seem to be a way to pass parameters using TransferText. It would be nice to use something like TransferText "spMySPName", Forms!frmSales!cboSalesPerson or something similar.
We also looked at using DoCmd.OutputTo but that also doesn't seem to accept parameters.
Is there a way to accomplish this without resorting to creating ADODB objects then coding the export?