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.

 

[dalski]

I'm considering doing this but it does not seemed a recommended solution & I'm confused why this is not a more common question; I'm obviously missing something.

I have a common precompiled select query (as it's used so often to maximise speed) to obtain different values assigning the result to a recordset. Dependant on where I use that query I want to return different values. Atm I am writing a different function for each different field I want returned. This is getting messy. It seems it would be better to return the recordset itself. So presumably the recordset is instantiated in the caller & the object reference disposes of itself once the return value is returned?

What would be the recommended procedure for this? I don't want to go defining & opening the recordset where I currently have the call, as that would be several lines; opposed to a single line.

 

[The_Doc_Man]

@dalski - Tacking onto an old thread is certainly permitted, but you might get more answers by starting a new thread and including a link to it. You have passed the "create a link" limit so should be able to just find the link at the top of its page, then do a copy/paste on it, leaving the copy of the link in your new post. There are other ways, but that is the traditional way for copying web links.

The problem with precompiled recordsets is that at first glance, they are always the same and always return the same records. However, there is a loophole of sorts.

You say you want to obtain different results from the same recordset depending on from where you use it. The loophole that might help you is that if you work with query parameters, you retain any pre-optimized nature of the recordset. What you do is define some parameters that can affect the WHERE clause that is part of the query. Before you actually use the query, you store values in the query's parameters. THEN you use the query. You will always select the same fields but by using parameters to make a dynamic filter, you can choose different sub-sets of the main query you describe.

Note that you DO NOT get to change everything in the query by parameters. For instance, you can change the value of a "...WHERE X = param0" by putting different values in param0 (the parameter). But you can't change that to "...WHERE X IN (SELECT Y FROM Table2 WHERE Z= param0) ..." because that would materially alter the precompilation. Here's a starting point.

Use parameters to ask for input when running a query - Microsoft Support

Adding parameters to an Access desktop database query makes it ask for criteria so that it filters the results.

support.microsoft.com

Here's a VBA-oriented article.

PARAMETERS declaration (Microsoft Access SQL)

Office developer client VBA reference documentation

learn.microsoft.com

 

[dalski]

Thanks Doc_Man, Pat.

If you are talking about a parameter, that is different. You can create a query that takes an argument, customerID for example, and when you feed it a customerID, it returns THAT record only.

Correct, that's what I'm up to; a query parameter executed from a function & from your great book recommendation using a saved query for the benefit you mentioned. But in my little mind I have the benefit of only a single line of code calling the function to return the result & in complex procedures to my little mind it is less superfluous & more concise to what I'm actually doing in the more advanced procedures; with lots of code. These query results are used in several places. But the downside is I'm now generating lots of queries & it's getting difficult to see exactly what does what; despite sticking to naming conventions & trying to minimize their use.

Ditch the functions and use separate queries. Depending on how many variations of this select query you need, you might just build it on the fly rather than create a bunch of almost identical querydefs.

I was doing this originally, then i discovered Parameter Queries, thinking they would make it more concise.

The normal solution simply works with the recordset. Not sure why you feel the need to move a step away from that and you haven't told us.

Ok understood, it seems one trade-off for another, I either get a bloated Navigation Pane or bloated code with recordset declarations & finding records... I'll also have duplicate code doing the same thing in several places, pretty near identical to the astute e.g. you used of looking up a certain customerID. This is why i thought a function for that specific purpose would be best. BUT I'm now getting several variations of that function; returning CustomerID, returning CustomerAddress... So I thought best to return the recordset itself.

 

[dalski]

Thanks Pat, no the benefit is I have a single parametized query, which returns several different fields. This parametized query gets executed in many different procedures, many times. Sometimes it returns the same fields, sometimes not. In my little mind this reduces many different queries, performing the same lookup. But it would be nice to return different fields from that parametized query, but I get what you're saying so thank you.

I'm pretty sure that rather than creating a bunch of parameter queries that each return a single different field, most people would use dLookup()'s. Technically less efficient but obvious and doesn't create bloat. And that leads to a design question because it sounds like your table contains a repeating group and that is very poor practice.

Not getting self-defensive here so don't misconstrue my comments & you'd have know way of knowing this. I don't think I have any repeating groups. This query is based on another query to reduce the recordset size & I'm organizing hierarchial data with 10-different levels. With god knows how many different permutations.
My other option is recursion, but as we all know is resource-heavy. So I'm using queries to lookup values & structuring the logic to avoid recursion & using SQL to maximize speed, hence maybe why you think it is a bad design. I take the advice of experts like yourself very seriously & try to implement every piece of guidance I get; hence me trying to ascertain that I'm on a good path, thanks again.

 

[MajP]

So presumably the recordset is instantiated in the caller & the object reference disposes of itself once the return value is returned?

The question is not very clear, for what you want to do. But sounds like you want to get some values from a recordset and then disconnect the recordset. That will not do it because you create another pointer to the instance in the calling code.

Are you returning one record with lots of fields? If so a type can make things a lot easier to read and manage

Type ResourceRecord
  Description As String
  BuyRate As Currency
  DiscountPercent As Double
  ResourceType As String
  UnitUse As String
End Type

Public Function GetResource(ResourceID As Long) As ResourceRecord
  Dim rs As DAO.Recordset
  Set rs = CurrentDb.OpenRecordset("Select * from ResourcesQ where ResID = " & ResourceID)
  If Not rs.EOF Then
    With GetResource
      .Description = rs!Description
      .BuyRate = rs!BuyRate
      .DiscountPercent = rs!ResDiscount_Percent
      .ResourceType = rs!ResType
      .UnitUse = rs!UnitUsed
    End With
  Else
    MsgBox "Bad or no record", vbInformation
  End If
  rs.Close
End Function

Public Sub Test()
  Dim RR As ResourceRecord
  RR = GetResource(134)
  Debug.Print "ResID 134 " & RR.Description & " Type: " & RR.ResourceType & " Discount: " & RR.DiscountPercent & " BuyRate: " & RR.BuyRate & " UnitUse " & RR.UnitUse
End Sub

ResID 134 Diesel Type: Material Discount: 0 BuyRate: 1.19 UnitUse Ltrs

 

[MajP]

But like @Pat Hartman suggests, most of my DBs have tons of Domain wrappers. Sometimes hundreds. Very simple and do one thing.

You pass in an ID and get back a value.

Public Function GetPathLength(pathID As Variant) As Long
 If Not IsNull(pathID) Then GetPathLength = Nz(DLookup("length", "qryPathLengths", "pathid_FK = " & pathID), 0)
End Function
Public Function GetPathDuration(pathID As Variant) As Long
 If Not IsNull(pathID) Then GetPathDuration = Nz(DLookup("TotalDuration", "qryPathSummary", "pathid = " & pathID), 0)
End Function
Public Function GetPiggyBackWait(pathID As Variant) As Long
 'both driver and passenger wait times
 If Not IsNull(pathID) Then GetPiggyBackWait = Nz(DLookup("waitTime", "qryWaitTimeOnPath", "pathid_FK = " & pathID), 0)
End Function

Public Function GetNumberOfTripsOnPath(pathID As Variant) As Integer
  If Not IsNull(pathID) Then GetNumberOfTripsOnPath = DCount("*", "tblPathTrips", "pathid_FK = " & pathID)
End Function
Public Function GetEndingTrip(pathID As Variant) As Long
  Dim strSql As String
  Dim rs As DAO.Recordset
  If Not IsNull(pathID) Then
    strSql = "Select top 1 TripID from qryPathTrips where PathID_FK = " & pathID & " order by TripID Desc"
    Set rs = CurrentDb.OpenRecordset(strSql)
    If Not rs.EOF Then GetEndingTrip = rs!tripID
  End If
End Function
Public Function GetEndingEdge(pathID As Variant) As Long
  Dim strSql As String
  Dim rs As DAO.Recordset
  If Not IsNull(pathID) Then
    strSql = "Select top 1 EdgeID from qryPathTrips where PathID_FK = " & pathID & " order by TripID Desc"
    Set rs = CurrentDb.OpenRecordset(strSql)
    If Not rs.EOF Then GetEndingEdge = rs!EdgeID
  End If
End Function
Public Function GetEndingVertex(pathID As Variant) As Long
  Dim endingEdge As Long
  Dim strSql As String
  Dim rs As DAO.Recordset
  If Not IsNull(pathID) Then endingEdge = GetEndingEdge(pathID)
  If Not endingEdge = 0 Then
     strSql = "Select top 1 EndVertex from qryPathTrips where PathID_FK = " & pathID & " order by TripID Desc"
     'Debug.Print strSql & " problem"
     Set rs = CurrentDb.OpenRecordset(strSql)
     If Not rs.EOF Then GetEndingVertex = rs!EndVertex
  End If
End Function
Public Function GetDriverFromPath(pathID As Variant) As Long
  If Not IsNull(pathID) Then
    GetDriverFromPath = Nz(DLookup("driverID", "tblcarShippingPaths", "pathID = " & pathID), 0)
  End If
End Function
Public Function GetPiggyBackDriver(pathID As Variant, EdgeID As Variant) As Long
  Dim CurrentDriver As Long
  If Not IsNull(pathID) And Not IsNull(EdgeID) Then
    CurrentDriver = GetDriverFromPath(pathID)
    If CurrentDriver <> 0 Then GetPiggyBackDriver = Nz(DLookup("driverID", "qrySelectPiggyback", "edgeID_FK = " & EdgeID & " AND driverID <> " & CurrentDriver), 0)
  End If
End Function
Public Function GetPiggyBackTrip(pathID As Variant, EdgeID As Variant) As Long
  Dim CurrentDriver As Long
  If Not IsNull(pathID) And Not IsNull(EdgeID) Then
    CurrentDriver = GetDriverFromPath(pathID)
    If CurrentDriver <> 0 Then GetPiggyBackTrip = Nz(DLookup("tripID", "qrySelectPiggyback", "edgeID_FK = " & EdgeID & " AND driverID <> " & CurrentDriver), 0)
  End If
End Function
Public Function GetNumberAvailablePiggybacks(pathID As Variant, EdgeID As Variant) As Long
  Dim CurrentDriver As Long
  If Not IsNull(pathID) And Not IsNull(EdgeID) Then
    CurrentDriver = GetDriverFromPath(pathID)
    If CurrentDriver <> 0 Then GetNumberAvailablePiggybacks = DCount("*", "qrySelectPiggyback", "edgeID_FK = " & EdgeID & " AND driverID <> " & CurrentDriver)
  End If
End Function
Public Function GetTotalDeliveredFromPath(pathID As Variant) As Long
  If Not IsNull(pathID) Then
    GetTotalDeliveredFromPath = Nz(DLookup("SumOfCarsShipped", "qryTotalShippedPiggyBackByPath", "pathID_FK = " & pathID), 0)
  End If
End Function
Public Function GetTotalPiggyBacksFromPath(pathID As Variant) As Long
  If Not IsNull(pathID) Then
    GetTotalPiggyBacksFromPath = Nz(DLookup("SumOfPiggyback", "qryTotalShippedPiggyBackByPath", "pathID_FK = " & pathID), 0)
  End If
End Function
Public Function GetTotalPassengersFromPath(pathID As Variant) As Long
  If Not IsNull(pathID) Then
    GetTotalPassengersFromPath = DCount("*", "qryPiggyBackData", "DriverPath = " & pathID)
  End If
End Function
Public Function GetNextDriver() As Long
  GetNextDriver = Nz(DLookup("driverID", "qryNextDriver"), 0)
End Function
Public Function GetStartVertexFromEdge(EdgeID As Long) As Long
  GetStartVertexFromEdge = Nz(DLookup("startVertex", "tblCarShippingEdges", "EdgeID = " & EdgeID))
End Function
Public Function GetEndVertexFromEdge(EdgeID As Long) As Long
  GetEndVertexFromEdge = Nz(DLookup("EndVertex", "tblCarShippingEdges", "EdgeID = " & EdgeID))
End Function

 

[RonPaii]

If you are filling variables from table columns like the following

Description = MyTableDesc(LookUpID)
Location= MyTableLocation(LookUpID)
..
SomeOtherField = MyTableSomeOtherField(LookUpID)

Consider using a class to fill and return values from your query.

With New MyTableClass
.LookupID = LookupID
Description = .Description
Location = .Location
..
SomeOtherField = .SomeOtherField
End With

 

[dalski]

@dalski Although likes are nice they are not helpful. Please tell people if my last response solved your problem and you are going to switch to domain functions. OR, you're going to remove the repeating group.

I cannot see how a domain function is the route when these queries are getting executed tens of thousands of times, against recordsets of around 10k records. Are in around 30 different locations (the callers I mean). Maybe an idea to submit a request to site development to remove the like button - I know you really don't like that thing, but seems so rude not to say thanks with a like after your generousity.

Not possible.

In my amateur perception it just seems so inefficient to create a query for each individual field one wants returned & that's what led me to this thread. It's looking up an ID & returning one of several fields I need dependant on where it is executed from.

Are you returning one record with lots of fields. If so a type can make things a lot easier to read and manage

Type ResourceRecord
  Description As String
  BuyRate As Currency
  DiscountPercent As Double
  ResourceType As String
  UnitUse As String
End Type
[/QUOTE]

Exactly right, I'm trying to replicate your masterful Treeview, but with a Subform. Trying to avoid recursion by using structured logic with SQL.

1 - I'm using a class, & class composition (sub-classes) to replicate what is being read on the current record of the subform in-lieu of your type e.g. I know we don't have inheritance in VBA but I'm replicating from some of your awesome tutorials & much reading/ experimenting. So that seems reasonable for a single record & gives me the benefit of encapsulation to reduce complexity.
2 - So that's fine for the single record being read in the Subform & the classes provide methods for sorting & adding records... But to maximise speed I'm using queries to lookup the headers... instead of your recursion.

 

[tvanstiphout]

I did not realize until now that you're working with hierarchical data. This is one area where SQL Server really shines, with elegant support for it. If SQL Server is an option, check this out.
Note: Access does not support the hierarchyid data type, so you'll work with it in stored procedures, and return this value as a short string.

 

[dalski]

Thanks Tom, I've spent my life-savings on the project, quit my job & about to go bankrupt . Long-term plan is to go MSSQL Server/ Azure, but for now I just need to get it working & get back to work, before i am homeless. That's a great link so thank you.

 

[dalski]

Thanks for the input, it cannot be done at once because the process I'm replicating is recursion. 'Repeating Group', I'm not exactly sure on this, but as a regular user I'm sure you're familiar with MajP's Treeview. That's what I'm up to, but with a Subform, & as I said, instead of using recursion I am using structured logic with SQL.

1 -

Don't use querydefs. Do it the way some of the others do by embedding your SQL and execute the hard coded strings. Because you can't have it both ways. The POINT of using a querydef is so that the created execution plan can be saved to make the query more efficient for the second and subsequent uses. But if you change the structure of the query, then the execution plan needs to be rebuilt. The two requirements are incompatible and that is that.

The QueryDef is being used tens of thousands of times. It is a Parametized Query. So if the point you are making is that the QueryDef gets recompiled when a different parameter is fed to it then I get your point now - so thank you. I did not think that was the case, in the book or in your previous posts; so I don't think this is relevant here. The perception I got from the book & your post, as you've mentioned the only benefit is it saves recompilation of the actual queryDef itself. NOT the parameter; the query structure is the same, just a different parameter. As it is being executed so many times every benefit is desired.
So am I reading you right here in you are saying that a Query Parameter looses all benefit of pre-compilation when feeding a new parameter?

2 -

But there is something wrong with the logic behind what you are doing. Whatever you are trying to do with the record should probably be done all at once rather than broken into tiny pieces. So, either the record contains a repeating group or a mishmash of unrelated fields or your processing logic is strange.

I can't see how recursion could be done with a single SQL statement. Again not getting defensive, I have nowhere near your technical ability. But maybe the complexities of what I'm doing is not clear; nor would it be with what limited info I have provided.