Edit record in cross table query

[PAuser]

Greetings,

I`m having a curious situation in MS Access 2003 while editing records in a cross table query.

Tables are:
Table1 (field1_1, field1_2, field1_3)
Table2 (field2_1, field2_2, field2_3)

field1_1 and field2_1 are key (id, autonumeric) fields

Fields field1_2, field1_3, field2_2, field2_3 are varchar(512)

The Query is:

Select field1_1, field1_2, field1_3, field2_1, field2_2, field2_3 from Table1 inner join Table2 on Table1.field1_1 = Table2.field2_1

The tables are in a SQL 2008 server and the MSAccess file is a project linked to the SQL Database using classic ODBC.

In the beginning I can't edit any record from the output. Later, when I sort the output (button A->Z in access) any varchar field in any record could be edited.

Any clue?

Thank you, best regards!

P.

[patjones]

I'm replying in order to subscribe to the thread. I'm not really sure what could cause this but am interested to hear what other experts might have to say.

I don't think the sort order should have anything to do with whether the query is read-only or editable. A curious thing indeed.

Pat

[NeoPa]

Bizarre behaviour. I have no idea how such a set of circumstances could come about.

Your explanation is very clear (more so than the vast majority of questions), but could you just run through the scenario again and check that everything you're doing and see matches everything you've described precisely. Let us know if there is any variation from what you've already explained.

It sounds like you've simplified the situation for the purposes of explanation. This needn't be a problem (and often makes sense) but it can lead to mistakes creeping in if the utmost care isn't taken. I'm sure you're naturally careful and precise, but if you wouldn't mind double-checking we may get a clue somewhere.

[PAuser]

Greetings,

I checked again. In fact my post is lot more simplified than reality, but facts are parallel. The field where I try to edit is a text field varchar(512) without any function, calculation, conditional or else in the source query.

As I said, when you open the view you can't edit this field. You can write there after ordering or filtering the data in the resulting view with Access tools (right click or sorting button on toolbar).

...the funny thing is, that this situation is with Access 2003. When I play the same in Office 2010, sorting makes no trick. I can edit the field in Office 2010 only after filtering. Welcome to the twilight zone! =)

[NeoPa]

Simplifying is fine, as long as you test that what you have simplified it to exhibits exactly the same characteristics. Simply assuming it does rather misses the point of asking for help in the first place (I don't know that you have, but I'm inferring from your comments this may be a possibility). If you understood the whole situation well enough to be able to make that call reliably then we wouldn't be here in the first place (That's not an insult at all. Simply stating incontrovertible logic which bears no reflection on you personally).

You stated in your first post that you were talking about a Cross Table query. Did this simply refer to a query where multiple tables are joined together, or was it a reference to a Cross-tab type query?

In your latest post you refer to a view. Should this have been a query? Or are we dealing with SQL Server Views (That wasn't mentioned earlier)? Terms are not irrelevant. Using one term instead of the correct one can lead to misunderstandings and waste of effort.

I hope that some of these questions reveal some clues as, for now, I see nothing that might explain the behaviour you describe. Nor can I imagine any scenario where a subsequent sorting of a query would change it from non-updatable to updatable. That certainly seems weird. In case you haven't already explored this, let me point you to Reasons for a Query to be Non-Updatable. If nothing else it can tell you whether to expect the query to be updatable or not.

[PAuser]

Greetings Pat, Neo,

None insults taken, don't worry; I agree on your observations, specially about that terms are not irrelevant and simplifications could mask the source of the error. I confirm the simplified approach matches the described behavior. The first thing we made was to verify all the details in the page "Reasons for a Query to be Non-Updatable".

The query is written in the Access file. This file has all the objects referenced (linked); that means tables, views, procedures are available for Access, but they're native SQL. The view that is having this weird behavior is written in Access.

My curiosity comes from the fact that just by sorting (office 2003) or by filtering (office 2003/2010), I can edit the data in the table. I didn't expect that.

This is in no way stopping the job of my colleagues, it's just annoying to get support calls every day because our users forget this subtle detail in our application. In the end, they can filter the set of data for the form or sort it before editing.

I appreciate your interest and your help, have a nice day!

Best regards,

P.

[NeoPa]

Just as a thought, and I'm not quite sure whether you want the data to be updatable or the reverse, but you could try forcing it to be non-updatable by setting the Recordset Type property of the form to Snapshot.

Otherwise, I have to agree with you that it appears to be quite weird behaviour.

[PAuser]

Greetings Pat, Neo,

Yes, the idea is to edit the data in a Form. The workaround we choose is to add a sorting after Form loads.

I appreciate your interest and your help, have a nice day and thank you very much!

Best regards,

P.

[NeoPa]

Fair dos P. It's still a bit of an anomaly then, but at least you have handled it adequately for the database :-)