(ADP) form problem: "This Recordset is not updatable"

[radio1]

Configuration: Access 2002 and SQL Server 2000 using a .ADP Project.


I would VERY MUCH appreciate anyone's input into this problem I'm having.

I have a form in Access that does not permit the user to add/change/delete any data, apparently as a result of adding a GROUP BY clause to a View used indirectly as the form's Record Source.
I really don't believe that this restriction needs to be there, and I'm hoping that someone can suggest ANY solution or workaround.

To be specific -- when the user tries to type into a field on the form, Access beeps and displays "This Recordset is not updatable" in the bottom status bar.

I have 3 tables, subjects, studies, and study. These are not ideally named, as evident by their descriptions:

"subjects" is a person table -- one record per person
"study" is a table of studies (events) -- each record describes one study.
"studies" is a 2-column table linking subjects records to study records (many-to-many)

Table: subjects -- one record per person
---------------
subjectId uniqueidentifier 16 <- PRIMARY KEY
lastName nvarchar 50
firstName nvarchar 30
middleName nvarchar 30
dob datetime 8
...
etc (about 80 fields total)

Table: study -- one record per study
------------
studyNumber nchar 8 <- PRIMARY KEY
startDate datetime 8
endDate datetime 8

Table: studies -- link subjects records to study records (many-to-many)
--------------
subjectId uniqueidentifier 16
studyNumber nchar 8


In Access, I have a form ("Single Form" - one record displayed at a time) which primarily allows viewing/editing of the subjects table.

Initially, I had the Record Source of the form to be the "subjects" table, and all was fine -- I could edit the records in the form.

I subsequently added the ability to display-only the subject's age on the form.
I did this by creating a View ("viewSubjectsMain") which simply added a new (calculated) column to those from 'subjects', changing the form's Record Source to "viewSubjectsMain", and setting the form's Unique Table property to "subjects". I used this View approach so that the end-user can filter/sort on this age field in the form. This also worked fine.

Here's that view code for posterity ("dob" is date-of-birth):

CREATE VIEW dbo.viewSubjectsMain
AS
SELECT dbo.subjects.*, CASE
    WHEN dateadd(year, datediff(year, dob, GetDate()), dob) > GetDate()
    THEN datediff(year, dob, GetDate()) - 1
    ELSE datediff(year, dob, GetDate())
    END
AS age
FROM dbo.subjects
 

The problem appears now as I'm trying to implement adding 2 new display-only fields to the form: NumberOfStudies and LastStudyDate. Since I want the end-users to be able to filter/sort on these fields as well, I want to create them as columns in a View.
I did this by creating a second View, and modifying my first view to reference it as follows:

CREATE VIEW dbo.viewSubjectsMain
AS
SELECT dbo.subjects.*, CASE
    WHEN dateadd(year, datediff(year, dob, GetDate()), dob) > GetDate()
    THEN datediff(year, dob, GetDate()) - 1
    ELSE datediff(year, dob, GetDate())
    END
AS age, numStudies, lastStudyDate
FROM dbo.subjects
LEFT OUTER JOIN viewSubjectsStudySumm
    on viewSubjectsStudySumm.subjectId = subjects.subjectId
--------------------------
CREATE VIEW viewSubjectsStudySumm
AS
SELECT studies.subjectId, 
    COUNT(*) AS numStudies, 
    MAX(study.endDate) AS lastStudyDate
FROM studies
JOIN study ON studies.studyNumber=study.studyNumber
GROUP BY studies.subjectId
 

Why have one view reference a second view? I'm far from an expert at these things, and my trial-and-error approach took me to this version. It initially made it two views to avoid having to name all 80 subjects columns in the GROUP BY clause, but it's very possible it's not the best or most efficient approach.

Perhaps someone knows some VBA way of setting some flag(s) in Access to allow editing of the form, or maybe how to rewrite the SQL so that it doesn't need the GROUP BY (if that's the only problem) -- maybe with a user function or two, or maybe someone knows how to implement what Microsoft is alluding to in the second-last paragraph of this page:
http://msdn2.microsoft.com/en-us/lib...ffice.10).aspx

ANY and ALL input about this is welcome and appreciated. Thank you in advance.

-radio1

[NeoPa]

I would think that any Recordset definition (Query / Table / view) would be non-updatable if, when you ask yourself exactly what the process should do, you can't come up with a good answer.
In a Recordset that is GROUPed, there is no way of knowing how to update a record as those records don't exist anywhere.
Equally, where you have a View (or even an Access QueryDef) which includes calculated fields, what should it do?
Maybe the latter could work if it's intelligent enough simply to update those non-calculated fields and ignore the calculated ones.
I'm afraid I can't be much more help at this stage - only to say that what you're trying to do is not the way to go as far as I can see.
You may try asking a similar question in the SQL Server forum though.

Lastly, after all that unpalatable stuff - nicely laid out question!
I wish all our members could post as clearly as this.

[cyberdwarf]

Hi try this for size (Microsoft quote):

"Updatable views can modify more than one table involved in the view. The DELETE, INSERT, and UPDATE statements can reference a view as long as SQL Server can translate the user's update request unambiguously to updates in the base tables referenced in the view's definition.

Expect differences in behavior when working with updatable views with more than one table involved in theDELETE, INSERT, or UPDATE statements."

IMHO, the word "unambiguously" confirms NeoPa's post.

HTH

Steve

[NeoPa]

Thanks Steve,
That puts it better than I could :)

[radio1]

I would think that any Recordset definition (Query / Table / view) would be non-updatable if, when you ask yourself exactly what the process should do, you can't come up with a good answer.
In a Recordset that is GROUPed, there is no way of knowing how to update a record as those records don't exist anywhere.
Equally, where you have a View (or even an Access QueryDef) which includes calculated fields, what should it do?
Maybe the latter could work if it's intelligent enough simply to update those non-calculated fields and ignore the calculated ones.
I'm afraid I can't be much more help at this stage - only to say that what you're trying to do is not the way to go as far as I can see.
You may try asking a similar question in the SQL Server forum though.

Lastly, after all that unpalatable stuff - nicely laid out question!
I wish all our members could post as clearly as this.

Thanks, NeoPa, for the reply and the kind words about my post. I did put in considerable time and effort to be as clear as possible!

I understand and agree with everything you said.

In my particular case, though, the "main" query returns uniquely keyed records, and the GROUP BY only applies to the JOIN'd view which effectively results in adding 2 calculated columns to the rows of the main view. Since there is no GROUP BY on the master "subjects" table, all columns originating from that table "shouldn't be" ambiguous.

Also, from my first code snippet, it's clear that Access was intelligent enough to know which fields from the view were updatable, and which was the simply calculated (age) field.

In fact, in my same app, I have other forms from views which include all 3 tables (no GROUP BY clauses though), and Access is smart enough to allow user edits only on the fields that come from the table I specified in the form's "Unique Table" property.

Let me add this piece of information: Taking my Access form out of the equation, my problematic view IS updatable directly from SQL Server Enterprise Manager (except of course for those 3 calculated fields), but it is NOT updatable when accessed directly from Access.

Thanks again for your (hopefully ongoing?) input -- I'm not ready (or able) to give up on this!

-radio1

[radio1]

Hi try this for size (Microsoft quote):

"Updatable views can modify more than one table involved in the view. The DELETE, INSERT, and UPDATE statements can reference a view as long as SQL Server can translate the user's update request unambiguously to updates in the base tables referenced in the view's definition.

Expect differences in behavior when working with updatable views with more than one table involved in theDELETE, INSERT, or UPDATE statements."

IMHO, the word "unambiguously" confirms NeoPa's post.

HTH

Steve

Thanks for your input Steve. Please read my reply to NeoPa above for some new thoughts and information.

Your input gave me more things to Google, and I found some things of interest in what appears to be the document you found your posted snippet in: http://p2p.wrox.com/topic.asp?TOPIC_ID=22741

I'll try to pursue the function angle and/or the INSTEAD OF angle mentioned in the link.

Thanks again -- I'll keep the thread updated with my progress, and hopefully will get still more feedback.

-radio1

[NeoPa]

In my particular case, though, the "main" query returns uniquely keyed records, and the GROUP BY only applies to the JOIN'd view which effectively results in adding 2 calculated columns to the rows of the main view. Since there is no GROUP BY on the master "subjects" table, all columns originating from that table "shouldn't be" ambiguous.

I'm not sure I follow.
If there is a GROUP BY clause in your RecordSet SQL then it is not specific to any of the tables included within the FROM clause but to the overall query.
Maybe I missed something here.

[radio1]

I'm not sure I follow.
If there is a GROUP BY clause in your RecordSet SQL then it is not specific to any of the tables included within the FROM clause but to the overall query.
Maybe I missed something here.

Hi NeoPa -- thanks for staying with the thread.

If you look at the second snippet I posted showing the two Views ('viewSubjectsMain' and the subview 'viewSubjectsStudySumm'), you'll see I essentially have a query on 'subjects', then (left) joined on 'studies', then joined on 'study'.
The GROUP BY clause is on a field in 'studies', not on 'subjects', therefore all fields returned from 'viewSubjectsMain' that originated from 'subjects' do in fact relate to a specific, identifiable row in 'subjects'.

This is supported by the fact that when accessing this View via Enterprise Manager, I AM allowed to update the fields that are associated with 'subjects'. It is only the 1 calculated field (age) and the 2 grouped fields (numStudies, lastStudyDate) that are correctly not updatable.

Unfortunately, the same View when opened directly in MS Access is entirely NOT updatable, and the same is true when the view is opened in Access via my form (even with the form's "Unique Table" property set to "subjects") -- which is my ultimate problem.

So my dream-solution would be to discover a simple way to make Access recognize this view as Updatable, as Enterprise Manager does.

Maybe you (or someone else) do have an idea that could help me, but haven't mentioned it because you think it is so obvious that I've probably already checked that box, associated that thing, or fiddled with that widget, etc.
Please don't assume that! While I'm no rookie developer, I'm still quite a newbie at this Access/SQL Server/VBA thing -- I'm learning as I'm going. :)

ANY and ALL thoughts are welcome and VERY MUCH appreciated!

Thanks again,

-radio1

[NeoPa]

Here's how I think it works.
SQL Server (as accessed via Enterprise Manager) is cleverer than it's little half-brother Access (They're not very closely related as they were developed independently within M$ - completely different {competing} developer teams).
SQL Server can handle determining whether or not any fields within a SQL output are related to tables directly, or whether they have been built upon non-updatable items (such as GROUP BY results and calculated fields). Access cannot (does not) do this and works at a simple query level. So if any element of a query is non-updatable the whole query is considered to be non-updatable.

[radio1]

Here's how I think it works.
SQL Server (as accessed via Enterprise Manager) is cleverer than it's little half-brother Access (They're not very closely related as they were developed independently within M$ - completely different {competing} developer teams).
SQL Server can handle determining whether or not any fields within a SQL output are related to tables directly, or whether they have been built upon non-updatable items (such as GROUP BY results and calculated fields). Access cannot (does not) do this and works at a simple query level. So if any element of a query is non-updatable the whole query is considered to be non-updatable.

I think you're essentially right, NeoPa, in the case of the GROUP BY clause -- its presence seems to be what's making Access treat all the columns in the view as non-updatable.

I've now even tried replacing my subview 'viewSubjectsStudySumm' with a Table-Value User-Defined Function with the hope that it may result in "hiding" that GROUP BY clause from Access, but this still did not resolve the problem.

In general, however, with a View without a GROUP BY clause, it is clear that in at least SOME circumstances Access is smart enough to treat calculated columns as non-updatable, while still allowing updates to other columns in the View (as evidenced by the first code snippet I originally posted).

Realizing this, I investigated the possibility of creating 2 Scalar User-Defined Functions to return the 2 derived values (numStudies, lastStudyDate). Unfortunately, such scalar-type UDF's seem to disallow retrieving data from user data tables, so this was a dead end.

I'm still hoping for a magic solution, but my optimism is on the decline... :(

I just wish there was a line of VBA code I could write to change a flag or something to force Access to treat this view as updatable.

Thanks yet again, NeoPa,

-radio1

[NeoPa]

It sounds as if you don't need much help from anybody here.
My T-SQL is certainly too rusty to be of any great help. Unfortunately, requirements at work don't allow for me to use that area much more than where absolutely necessary (which isn't much now I've set up what was originally required). I can understand well enough to follow you and your thinking (which seems impressive) but I could not have led you there myself.
The only concept that I can think of which might afford you an avenue is Pass-Through queries. I can't really fill in the gaps but I guess you won't need any more than a concept to see if it actually can produce any results (I suspect not in fact, but I can't check from here).

[radio1]

Thank you again, NeoPa for all of your input!

I have *finally* coded a (kludgy) workaround(s) to this problem, and I'll describe it here for posterity.

Again, an important aspect of these two display-only fields was that they be user-filterable via Access's standard filtering mechanisms.

Here's what I did:

1- referring to my initial post, I eliminated all code from the SECOND code snippet, and reverted to the code in the FIRST snippet

2- added two new columns to 'subjects': numStudies and lastStudyDate

3- added an AFTER TRIGGER on all three actions (insert, update, delete) on the 'studies' table to recalculate and update the new columns in 'subjects' (only affected rows of course). This was actually VERY tricky coding for a non-guru like me:

CREATE TRIGGER afterUpdateStudies ON studies  
AFTER  INSERT, UPDATE, DELETE  
AS  
 
SELECT studies.subjectId, COUNT(*) AS cNumStudies, MAX(study.endDate) AS cLastStudyDate  
INTO #subjectCalcs  
FROM (select subjectId from inserted union select subjectId from deleted) upd  
LEFT OUTER JOIN studies ON upd.subjectId = studies.subjectId  
JOIN study ON study.studyNumber=studies.studyNumber  
GROUP BY studies.subjectId  
 
UPDATE subjects  
SET numStudies = cNumStudies, lastStudyDt = cLastStudyDate  
FROM #subjectCalcs calcd  
WHERE subjects.subjectId = calcd.subjectId  
 
DROP TABLE #subjectCalcs  

4- modified the form to display-only these new fields

For simplicity in initially stating my problem, I didn't mention that my main form contained a subform, which was the editable list of studies that the subject had been on. This meant that user-edits to this subform caused the new trigger to update the 'subjects' record displayed in the main form at the back end (but not on screen).

I therefore had to refresh the main form's record for two reasons: to reflect the update to the new columns, and to prevent the "record has been modified by another user" message if the user subsequently tried to update it.

I didn't discover a way to make Access refresh only the current record in a form, so:

5- I coded the following lines in the subform's module:

Private justDeletedRecord As Integer
 
Private Sub Form_Load()
    justDeletedRecord = 0
    Me.TimerInterval = 250
End Sub
 
Private Sub Form_AfterUpdate()
    ' refresh parent rec as db trigger changed some values in it
    Dim bookmark As String
    bookmark = Me.Parent.bookmark
    Me.Parent.Refresh
    Me.Parent.bookmark = bookmark
End Sub
 
Private Sub Form_Delete(Cancel As Integer)
    justDeletedRecord = 1
End Sub
 
Private Sub Form_Timer()
    If (justDeletedRecord <> 0) Then
        If (justDeletedRecord = 1) Then
            justDeletedRecord = 2
        ElseIf (justDeletedRecord = 2) Then
            justDeletedRecord = 0
            Dim bookmark As String
            bookmark = Me.Parent.bookmark
            Me.Parent.Refresh
            Me.Parent.bookmark = bookmark
        End If
    End If
End Sub

This code causes the entire parent recordset to be refreshed from the server (could be slow unfortunately) after any edits to the 'subjects' table in subform. This unfortunately causes the form to redisplay on the "first" record (contrary to documentation I saw, so I guess it's just this way for an ADP), so the bookmark code is there to force the original record to redisplay (yes, ugly screen flickering).

The reason for the timer nonsense on the delete-handling code is because there doesn't seem to be any Access event that gets fired AFTER a record is actually deleted (an ADP exclusive -- the firing order of the three delete-related events is different for an ADP versus an MDB -- see http://support.microsoft.com/kb/234866 ). To work around this, the timer code causes the recordset refresh between 250 and 500 milliseconds after the user clicks OK on the confirm-delete popup. Hopefully this will always achieve the desired result.

I'm still very interested in any feedback, especially if someone knows of a better way to have done this but also if anything I've stated here is incorrect.

Thanks All,

-radio1

[NeoPa]

Nice work and thanks for the feedback here in the thread and I got your PM too.
As I said in an earlier post my T-SQL is severely underused so I would need to refresh a lot of it before I could be much use to you. My ADP experience is even less at nill. It seems most of your problems arise from ADP specifics as you say.
I get e-mail notification for all the (>1,000) threads that I've posted in so I wouldn't have missed it ;) I don't know the exact count as the server lost track of a large number of them some time back, but the ones it remembers are nearly at 1,000 again so...
Very pleased to see you've got a solution and, however kludgy, it's nice to see timer code used again - I only have it in one of the projects I've designed so far.

[Rux]

Hi everybody,
I solved the problem adding a field (the calculated 1 in the secondary table with the group by statement) to the main table and updating it trough triggers reflecting any modification to the secondary table. Works fine. Some problem with the delete events, thank's for the solution.
Bye all
Rux

[Rux]

By the way,
I preferred to set the timeinterval only during the delete event:

Private Sub Form_Delete(Cancel As Integer)
 
  deleted = 1
  Me.TimerInterval = 250
 
End Sub

and to reset it after the refresh:

Private Sub Form_Timer()
 If deleted = 1 Then
 
   deleted = 0
    Me.Parent.refresh
    Me.TimerInterval = 0
 
 End If
 
End Sub

;)
Rux

[MMcCarthy]

"subjects" is a person table -- one record per person
"study" is a table of studies (events) -- each record describes one study.
"studies" is a 2-column table linking subjects records to study records (many-to-many)

Table: subjects -- one record per person
---------------
subjectId uniqueidentifier 16 <- PRIMARY KEY
lastName nvarchar 50
firstName nvarchar 30
middleName nvarchar 30
dob datetime 8
...
etc (about 80 fields total)

Table: study -- one record per study
------------
studyNumber nchar 8 <- PRIMARY KEY
startDate datetime 8
endDate datetime 8

Table: studies -- link subjects records to study records (many-to-many)
--------------
subjectId uniqueidentifier 16
studyNumber nchar 8

Hi radio1

Going back to your original post. You have a many to many relationship between studies and subjects which is why you (correctly) have created a join table.

Now my understanding is that each subject takes part in many studies and each study has many subjects. You can represent this correctly on a data entry form in only one of two ways.

Either create a main form for studies and a subform for subjects. Therefore for each study you will be able to add subjects. Or you can reverse the process and create a main form for subjects and a subform for studies and then add studies to each subject. You cannot do both in one form as regardless of the join table this is a many to many relationship. You can only use one form for data entry when a) it is based on a single table or b) it is based on a query joining two tables with a one to one relationship.

Mary

[radio1]

Thanks much for the input, Rux!

Sorry for my delay in replying, I suspect my spam filter may be preventing me from getting thread-update notification emails.

Three things:

1- I'm going to give your updated timer code a whirl. While I haven't had any run-time problems due to my implementation, the timer code seems to cause me some curious cursor/typing behaviour at dev time within the VB code editor when I happen to have that form open (like I type a space, briefly see the space appear and the cursor move, then suddenly see the space disappear and the cursor move back as if some phantom typist pressed the <Backspace> key).

Even without the above quirk, I just like your idea of initiating the callbacks only when they're known to be needed better.

2- Also, let me add that I ended up needing to implement a second trigger, this time on the 'study' table, that essentially does the same thing as the trigger I described earlier to handle date edits or deletions of study records.

Also, being the distrusting guy I am in my own code&logic, I implemented a stored procedure that performs the same recalculations as the triggers, but for all of the subjects. I have this scheduled to execute nightly immediately prior to the backup procedure. It may be overkill, but it certainly can't hurt (!?).

3- I've discovered two problems with my Form_AfterUpdate() code in the studies-based subform:

Private Sub Form_AfterUpdate()
    ' refresh parent rec as db trigger changed some values in it
    Dim bookmark As String
    bookmark = Me.Parent.bookmark
    Me.Parent.Refresh
    Me.Parent.bookmark = bookmark
End Sub

Both problems can occur when the user adds a NEW subject record (the master record on the form), and then immediately adds a studies record in the subform.

The first problem occurs if there are no current user filters on the main form (no filter-by-form, etc.): The bookmark-ing code simply fails and the first record in the main form's recordset (subjects) is erroneously navigated to instead of the current record.

The second version of the problem seems to occur if there are any user filter on the main form. A nasty popup appears:

Run-time error '2001': You cancelled the previous operation. <End><Debug>

The problem is with the line "Me.Parent.bookmark = bookmark"

Of course what I need to happen is to return to the same record the person just added. I can add some error-handling logic to that function, but I'm not sure what code to put in there to achieve my desired result.

If you or anyone has a solution or any thoughts I'd really appreciate it!

Thanks again, Rux.

-radio1

[radio1]

Hi radio1

Going back to your original post. You have a many to many relationship between studies and subjects which is why you (correctly) have created a join table.

Now my understanding is that each subject takes part in many studies and each study has many subjects. You can represent this correctly on a data entry form in only one of two ways.

Either create a main form for studies and a subform for subjects. Therefore for each study you will be able to add subjects. Or you can reverse the process and create a main form for subjects and a subform for studies and then add studies to each subject. You cannot do both in one form as regardless of the join table this is a many to many relationship. You can only use one form for data entry when a) it is based on a single table or b) it is based on a query joining two tables with a one to one relationship.

Mary

Thank you (yet again, referring to previous occasions in another thread) Mary, for your input. Sorry for my delay in replying, I suspect my spam filter may be preventing me from getting thread-update notification emails.

I completely understand and agree with what you're saying, and in fact I actually have both screens you describe:

• (A)- a main 'subjects' form with a 'studies/study' subform
• (B) a main 'study' form with a 'studies/subjects' subform

Please note that this entire thread has been about the first (A) screen -- I have not until just now even mentioned screen B specifically as I'm not having problems with it.

Sorry for the confusion that my descriptions have caused that lead you to believe I was trying to do something wonkier! There is no attempt in my screen to have a sub-subform based on the same 'subjects' table that the main form is based on!

Thanks Mary,

-radio1