Recordset Updateable in Datasheet but not in Form

Posted on 2010-01-08
Last Modified: 2012-06-27
Four tables are joined with an M:N table that contains nothing except the RecordIDs from the related tables. Running the resulting query from the query designer yields a datasheet view which is updateable.

Running the query using a macro in the form load event yields an updateable recordset.

Copying the SQL code created by the query designer into VBA to open a recordset into a form yields an unupdateable recordset, using the form load event.

Dynaset (Inconsistent Updates) in the form's recordset type property makes no difference
No aggrigation
Use of outer joins in place of inner yields duplication

The solution offered in Question ID 21565049 is the next step, which is to use append and update queries to update the data. This will introduce more complexity than should be necessary.

If the query is updateable as a macro or in the datasheet view, I think it should be using an ADO recordset. So I believe I am overlooking something simple.  The related VBA source is attached. A graphic of the join is also attached. With little experience and a little book learning, I would like to make sense of this before using a workaround.

Thank you for your help.
Question by:koshari
    LVL 39

    Expert Comment

    You can change the form's dataset type to "Dynaset (Inconsistant Updates) to make the form updatable in some cases but you tried that.  If that doesn't work, you need to change the query or use Update or Insert queries to update the data.  Type "When can I update data from a query?" in MSAccess help answer wizard for more information on when queries (datasets) are updatable or check this link:;en-us;304474#XSLTH4225121123120121120120

    Author Comment

    thenelson, thank you for the post and reference. If the MS page reference, the right side of the third table "Data can't be updated," says "Though you can't update the data in the query directly, you can update the data in a form or data access page based on the query if the form's RecordsetType property is set to Dynaset (Inconsistent Updates)."  However, I can update the data in the query directly (foreign keys, text). Of course, I am unable to update the primary keys in the query.

    As I said in the original post, the query datasheet is updateable, it is updateable in a form using a macro. I am not attempting to update any primary keys, only existing data. Full RI is enabled in the table relationships. Updating using the query datasheet works, so I want to be sure there is no alternative to using the update and insert queries for updates.
    LVL 39

    Expert Comment

    I have not seen an updateable query but a form based upon that query not updateable. Try posting your database. To remove private information and reduce the size of the database, do this:
    1. Make a copy of your db.
    2. If using Access 2007, convert the database to an mdb format.
    3. Remove all tables, queries, forms, pages and reports that have nothing to do with the problem.
    4. Delete all but 6-12 records in the remaining tables - use shift / Mouse select to select huge blocks of records, then delete. Change the data on the remaining records to remove any confidential information.
    5. Import all linked tables so that everything is in the one database
    6. Remove any passwords.
    7. Disable all Startup options.
    8. Compact and repair
    9. Move the database to a different folder to make sure it still runs showing the problem
    10. Post explicit steps to recreate the issue.
    11. Zip (optional) and attach to a message.
    ... Again, test the DB before posting to make sure that we can open the DB and easily see the issue.

    Author Comment

    thenelson, thank you for taking a closer look. I followed your instructions, thank you. Attached is an accdb file (txt to be permitted as an attachment? You will need to change the extension to accdb.).  I was, however, unable to save into a 2003 mdb file because a dialog said I was using features only available in the 2007 version. That's puzzling because I crashed the DB earlier and Access saved it as database.mdb.

    Two forms are available: one that uses a macro which allows updates, the other that uses the VBA, which does not allow updates. The query in datasheet format allows updates on all the columns except the last 3 which are the primary keys, as expected. The form fields to the right of the check-boxes are hooked to the foreign keys, which should allow updates.

    The form has check-boxes and option buttons which have not been enabled yet. These can be ignored.

    Feedback related to the problem as well and anything else related to the design and approach welcome.

    Thank you for your help.
    LVL 39

    Expert Comment

    I do not have A 2007 so I cannot open the accdb file.

    Author Comment

    No problem, I deleted the attachment field from the Rules table which refers to an external file. Attached is the .mdb file. I did a brief test to confirm the problem is still there with updates.

    Sorry about the inconvenience.
    LVL 39

    Accepted Solution

    In the VBA form, you defined rstEntryExitRules locally to the form load event with
    Dim rstEntryExitRules As ADODB.Recordset
    in the form load procedure so when you exited that procedure ForiegnKeyRulesID was no longer available and the recordset then became not updatable. I corrected that by using the correct way to set the form's record source property:

    Me.RecordSource = "SELECT tblLinkRules.RulesID, tblLinkRules.TradeTypeID, tblLinkRules.EventID, tblRules.Rule, tblRules.Discussion, tblTradeType.TradeType, tblTradeEvents.Event, tblTradeType.TradeTypeID, tblTradeEvents.EventsID, tblRules.RulesID" & _
                        " FROM tblTradeType INNER JOIN (tblTradeEvents  INNER JOIN (tblRules  INNER JOIN tblLinkRules ON tblRules.RulesID = tblLinkRules.RulesID) ON tblTradeEvents.EventsID = tblLinkRules.EventID) ON tblTradeType.TradeTypeID = tblLinkRules.TradeTypeID;"

    Note: In the query qryLinkRules (or anywhere else), it is best to avoid spaces, underscores and other special characters in your naming of objects. For example: ForiegnKeyRulesID is just as easy to read as Foriegn Key RulesID.  Objects named with special characters including spaces need to be placed in brackets for access to recognize them correctly. Here is an extreme example of the problems you will have using special characters in your names:
    I named a textbox
    !@#$%^&*()_+= -{}:;"'<,>?/|~
    And had access create an event procedure.  Access converted the name of the textbox to:
    so if the form had the name:
    ~!@#$%^&*()_+= -{}:;"'<,>?/|~
    Referencing the textbox would be:
    a useful tip for someone who doesn't want someone else (probably even themselves) from reading their code.
    And spaces will sometimes cause problems in vba references even after years of trouble free operation.

    It is also a good idea to use a naming scheme such as leszynski naming conventions (see references). It makes it clearer what type of object you are naming and it reduces the risk of duplicate name problems (like a control name and its control source).



    Author Comment

    The solution you suggest is simple, much less complex than using ADO, can be used generally, and should suffice for updating without using update and insert queries for updates. The form is bound to the query and permits updates using the form fields directly. A much simpler solution than any of the other solutions found using google. The form appears to work without the "inconsistent updates" property, which allows the integrity constraints to do their jobs.

    A rhetorical question: could a class property, or Friend variable, be added to the form to permit the recordset to become available so ADO could be used if necessary?

    Nice job! Thank you.

    Author Closing Comment

    A simple solution that could not be found in any other similar questions.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
    If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

    729 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