Recordsets

[ASherbuck]

Hello all,

It's been a while since I've posted but I've been digging around trying to find as much information as I possible can regarding Recordsets. I have a few questions as the resources I have give me to dots but I haven't drawn the lines as clearly as I'd like.

From my understanding, a recordset works along the lines of:
Dim rs as DAO.Recordset

rs = "SQL STATEMENT"

Then I can use that recordset to populate controls on a form, effectively eliminating the need to use a bound text field.

It is also my understanding, I believe I found it on the MVPS site, that using the recordset on the forms OnLoad event greatly enhances a forms load time, especially when dealing with a large number of records.

Furthermore, recordsets allow a user to make whatever changes they want to the recordset without having to save the changes to that data until they specify. So, a form with recordset data can be parsed, manipulated, etc til kingdom come and until the user performs the function that you've created to actually change the data no changes actually take place to the data stored in the tables.

My main interest lies with increasing performance within my forms. I am not a fan of bound text boxes especially when dealing with a large number of records and I am curious if I am on the right track. I have used queries in the past, with criteria to limit the number of records returned so forms load faster, but it seems to me that recordsets seem a little more advanced and have a high pay off.

Any advice ?

 

[DCrake]

Simple Software Solutions

You are a man after my own heart. Its nice to here of people preferring recordsets to bound forms. What you state is almost exactly right, here is an example of using a recordset to retreive data from a large table.

Dim Rs As DAO.Recordset

Set Rs = CurrentDb.OpenRecordset("Select * From Table Where Field = " & Criteria )

If Not Rs.EOF Or Not Rs.BOF Then

     Your code here....

     Rs.Close
End If

Set Rs = Nothing

CodeMaster:

 

[jagstirling]

This is jus what I was after ...... thanks .....

One question though, once you have created a recordset do you have to writew your code as a 'string' ?

For example;

Dim mySQL as string

my SQL = "xxxxxxxxxxxxxxxxxx "
mySQL = mySQL + "xxxxxxxxxxxxxxxxxx "
mySQL = mySQL + "xxxxxxxxxxxxxxxxxx "

DoCmd.SetWarnings False
DoCmd.RunSQL mySQL
DoCmd.SetWarnings True

Or can you just write the code without putting it into a string ?

 

[DCrake]

Simple Software Solutions

This is a matter of readability. If you have a small SQL string then you could do the following:-

DoCmd.RunSQL "Delete * From Table;"

However if the resulting SQL is long then it is better to do parse it into one string for readability

Whatever fires your gun.


David

 

[LPurvis]

As big a fan of unbound forms as I can be at times - they're often not worth the development time required to implement.

Bound forms can be perfectly efficient.
You can even exercise a great deal of control over them, and make efficient requests for data.
Forms bound to an entire table are a poor choice (massively so with a lot of records).

If you have a good index scheme in place you can see good results from careful selection.
If you don't then your recordset will take a long time to open too - as it's the fetching of the data which takes the time.
With unbound forms we just take some further control over how that is then displayed on the form and updated.
As I said though - this kind of control can be exerted upon a bound form to an extent.

If you're worried about the selection time - consider opening a recordset and binding your form to that recordset instead. You know you've only selected what you requested then.
You're also fully able to determine when the data was fetched compared to when it was displayed. If there's a bottleneck - it can help you determine it.

(As I say though - I'm a big fan of unbound forms too.. when it's appropriate).

 

[PatrickJohnson]

This is jus what I was after ...... thanks .....

One question though, once you have created a recordset do you have to writew your code as a 'string' ?

For example;

Dim mySQL as string

my SQL = "xxxxxxxxxxxxxxxxxx "
mySQL = mySQL + "xxxxxxxxxxxxxxxxxx "
mySQL = mySQL + "xxxxxxxxxxxxxxxxxx "

DoCmd.SetWarnings False
DoCmd.RunSQL mySQL
DoCmd.SetWarnings True

Or can you just write the code without putting it into a string ?

To answer your question more directly, yes the code needs to be housed in one way or another, either as an argument in the DoCmd.RunSQL part or in a string field. However, you don't have to reassign variables as you have above. If I have a dinky bit of one line SQL code, i do as above:

DoCmd.RunSQL "Delete * from 'A_Table'"

but if i have a huge bit of code, i will write it into a variable like this:

strSQL = "Select * from 'A_table' where blah blah blah" & _
            "AND blah blah blah..."

The "_" at the end of the line is VBA for "hey, continue this code on the next line, but as the same step." so all your lines are joined into one string variable, then you can run it with DoCmd:

DoCmd.RunSQL strSQL