Binding forms to DAO and/or ADO Recordsets

Posted on 2006-05-26
Last Modified: 2007-12-19
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?
Question by:HKComputer
    LVL 4

    Accepted Solution

    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.
    LVL 44

    Assisted Solution

    by:Leigh Purvis
    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.

    LVL 4

    Author Comment

    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
    LVL 42

    Expert Comment

    >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).


    LVL 4

    Author Comment

    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
    LVL 44

    Expert Comment

    by:Leigh Purvis
    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).
    LVL 4

    Author Comment

    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

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Join & Write a Comment

    Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
    I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now