Re: Weird SQL Question

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: TK (TK_at_discussions.microsoft.com)
Date: 11/16/04 

Date: Mon, 15 Nov 2004 16:37:01 -0800

Hi Jamie:

>Jamie wrote:
>This demonstrates that Jet (MS Access) also can/will distinguish
>stored procedures from other stored Query objects.

You have miss read my post:

I refered to an Access Query.
I refer to an Access Query as a stored object that returns a rs or
performs some action on the DB, a user defined procedure.
I refer to that same Server object as a store Procedure.
These terms are specific.
ADO returns a recordset
ADOMD returns a Cellset
ADO.Net returns a Dataset
and bla bla bla
You seem to use these terms interchangeable.

If you think they are try to enter:
.CommandType = adCmdStoredProc
to call an excess Query
or conversely
.CommandType = adCmdUnknown
to call a Server Store Procedure

ADO will promptly inform you of your error.

Further:

>This demonstrates that Jet (MS Access) also can/will distinguish
>stored procedures from other stored Query objects.

I agree.

The last article I read (ADO) OpenSchema actually had 31 QueryType
Methods however, providers are only required to support adSchemaTables
adSchemaColumns and adSchemaType by OLE DB specifications.

And yes I know there is something missing in a persons life
that would think this stuff is note worthy or interesting.

Good Luck
TK

"Jamie Collins" wrote:

> "TK" <TK@discussions.microsoft.com> wrote ...
>
> > The Schema can/will also show the stored Procedure (Server) stored
> > Queries (access).
>
> As you are an ADO advocate (plus I haven't had any caffeine since my
> espresso breakfast <g>) I won't post the VBA, but try the following
> against a ADO OLEDB connection to a Jet 4.0 .mdb (MS Access) database:
>
> CREATE TABLE MyTestTable (
> MyKeyCol INTEGER NOT NULL PRIMARY KEY,
> MyDataCol VARCHAR(255) NOT NULL
> )
> ;
> CREATE VIEW TestResults AS
> SELECT MyKeyCol, MyDataCol
> FROM MyTestTable
> ;
> CREATE PROCEDURE TestProc (
> KeyValue INTEGER
> ) AS
> SELECT MyKeyCol, MyDataCol
> FROM MyTestTable
> WHERE MyKeyCol = KeyValue
> ORDER BY MyKeyCol
> ;
>
> Then use the Connection object's OpenSchema object using adSchemaViews
> and you'll get a row for TestResults, then using adSchemaProcedures
> and you'll get a row for TestProc.
>
> This demonstrates that Jet (MS Access) also can/will distinguish
> stored procedures from other stored Query objects.
>
> Jamie.
>
> --
>