Recordsets and text boxes

Topic

New Reply

This is a stupid question, but I haven’t found a good answer. I have been developing Access apps for a while. I never use recordsets and rarely use text fields. I directly use the fields from my table(s) in my forms. Why does this work when everyone else uses text fields and recordsets. I would appreciate any insight as I want to make my work better. Thanks!

Reply | Quote

Replies

First of all I think you are already using text boxes on your forms. If you open your form in design mode and click on one of your fields, look at the properties I

Reply | Quote

Any time you’re working with a query or a bound form or even a table, you’re working with a recordset, so I assume what you mean is that you haven’t worked with recordsets created in code.

Recordsets are abstract objects and it might help if you remember that. They are NOT the table or the query or the data, but they REPRESENT a set of data that can be returned from a table or query (that’s simplistic, but for these purposes it’s adequate).

So you can create a temporary instance of a set of data by creating a recordset object, and you can then read, manipulate, and even change that data and have those changes written back to the tables themselves. In effect, this is what you’re doing when you use a bound form to edit a table, but it’s also possible to do it in code and without the form and its overhead. A recordset that doesn’t have to be displayed will work much faster than one that has to be loaded and then written to the screen.

One essential use of recordsets has already been mentioned–looping through them. What makes that particularly useful is that you can loop through them, examine each record, and do something depending on the value in the record or even just stop the whole process. A query has to go through every record in its recordset and it can’t be told “just stop if you find this condition”. There is also an upper limit to how complex a query can become before it blows up entirely, although you can accomplish even more highly complex actions using recordsets.

You can get very exotic with code-created recordsets, including nesting them so that you loop through every matching record in an orders recordset for each record in the customer recordset and do something appropriate. Try that with a query. So far, SQL doesn’t include the equivalent of For Each.

You can also pass them as objects, create collections of them to pass into other code routines, and write them out to text files. Using ADO, you can disconnect them from their connection, work with them, reconnect them and update the source. Again with ADO, you can “persist” recordsets, creating a virtual table that can be put on a diskette and handed to someone who can open and edit it using appropriate code, then give it back, and you can open it and use an updatebatch command to write all the changes back to the table.

There’s an analogy of sorts in the comparative strengths of macros and modules. Macros can do some fairly sophisticated stuff, but they lack the flexibility and control inherent in code. Queries can can do some pretty complex stuff, but they lack the flexibility and control provided by a code-created recordset. In both examples, you need to understand and learn to use the more complicated option in order to do serious development in Access.

Reply | Quote

One other quick thought: it is my understanding that queries are saved in an optimized state, recordsets are not. So, in theory, a contest between a saved query and a recordset that performs exactly the same function but is created and executed in code will see the saved query emerge victorious in terms of time to execute.

Charlotte, folks at large, would you agree?

Reply | Quote

Apples and oranges! You can base a recordset on a saved query. Does that make the recordset optimized?

When you are doing updates that don’t require some complex evaluation of each record, it makes sense to use a query. When that isn’t possible, then the issue of “optimized” doesn’t arise.

Reply | Quote

Thanks for the replies. I do understand the concepts of recordsets, but have not had the discipline to figure out the coding. Will work on that! My other question on “text boxes” refers to creating an unbound text field on the form rather than putting the actual field on the form. That is, writing the value to the table in code. Any thoughts on why this is better?

Reply | Quote

It isn’t better, it simply has different uses.

If you need to write multiple records from the same form page, it is necessary. For example, if you have a survey to fill in, each question and answer could/should be a separate record in a response table instead of a field in a flat table. In order to do that, you build the form with unbound controls and use code to write a record for each answer.

It is necessary where the underlying recordset is not updateable from a bound form. For example, if you wanted to base a form on a union query, the recordset would not be updateable, but you could use it to populate ubound controls and then use code to write the entire “record” back to the appropriate table. If you use ADO, you have no choice because forms bound to ADO recordsets are not updateable. In that case, you have to navigate through the recordset and populate the form and capture edits through code using an unbound form and unbound controls.

Reply | Quote