Stored procedure functionality and visibility questions

[Guest]

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?

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?

Any information is appreciated.
Thanks!

 

[John Nurick]

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.

 

[Guest]

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]

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).