A Function that returns a Recordset

[keirnus]

Hello,

I'm into DB access now.
I have a SELECT Query code here:

Public Sub DBSelectUpdateListTable()
    
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset("SELECT ActivityID FROM " & DB_UPDATE_LIST & " ORDER BY ActivityID")
    
    While Not rs.EOF
        
        rs.MoveFirst
        GatherDataHere = rs![ActivityID]
        rs.MoveNext
        
    Wend
    
    rs.Close
    db.Close
End Sub

I want this function to be exclusively for DB access only.
So, I want to get the gathered recordset data out from this function.

What is the best way to do that?

I was thinking of return function but I don't know what to use for a Recordset.

 

[Banana]

I'm not understanding why you need this, but if you want a function to return a recordset just do this:

Note: Aircode!

Public Function Foo() As Recordset
...
End Function

Then whenever you call the function, the code would look like this:

Private Sub bar()

Dim iter as DAO.Recordset

Set iter=foo()

....

End Sub

HTH.

 

[keirnus]

I'm not understanding why you need this, but if you want a function to return a recordset just do this:

Note: Aircode!

Public Function Foo() As Recordset
...
End Function

Then whenever you call the function, the code would look like this:

Private Sub bar()
 
Dim iter as DAO.Recordset
 
Set iter=foo()
 
....
 
End Sub

HTH.

Oh, that does the job.

I just want the function to be exclusively for DB access only.
When the data is gathered in that function, I want it to be saved
into something and pass it by the function.

I will just be calling that function whenerver I need to search data
(using SELECT Query) from an Access DB.

Is it a good implementation?
Do I need to pass the whole Recordset or pass something else?

My implementation is kinda not good.

 

[Banana]

I'm afraid I will have to ask you to elaborate more on "exclusively for DB access only" as I'm not sure what it is supposed to mean.

Furthermore, I'm not clear why this has to be done in code when it would be simpler to do with a bound form...

Maybe if you explained just a bit more about your business needs and what you want to do with data, then we can help with providing you the most simple and effective solution for your particular needs instead of giving you code that answers your question but isn't the best solution...

 

[boblarson]

... instead of giving you code that answers your question but isn't the best solution...

I second that one!

 

[keirnus]

I'm afraid I will have to ask you to elaborate more on "exclusively for DB access only" as I'm not sure what it is supposed to mean.

Furthermore, I'm not clear why this has to be done in code when it would be simpler to do with a bound form...

Maybe if you explained just a bit more about your business needs and what you want to do with data, then we can help with providing you the most simple and effective solution for your particular needs instead of giving you code that answers your question but isn't the best solution...

Ooops, sorry if I wasn't able to give more information.
My bad.

"Exclusively for DB access only" means it access the DB
on that function only.

I was thinking of passing an array by that function which retrieves DB data instead.

I just need to get DB data using SELECT * Query and display them in my customized GUI.

 

[namliam]

Sounds to me like you are trying to apply a three tier internet security protocol into access....

 

[Banana]

"Exclusively for DB access only" means it access the DB on that function only.

I was thinking of passing an array by that function which retrieves DB data instead.

I just need to get DB data using SELECT * Query and display them in my customized GUI.

I'm still fuzzy on this. I was expecting a response among those lines:

"The database will be accessed via internet and I need to secure the access."

"I want to check if users has the permissions first before retrieving the data."

"I do not want to allow users to edit the tables directly, rather using my GUIs instead."

"I am using Visual Basic 6 as a front end and need to retrieve data from an Access database."


or something in similar vein....

Did that clarify the question?

 

[The_Doc_Man]

First, let's understand what a recordset is.

It is a data structure that points somewhere in a record stream associated with a record source such as a table or query. It is a POINTER to something, a placeholder.

You dont' return the actual record this way. I.e. there is no copyout of the buffer contents. We talk about a recordset as though it were really a set of records - but it is not the set. It is a selection mechanism for the IMPLIED set.

Passing a recordset is perfectly fine as long as you realize that you are passing a pointer and descriptive structure for the REAL set of records.

Now... I'm with Banana and others on the "why" of doing this. But as a toolsmith, I've had to write some pretty bizarre code sometimes as a way of compartmentalizing some action, so that I could ASSURE that some rule was being followed before I actually touched the entity in question. And that's not uncommon. So I won't criticize for the choice. If I have any criticism at all, it is that you appear to not understand at a verbal level WHY you have made the choice. And that betrays a sense of confusion.

Let's be honest - when it comes to problem-solving, all of the regulars here tend to want to give you the BEST answer, not the narrowly constructed "correct" answer to the question. So we get nosey and ask questions. But there is an ulterior motive. We learn not only by doing but by seeing problems and learning how others approach them. Some problems are "ordinary" but every now and then we get a problem that lets us learn something new. So of COURSE we have to explore anything we don't understand.

Back to your issue. If you cannot articulate WHY you did something, then perhaps you did it for the wrong reason. And that is what we are picking up in this thread.