recordset

[playtime38]

I looked up recordset and it said it was a set of records. Why not just call it the records then? Is it because it is taken from a table and only those records may be extracted like all those who live in London? That means it is not ALL the records in the table which would be inferred from the term RECORDS.Also if two tables are combined to take only those who live in London and only those who earn more than 100 0000 pounds a year it would indeed be a set of records. I think that is why it is called a recordset? I have not seen recordsets set out in a table form. How do you view a recordset please? When you go to the menu you can create tables forms reports queries, but I dont see anything for create recordset.

 

[arnelgp]

to give you peace of mind, recordset can only be created through code or probably macro.
it is called recordset because it can hold many tables, not just one as you might think. while DAO doesn't support it , ADO recordset does. for consistency, both DAO and ADO recordset have somewhat the same proc/function names.

 

[jdraw]

Further to arnelgp comment, you may find this article useful.

Good luck with your project.

 

[playtime38]

DAO come and me wanna go home! Jingoes it is bad enough trying to understand DAO and ADO and now we have ACID DAO . As of Access 2007 and onward, the default data access library is named "Microsoft Access Database Engine Object Library" or ACEDAO. Though this has different name, it is merely a newer version of DAO with support for new functionality introduced in those versions. Unlike the case between ADO and DAO, one cannot have both ACEDAO and DAO referenced in the same application; one or other must be chosen. Fortunately, the rest of article is relevant to both so for the rest of article, DAO will be used to refer to both DAO and ACEDAO.
Thanks especially for your answer that recodsets are for vba and it is vba that I am learning that has prompted my question. I never thought that I would get such a succint reply from my gormless question, and I understand why I cant see a recordset as an access object. I thought it may be hidden as a query but wont waste my time now that you have explained it emanates from the coding window. Thank you again

 

[Uncle Gizmo]

Too loud man.....

 

[MarkK]

If you think of it as playtime, I think it makes sense. And playtime can get loud if you do it right.

 

[Uncle Gizmo]

You can see a recordset but you have to jump through hoops! From the table where your data resides create a query listing only the field you want to see in your recordset. From this query create a form. Open the form in design view and delete the record source. Add the following code or something similar:- I have also provided a small demo attached.

Option Compare Database
Option Explicit
'
'"SELECT TOP 25 Employee_Master.ID, Employee_Master.Job_Title, Employee_Master.Job_Code, Employee_Master.NumbTest FROM Employee_Master"

Dim rstEmployee_Master As DAO.Recordset

Private Sub btnSetRecordSet_Click()
    Call fCreateRecordSet1
End Sub

Function fCreateRecordSet1()
Dim rs As DAO.Recordset
 
Set rs = CurrentDb.OpenRecordset("SELECT TOP 25 Employee_Master.ID, Employee_Master.Job_Title, Employee_Master.Job_Code, Employee_Master.NumbTest FROM Employee_Master")

Set rstEmployee_Master = rs

Set Me.Recordset = rstEmployee_Master

'rs.Close
End Function

Private Sub Form_Load()
    Me.RecordSource = ""
    Call fCreateRecordSet1
End Sub

 

[Galaxiom]

You can see a recordset but you have to jump through hoops!

Nowhere near so many hoops as you would have them believe!

There is no need to set the recordset to a procedure variable then to module variable. I would normally use a procedure variable so it can be viewed in the Locals window if necessary but the recordset property of the form can even be set directly in a single line. It persists without either of the other variables.

Open the form in design view and delete the record source.

A good idea for clarity of the developers following in your footsteps but even that isn't essential. Setting the Recordset property overrides the RecordSource property anyway. Certainly no need to change the RecordSource to a null string every time the form loads.

BTW I don't understand why you apparently prefer to always use functions for what can be done in a Sub then have to Call them. I know functions can be used directly in the Event property but it seems odd to do it all the time especially when demonstrating code.

 

[Uncle Gizmo]

There is no need to set the recordset to a procedure variable then to module variable.

In answering this particular question I didn't write the code from memory, I grabbed some from the Internet here:-

Form.Recordset Property (Access)

and modified until it worked. I didn't check to see if it was logical, it worked and that was all I needed to answer the question.

I don't understand why you apparently prefer to always use functions for what can be done in a Sub

It's the way I work! When I am trying to explain a particular piece of code to someone I start by typing in:-
Private Function fX()
then MS Access fills in the rest of the function stub for me, then I begin typing in the stub, that's the main reason I invariably start out with a function.

I suppose I could start with:-
Private Sub fX()
but I'd have to change it to a function if I needed to return a value. So it makes a lot more sense to start out with private function fX()... If I am being particularly tidy, I go through my code at the end, and change all the functions that I can to subs, but I'm not particularly fussy about this.

I don't know of any good reason for using a Sub instead of a Function other than convention. However if there is a good reason, I'm all ears!

 

[Uncle Gizmo]

By the way I searched the forum for Private Function fX() the function signature I use and interestingly, I only found one example HERE:- At least one of those 3 fields not null? which surprised me as I was sure there would be more.

However the one I found:- At least one of those 3 fields not null? ... is a perfect example for what I am saying. If you follow through the code development in that thread, at the end I change the function to pass a Boolean. If I had written a sub from the beginning then I would have found it necessary to change it to a function.

See access help here for more Info:- Expected Function or variable

Another by the way BTW! I am well aware that I do lots of things through habit/instinct with no idea of why I do it that way. So if you see me doing something which you know is wrong, or can lead to problems, then please let me know. However be prepared, I challenge everything. Why do we do this? Why do we do that? VB/VBA has been around for decades, so ideas and ways of doing things will filter through from the past and they may not be relevant today, we just carry on doing them because that's the way our mentor's said we should do it!

 

[Galaxiom]

I don't know of any good reason for using a Sub instead of a Function other than convention. However if there is a good reason, I'm all ears!

Convention is a very good reason. Especially so when posting advice.

The full range of differences and similarities between Subs and Functions along with the use of the Call keyword and the effect of parentheses around parameters is probably only understood by the tiniest proportion of developers.

Even those who strongly suspect they understand it all generally don't. For example did you know that a Function with no arguments is automatically treated as a Sub so the Call keyword as you used in your sample code was not actually required?

See the discussion on this in this thread, particularly my explanation in post 12.

So you know that putting parentheses around an argument in a Sub causes the value to be passed instead of the object? Sorry, not quite the whole picture. See the discussion in this thread, particularly posts 12 and 14.

 

[Galaxiom]

I grabbed some from the Internet here:-
Form.Recordset Property (Access)

BTW They have a mistake in that code.

An ADO Recordset used for a form should use:
CurrentProject.AccessConnection

rather than:
CurrentProject.Connection