[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

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

Recordset Updateable in Datasheet but not in Form

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.
  • 5
  • 4
1 Solution
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:
koshariAuthor Commented:
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.
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.
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

koshariAuthor Commented:
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.
I do not have A 2007 so I cannot open the accdb file.
koshariAuthor Commented:
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.
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).


koshariAuthor Commented:
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.
koshariAuthor Commented:
A simple solution that could not be found in any other similar questions.

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

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