Re: Stored procedure functionality and visibility questions

Hi Troy,

Like the other Office apps Access can be used as an Automation server,
so any OLE-aware application (or script) can fire up an instance of
Access, open a database and run queries, reports, etc. to its heart's
content (more or less).

The process is a little more complicated with Access than (say) Word or
Excel, and it's worth studying http://support.microsoft.com/?id=210111.
I have the impression that most of the time it's best to use DAO or ADO
to get at the data, and that the commonest real need for automating
Access is to use its reporting engine.

Whether it's worth doing in this case probably depends on how much of
the processing you need to do is internal to the Access database. If
there's a lot of data mangling which produces a small recordset for your
external application, doing it in Access might be best. Otherwise I'd be
inclined to use VBA+recordset via DAO (or ADO if you prefer).

On Tue, 30 Aug 2005 06:19:04 -0700, "Tspwi"
<Tspwi@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

>Thank you for the quick response and the confirmation of what I was suspecting.
>
>Could you explain a little what you mean by
>"have your external application automate Access."
>
>I may end up going the ADO recordset route but I would like to make sure
>I've considered any other options first.
>
>Thanks,
>Troy
>
>
>"John Nurick" wrote:
>
>> On Mon, 29 Aug 2005 09:25:01 -0700, "Tspwi"
>> <Tspwi@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
>>
>> >I've recently been working on a project that uses a stored procedure to
>> >perform some complex query processing along with some manipulation of the
>> >results using T-SQL and PL/SQL. I would like to be able to support the same
>> >thing with an Access database but I haven't been able to find the answers to
>> >these two questions and I'm hoping someone more familiar with Access
>> >development can help.
>> >
>> >1. Can a "stored procedure" in Access be anything more than a single SQL
>> >statement? Is anything like T-SQL supported?
>> >The only samples I can find are using single SQL queries like
>> >Create Procedure myProc (prmCity varchar) AS SELECT * FROM Customers WHERE
>> >City = prmCity
>> >but I also keep reading about additional stored procedure support that was
>> >added in Access 2000. Is this what that is referring to?
>>
>> As far as I know that's the case. The Jet SQL documentation is clear
>> that CREATE PROCEDURE creates a stored procedure containing a single SQL
>> statement.
>>
>> >2. I've looked into using a VBA function or procedure to perform the task
>> >but I haven't been able to find any information to confirm that calling the
>> >function or procedure would be possible from an application that isn't an
>> >Office application. I need to have a function that can be called by another
>> >application through ADO. Is that possible?
>>
>> No. Custom VBA functions in queries are handled by Access's "expression
>> service" and not available when you're working with ADO or DAO.
>>
>> One possibility - if you have to use Jet and not a database engine that
>> offers "proper" stored procedures - might be to have your external
>> application automate Access. Alternatively, use ADO recordset operations
>> to access and manipulate the data from the external app.
>>
>> --
>> John Nurick [Microsoft Access MVP]
>>
>> Please respond in the newgroup and not by email.
>>
>>

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

.

Relevant Pages

  • Re: SQL Server stored prcedures with output parameters
    ... the stored procedure below appears to consistently ... > moment) how ADO handles that. ... Parameter 1 had direction input ... Appending parameter value http://www.holdenweb.com/ ...
    (comp.lang.python)
  • Re: Stored Procedure is running slow!
    ... > I run stored procedure from QA, it takes some 15 seconds to execute. ... > seconds when called from MS Fox Pro, via ADO. ... Try this in Query Analyzer: ... By default Query Analyzer runs with ARITHABORT ON, ...
    (microsoft.public.sqlserver.programming)
  • Re: Very bad response time from stored procedure call via ADO
    ... We have a stored procedure that returns some statistical information ... SQL Server is version 2000 ... When run in SQL Query Analyser the typical duration is 400ms. ... When run from the VB6 application via ADO the duration increases to ...
    (microsoft.public.data.ado)
  • Re: Returning results from a temporary table using ADO
    ... > I 'm trying to invoke a stored procedure using ADO from a web page. ... > The error manifest itself as an empty recordset or a closed recordset ... > creates and populates a temporary table and to have the results of that ...
    (microsoft.public.vb.database.ado)
  • Re: How can a recordset returned by a sproc be updated?
    ... With the resultset metadata is returned to the client that allows ADO and ... SQL Server MVP ... > ADO. ... I tried making a view and having the stored procedure select ...
    (microsoft.public.sqlserver.programming)