• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 670
  • Last Modified:

Access ADP Stored Procedure and TransferText

Hi Everyone,

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?
  • 2
1 Solution
Kelvin SparksCommented:
When have struck this, I have changed the stored proc to a "Make Table" SP, run this passing the parameters, then used the transfer text method on the resultant table. Note: it also solves the problem of fields of a length greater than 255 characters being truncated.
BCTITechAuthor Commented:
Thanks for your response.  You're answer is what I was afraid of because that is what I was trying to avoid.  In this particular case, the data in a large table (55 fields in excess of 130,000 records - several fields are memo and multiple links) is needing to get e-mailed in several Excel spreadsheets.  There are over 30 queries involved because different people need to see different information. There is a constraint on time for the conversion and having to recode the queries and the application logic as well as incorporate deletion of the tables to free back up the space will eat up a lot of time so that is why I was hoping there was another way without having to use temp tables.
Kelvin SparksCommented:
Generally, when your Access database has got to that size, it is in need of some investment in time and effort. An adp is a good step, but they are very different to a mdb. My usual process is to move all the tables, then procedd form by form by importing each form and redeveloping the underlying processes. SPs offer a lot more functionality, but you have to relaise that you've handed a lot of the work to SQL Server and you need to rethink how you manage this. Much of the data never needs to leave the server, whereas with the mdb it has been clogging up your network. At the end of the day, if done properly, it can make an enormous difference.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now