• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1715
  • Last Modified:

Binding forms to DAO and/or ADO Recordsets

Every example I see on EE and on MS (and elsewhere) shows that after binding a form's recordsource to your recordset, you close the recordset, close the connection and set the connection to nothing.

If you close everything, how does your code make updates to the table?

Do you have to use .update and .addnew methods?  Can't you just set the recordsource and continue as if you had not used implicit DAO or ADO methods?
2 Solutions
If you do this then the form may as well be set up as bound. Discoonnecting the recordset creates greater performance, and is much better for data integrity than bound forms. To add new or update records, you will need to code.
Leigh PurvisDatabase DeveloperCommented:
Binding forms to recordset can be handy in a Server environment.
Using more effecient data access (likely through an SP) to then bind to a form.
In such a case no you won't want to close the recordset.

That is different to unbound forms - where the recordset are used only to fill in text onscreen - and then either another recordset to edit or an update statement afterwards does the work.

HKComputerAuthor Commented:
You've just filled in the missing link for me.  I wasn't understanding that this type of coding is specifically for (generally) unbound forms.  Thanks -HK

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

>If you close everything, how does your code make updates to the table?

When you bind a form to a recordsource, you create/use an implied connection between the form and the recordsource. Assuming the recordsource is updateable, that enables changes just by filling out the form and saving the record (or navigating to a new record which saves changes automatically).  It's a quick and easy way to build a form, but it may lack robustness and flexibility that you desire.

Another way to access data is through a connection and a recordset created by your application. That connection is independent from the one that is implied by the bound form. You can use either method, or both in combination. For example, you could bind your form to table A, in which case the form displays its "current" record for table A.  But you may want to access another record in table A or even table B which you can do independently with a different recordset.  

You can think of the bound recordsource as a special recordset that is tied to the form.  Setting some other recordset to NOTHING does not affect the form's bound recordset (recordsource).


HKComputerAuthor Commented:
Thanks dqmq.  I think I'm understanding you and in fact I do use this feature.  I set the form to a recordsource but manipulate date through DAO recordsets in loops etc. to build invoices.  But here is an extension of my question.

In the following code, wouldn't the form's recordsource be destroyed?

Dim db as dao.database
Dim rst as DAO.Recordset

set db = CurrentDB()
set rst = strSQL

Me.Recordsource = rst


set rst = Nothing
set db = Nothing
Leigh PurvisDatabase DeveloperCommented:
Yes - because you've closed it.
Ammending slightly...

Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb()
Set rst = db.OpenRecordset("TableName", dbOpenDynaset)

Set Me.Recordset = rst  'You set the recordset object - Access 2000+ only

'rst.Close  Don't close it!

Set rst = Nothing
Set db = Nothing

Then your form would remain bound as the recordset isn't destroyed as there is still a pointer to it (i.e. from your form).
HKComputerAuthor Commented:
I closed this one out too soon.  I sure do appreciate the "post" input.  I think I'm getting the drift.  Thanks again. -HK

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now