Creating and using Stored Procedures and Views in Jet

[billmiami2]

Perhaps many of you MS Access fanatics already know this, but it seems
that stored procedures and views are possible in Jet. I thought I
would leave this message just in case it would help anyone.

I discovered this the other day while doing some experiments with ADO
and ADO.NET. Basically, I wanted to run a stored MS Access query with
parameters using the syntax

Execute MyProcedure @Param1, @Param2...

I knew that I could run a stored Access query by creating ADODB
parameters, assigning values and executing as if I were running a
stored procedure. However, the above Execute syntax above also worked
perfectly to my surprise.

I later ran the code below from a Module in a simple Access database
with one table called Ranges with two columns (Range_ID integer,
Range_No varchar(50)). I expected to see the procedure prTest appear
in my list of queries, but it didn't--however it did appear in my
msysObjects table as type 5, which I think is for queries. I was able
to create, execute and drop the procedure as if I were working in SQL
Server.

I also tried creating a view, and did so with success. I was able to
create a ADO recordset by selecting from the view, which worked
perfectly as shown in the code below. Just like the procedure, the
view didn't show up in my list of stored queries, but it did appear in
msysobjects as type 5.

For me, this simply means that I can use ADO/ADO.NET to interact with
Jet databases in very much the same way that I do with SQL Server
databases.

I'm almost tempted to see if I can create a user-defined function in
Jet, but I'll save that for later.

Bill E.
Hollywood, FL

_______________ _______________ _____
Sub TestProcedure()
Dim strSQL As String
Dim objConn As ADODB.Connectio n
Dim rst As ADODB.Recordset

strSQL = "CREATE PROCEDURE prTest ([@intRange_ID] integer) AS
SELECT Range_No FROM Ranges WHERE Range_ID=[@intRange_ID]"

Set objConn = CurrentProject. Connection

'Create a procedure
objConn.Execute strSQL

'Run the procedure
Set rst = New ADODB.Recordset
strSQL = "EXECUTE prTest 1"

With rst
..ActiveConnect ion = objConn
..CursorType = adOpenStatic
..Open strSQL
Debug.Print .Fields("Range_ No")
..Close
End With

Set rst = Nothing

'Drop the procedure
strSQL = "DROP PROCEDURE prTest"
objConn.Execute strSQL

Set objConn = Nothing

End Sub

Sub TestView()
Dim strSQL As String
Dim objConn As ADODB.Connectio n
Dim rst As ADODB.Recordset

strSQL = "CREATE VIEW vwTest AS SELECT Range_No FROM Ranges"
Set objConn = CurrentProject. Connection

'Create a view
objConn.Execute strSQL

'Create a recordset based on the view
Set rst = New ADODB.Recordset
strSQL = "SELECT * FROM vwTest"

With rst
..ActiveConnect ion = objConn
..CursorType = adOpenStatic
..Open strSQL
Debug.Print .RecordCount
..Close
End With

Set rst = Nothing

'Drop the view
strSQL = "DROP VIEW vwTest"
objConn.Execute strSQL

Set objConn = Nothing

End Sub