VBA function vs STORED PROCEDURE

[archer2002]

Hi there.
I'm fresh with MS Jet SQL, so I have the question.
I need to implement something like STORED procedure in Access and suppose to do that using VBA functoin in SELECT/INSERT statements. But. It is easy to make function which returns ONE value, is that possible to make the function wich return all RecrdSet and use it from SELECT statement, like:

SELECT sp_AllCustomersWhereSomeConditionsAreTrue(ConditionOne, ConditionTwo, ConditoinTree,...);

and have the numerous records as the result of SELECT execution.

 

[JeremyNYC]

Yeah. Sort of. At first blush it looks like you just want a select query that uses several criteria. Or are you doing something in code that needs to walk through this recordset? If so, just make the recordset using the proper sql string and use that in your code. You can pass a recordset variable from one procedure to another, but I've never made a function of datatype recordset.

I just played with it a _tiny_ bit, and the problem I ran into is that the recordset gets recreated every time you call it. Here's the code I used
Option Compare Database
Option Explicit
Public db As DAO.Database

Private Function rst() As Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT AccountID FROM tblAccount", dbOpenSnapshot)
End Function

Function testRst()
rst.MoveLast
MsgBox rst.RecordCount
End Function

The recordCount is always 1, because the moveLast doesn't happen to the copy of the recordset that gets counted.

I only spent about 90 seconds playing with this, and a lot less than that thinking about it, but I'm guessing there's another way to do what you want. Which I guess brings us to what should have been my first question: what is it you're trying to do with these records? Will a select query work for you?

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developers' section of the site for some helpful fundamentals.

 

[archer2002]

Dear JeremyNYC,
it seems, that I have described my problem not properly.
So, I suppose that this example will explain what do I mean.
I have the query

SELECT sp_GetData(1,2,3);

and the VBA Function


Function sp_GetData(p1 as Integer, p2 as Integer, p3 as Integer ) as Array
.....
' SOME OPERATIONS
.....
set sp_GetData = new Array(p1, p2, p3)
End Function
So, I need to receive the output:
=============
| 1 | 2 | 3 |
=============

Really need someone's help...