write conflict with subform (1 Viewer)

[pilsdumps]

Hi,

I have an Access 2003 project front end on SQL Server 2008 back end.
I have a form containing patient data which has a subform containing data about when an examination was performed for the cycle of treatment for the current patient.

The subform (the problem form) has a data source as follows;

SELECT a.phys_exam_clinically_indicated_pk, a.exam_date, a.patient_cycle_fk, b.patient_fk
FROM phys_exam_clinically_indicated a, patient_cycle b
WHERE a.patient_cycle_fk = b.patient_cycle_pk

I have the unique table property set to the phys_exam_clinically_indicated table as this is where data should be updated/inserted.

The write conflict occurs when trying to insert a new record (despite the data being successfully written to the table). I've seen it suggested that adding a timestamp field can prevent this. So I added one to the phys_exam_clinically_indicated table and found the following;

When the data source is left as above;
1. If phys_exam_clinically_indicated has no records, the fields do not display on the form (ie. it is blank) so no records can be entered
2. If a record exists, the recordset is not updateable.

I changed the data source to include the new timestamp field as follows;

SELECT a.prevent_write_conflict, a.phys_exam_clinically_indicated_pk, a.exam_date, a.patient_cycle_fk, b.patient_fk
FROM phys_exam_clinically_indicated a, patient_cycle b
where a.patient_cycle_fk = b.patient_cycle_pk

Now I find I can update existing records without causing a conflict, but cannot insert new records and get the error message:

Run-time error 273: Cannot insert an explicit value into a timestamp column. Use Insert with a column list to exclude the timestamp column, or insert a DEFAULT into the timestamp column.

As far as I'm aware, you cannot insert into timestamp columns. So, how do I exclude this column from the insert list, without encountering the problem with the recordset not being updateable????

Any help gratefully received as this is making my already bald head much balder....

 

[boblarson]

You should not mess with the timestamp field. It is there on the table but you should not try using it at all. Get rid of it in your query. SQL Server uses it to determine changes made to the row and it should not be used by you at all.

 

[pilsdumps]

Thanks for taking an interest...

Ok, makes sense. However, I still have the issue of the recordset not being updateable. How do I overcome this? I started playing around with sql server functions, but figured there must be an easier way.

Any ideas? because I have to admit I'm stumped...

 

[Scooterbug]

Well, if I had to take a guess, it would be in your Sql statement.

SELECT a.phys_exam_clinically_indicated_pk, 
           a.exam_date, 
           a.patient_cycle_fk, 
           b.patient_fk
FROM phys_exam_clinically_indicated a, 
         patient_cycle b
WHERE a.patient_cycle_fk = b.patient_cycle_pk

How is the subform related to the main form? Also, your where statement looks wrong. You have b.patient_cycle_pk, but that field isn't listed in the select part of the statement.

 

[pilsdumps]

The subform relates to the main form using patient_fk. This works fine, and updates the subform data based on the patient selected in the main form.

I think the query is okay - at least it returns the correct data. Whether it's correct in terms of what Access requires to make the form updateable is another matter.

To explain a little further, the data is related as follows;

patient.dbo.patient_pk ---> patient_cycle.dbo.patient_fk, and patient_cycle.dbo.patient_cycle_pk ---> phys_exam_clinically_indicated.dbo.patient_cycle_fk

To only show the relevant physical exam records for the patient current on the main form, I need to use the patient_fk from patient_cycle - hence why the query is written as it is.

The problem is that Access tells me I have a write conflict - the table updates correctly, but I need to somehow circumvent this message ie. using the timestamp field, but then the query is not updateable....

Any ideas anyone?

 

[boblarson]

Make sure you aren't using the same tables for the main form and subform recordsets or else it will cause an issue.

 

[Scooterbug]

The record source for the subform can contain all the records (ie. Select * from phys_exam_clinically_indicated). The Parent/Child fields should be the PatientID

Also, when you run just the query, do you get the option to add records?

 

[pilsdumps]

The main form uses the patient table as its data source - this table is not used in the subform data source query.

The subform child field is the patient_fk field, the main form master field is the patient_pk from the patient table.

I can add records when the timestamp field is omitted from phys_exam_clinically_indicated, although get the 'write conflict' message, despite the record being successfully inserted. When the timestamp field is included, I don't get the write conflict message but the recordset is not updateable. Also, I don't use the select * notation because I don't want to retrieve all the fields.

I think my initial problem is resolved by using the timestamp field and NOT including it in the recordset as Bob said, although I need to work out how to make it updateable. All tables have a primary key and there are no aggegating fields, both of which, at least in theory, should avoid the not updateable problem.

Not sure where to go from here apart from updateable functions...

 

[boblarson]

Not sure where to go from here apart from updateable functions...

See if any of these things here might be causing the non-updateable query message.

 

[pilsdumps]

Thanks Bob, a useful site.

Unfortunately none of the issues identified in the list apply to my query. It is updateable without the timestamp field, so somehow it's this that is preventing the updates but I'm not sure why.

Is there anything further you or anyone else can suggest??

 

[pilsdumps]

Thanks for your help Bob and Scooterbug. I think have this resolved now.

The data source query was 'incorrect'. In terms of a query that returned the data needed, it was fine. However, as a query that will allow the recordset to be updateable it required the b.patient_cycle_pk field to work. You suggested this Scooterbug so should have tried it earlier and saved me a headache. However, there doesn't seem to be any logic to why this is required as the patient_cycle table is not being updated. I figured that I was getting the patient_cycle key from the other table anyway.

The table no longer requires a timestamp field as the write conflict no longer occurs.

Thanks again.