Yet another non-updateable recordset problem

[George-Bowyer]

Oh, lordy! I have been building my own access database for various work applications since 1997, but whilst i have taught myself to understand quite a lot of VBA in that time, queries have always worked or not worked purely because the magic query fairy says so...

It is entirely my own fault, but SQL is still mostly an unknown country as far as I am concerned.


I have list of organisations, in tblOrganisations, OrganisationID being the primary key.

Each organisation can be in one or more Association. This is recorded in tblOrgAssoc (OrgAssocID (PK); fldOrgID; fldAssocID). Each org can only be linked with any assoc once.

I have another table of records connected with various Organisations (tblAnnualReturns), with fldOrgID and various other fields.

I am trying to create a datasheet to update records in tblAnnualReturns, which works fine if I look at all the records.

However, I want to be able to limit it to only the records from Organisations within one Association.

Here is the sql so far:


SELECT tblAnnualReturn.fldOrgID, tblAnnualReturn.fldDateRecieved, tblOrganisations.FldOrgName
FROM (tblOrganisations INNER JOIN tblAnnualReturn ON tblOrganisations.OrganisationID = tblAnnualReturn.fldOrgID) INNER JOIN tblOrgAssoc ON tblAnnualReturn.fldOrgID = tblOrgAssoc.fldOrgID
WHERE (((tblAnnualReturn.fldYear)=DatePart("yyyy",Date())) AND ((tblOrgAssoc.fldAssocID)=307))
ORDER BY tblOrganisations.FldOrgName;


It shows the info I want fine, but I can't edit fldDateRecieved, which is what I am trying to achieve.


I am fully aware that I ought to know a lot more about sql and query structure then I do - but alas I don't, so if anyone could give me a steer, I'd be grateful...

 

[isladogs]

Allen Browne has a useful list of reasons why queries are read only: http://allenbrowne.com/ser-61.html
See if that provides your answer

 

[George-Bowyer]

Allen Browne has a useful list of reasons why queries are read only: http://allenbrowne.com/ser-61.html
See if that provides your answer

Thanks. Yes, I had already found that.

Trouble is, if you don't really understand the language of queries, then it doesn't actually help very much.

I can see lots of things that I'm pretty sure it isn't.


I'm guessing it's this one: The fields in a JOIN are not indexed correctly: there is no primary key or unique index on the JOINed fields.

But I don't know how to fix it...

 

[isladogs]

Sounds a very likely reason
Do each of your tables have primary key fields? ESSENTIAL
Are you using those in your query joins? HELPFUL
If not, index the fields you are using as joins in the table designs

 

[George-Bowyer]

In tblOrganisations, OrganisationID is PK

I've made fldOrgID and fldAssocID into a joint-PK in tblOrgAssoc

If I make fldOrgID in tblAnnualReturns PK, then the recordset is updateable.

If it's just an Index, then it isn't. The problem is that fldOrgID can't be a unique value, because there is, as the name would suggest, a report for every year.

Unless I make fldOrgID and fldYear a joint PK...?

Nope, that didn't work. Neither did making fldOrgID and AnnualReturnID a joint PK.

It only seems to work with fldOrgID as PK of tblAnnualReturns, which it can't be... :banghead:

 

[isladogs]

OK I can't really follow all that without seeing it in front of me.

Can you post a stripped down version of your database with just enough items & records to see the effect

 

[The_Doc_Man]

It would perhaps make sense that you have a non-updatable query if one of the elements in the JOIN isn't unique, and you point out that fldOrgID cannot be unique. One of the causes of a query not being updatable has to do with uniqueness of destination. When trying to update something, if Access detects non-uniqueness on the destination side, it barfs. Updating through a query would make the JOIN a destination subject to the uniqueness test. If you explore your SELECT clause, trimmed to only show the keys used for selection and no other fields, do you get any repeated rows? I.e. is that SELECT clause always pointing to a distinct and different record or does it sometimes point TWICE to the same record?

There are usually a couple of ways to fix that.

First, it is not enough to make a particular field a compound PK if your referencing query (the other member of the join) doesn't use both elements of the compound key in that join operation. So if you can't link to a unique ID/Date combo, that would make the query behave badly.

Second, if you cannot make something unique, then consider adding a synthetic autonumber key and find a way to build your linkage between the autonumber PK and the particular record you wanted, where the organization ID and year are compound indexes that assure uniqueness but that also could be used to power a search in order to FIND that synthetic PK value.

 

[George-Bowyer]

Database attached as requested.

Its the qryAnnualReturnsByAssoc that I want to build an editable datasheet from - and, as you can see, it is uneditable.

 

[mike60smart]

Hi George

I cannot see how you are able to do Data Input for your process.

I take it you have entered the various values manually into the tables??

 

[George-Bowyer]

Hi, Mike,

I have a NavigationForm with bazillion forms and subforms for data entry.

You asked for stripped down...

 

[isladogs]

I don't know why its necessary here but changing the query recordset type to 'Dynaset-inconsistent updates' makes your query editable.

Suggest you also restore the PK fields as I have done. It still works.

HTH

 

[mike60smart]

Hi

I agree with Colin

Now it makes sense for Data Input

 

[George-Bowyer]

Awesome

Thanks guys.

Working is absolutely good enough for me, but I have no idea how or why???

(I will pretend that it was something technical and scientific, but deep down I'll just carry on believing that Colin knew which ritual sacrifices to make to the Magic Query Fairy...)

 

[isladogs]

LOL. You're welcome.
It's a method I've only ever needed to use with outer joins in the past.
I know why it worked but I don't know why it was necessary in this case.