Access ADP Stored Procedure and TransferText

Posted on 2005-05-16
Last Modified: 2013-12-05
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?
Question by:BCTITech
    LVL 22

    Accepted Solution

    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.

    Author Comment

    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.
    LVL 22

    Expert Comment

    by:Kelvin Sparks
    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Suggested Solutions

    This article is a continuation or rather an extension from Cascading Combos ( and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
    In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now