Access 2007 bug with SQL Server Back End
in [Help]
|
From: AVG on 13 Jun 2010 16:41 I am posting this at the suggestion of Tony Toews in response to another of my posts. Access 2007 SP2 with SQL Server 2005 back end. Datasheet form bound to an updatable view. The view is based on multiple tables, but does have a single field primary key. Whether the PK is created in the view, or in Access, makes no difference. The problem is on inserting a new record. Like most forms, some fields are required and some are not. If data is entered in all the required fields any (or none) of the non-required fields, all works fine. The problem is that if user changes their mind and deletes the data in a non-required field prior to saving the record, the form displays #deleted in each field of the new record after saving it. The record, however, IS inserted and does appear in the form if it is requeried. This happens because of the way the Access retrieves a newly inserted record from SQL Server. If the view or table that a form is bound to contains an autonumber (identity) field, Access will query SQL Server for the last inserted value and then query SQL Server for the new record using PkField = IdentityValue, in order to display it. However, when a SQL Server view is based on more than one table, SQL Server does not pass the identity property along with the view. In that case, Access uses all of the entered field values in a where clause, to request the new record. E.G. Where field1 = value1 and Field2 = value2, etc. Only the fields where data was entered is included. While it does take a couple of round trips, this does work. If the user made an entry in a field and then deleted it, Access still includes that field in the where clause. The problem is that it still uses the exact same construct. Where field1 = value1 and Field2 = value2, etc. Well, don't we all know that if value1 is NULL, we must use the construct, 'field1 IS NULL' and not 'field1 = NULL'? Apparently, Access doesn't know that! So, since no record is returned with that query, Access displays #deleted. I have an open case with Microsoft for over three months now. They have duplicated the problem and they don't have a solution or workaround, other than a requery, which would not be acceptable to the client. Fixing a bug in Access or even creating a hotfix is a long-term project for Microsoft and they haven't even decided if they will do either. Of course, another solution would be if there were a way to get SQL Server to pass the identity property through the view, so that Access would use that, but from all of my research, it seems that there is no way to do that. I had suggested that the Access team discuss it with the SQL Server team and when I pressed the issue, was told (by a supervisor) that they had no one in-house that knew enough of both Access and SQL Server to know how to present it to the SQL Server team (not exact words). So, anyone have any suggestions? -- AG Email: npATadhdataDOTcom
From: Banana on 13 Jun 2010 18:17 On 6/13/10 1:41 PM, AVG wrote: > So, anyone have any suggestions? Considering that you've had an open case with MS, some of suggestions may already been tried but I want to make sure we covered all bases so we know what has been already tried and failed (and why). It has been said before that to avoid field-by-field comparsion, one would add a RowVersion column to the table (in older SQL Server, that's misleadingly known as Timestamp, which is not in fact a timestamp). Access will then compare only the RowVersion and thus avoid the problem of losing the pointer to the changed entry. Did you try that? If it did not address the problem, can you make use of SCOPE_IDENTITY()? You said there was no way of getting the ID back from SQL Server, which is strange as SCOPE_IDENTITY() should do that for you, but maybe I'm not fully understanding the problem here. Thirdly, you say the problem occurs when the user changes an edit prior to the saving the record. Do you have any VBA code that saves the record behind the scene or could it be in partial state? I would expect that SQL Server would never receive the edited rows until it was saved by Access/your VBA code and thus that issue should not occur. Finally, if all of above does not apply/won't fix your problem, the alternative to look at is to use ADO recordset and bind it to the form, set the form's UniqueTable property and see if that help Access focus on only one table. If that does not even work, then you may need to take a step further and manipulate the ReSyncCommand property so it look at only the identity column. However, I've yet to try that. HTH.
From: Tony Toews on 13 Jun 2010 19:19 On Sun, 13 Jun 2010 16:41:53 -0400, "AVG" <NOSPAMagiamb(a)newsgroup.nospam> wrote: >Datasheet form bound to an updatable view. Banana has some fine suggestions. Dumb question. Does the same thing happen if you use a continuous form rather than a datasheet form? Tony
From: AVG on 13 Jun 2010 20:00 Thanks for the quick reply Tony. Hadn't thought of that, so just tried it - yes, the same thing happens. I'll address Banana's comments next. -- AG Email: npATadhdataDOTcom "Tony Toews" <ttoews(a)telusplanet.net> wrote in message news:8qpa1656phii0dju8sdve4j3cj2b4uikf7(a)4ax.com... > On Sun, 13 Jun 2010 16:41:53 -0400, "AVG" > <NOSPAMagiamb(a)newsgroup.nospam> wrote: > >>Datasheet form bound to an updatable view. > > Banana has some fine suggestions. > > Dumb question. Does the same thing happen if you use a continuous > form rather than a datasheet form? > > Tony
From: AVG on 13 Jun 2010 21:08
Banana, Thanks for the quick reply. MS hasn't told me everything that they have tried, only that they have tried everything the tech and escalation team could think of. They only had two suggestions: 1. Requery. 2. Change all non-required numeric fields to text and don't allow nulls. That is totally impractical. This is a very large project with many tables and relationships that is 90% done. That would add a great deal of time and cost to the project. Neither of which would be acceptable to the client. Regarding RowVersion - The problem is only on inserting a new record, not updating and existing record, which works fine. The view that the form is bound to consists of at least two joined tables. Each does have a Timestamp column. I have tried including and excluding each Timestamp with no change in results. For brevity, I did leave out the fact that I collected all of my info by monitoring SQL Profiler and that prior to Access requesting the new record by using all of the entered fields, it first requests the record by 'PKfield IS NULL' - something else we all know won't return a record. That wasted round trip (and another), I actually discovered two years ago and had another case with MS - they would not address it unless I could prove to them that the wasted trips was crippling the network - which, of course, I could not do. SCOPE_IDENTITY() - Since this is a bound form, I don't see how I could do that. Unless, I used a local table and constantly synchronized it with SQL Server. Again, a good bit of work as there are several places in the system using forms bound to views. VBA code, etc. - The code behind, does validation, etc. prior to the record being saved and does not perform any specific 'save'. That is done by Access when user tabs or moves to another record, etc. If a user never enters any keystroke in a non-required field, Access does not include that field when it queries SQL Server for the inserted record. It only includes fields where data is entered. Apparently, if data is entered in a field and then deleted prior to saving, Access still includes that field in it's query. ADO - I should have mentioned that the project is an accdb using ODBC, not an adp. Please correct if I am mistaken, but isn't binding to an ADO recordset limited to adp's. I wasn't familiar with the ReSyncCommand and UniqueTable, so just did a search and they seem to apply to adp's also. If a form can be bound to an ADO recordset in an accdb, can you point me to an example? -- AG Email: npATadhdataDOTcom "Banana" <Banana(a)Republic.com> wrote in message news:4C15590A.6090507(a)Republic.com... > On 6/13/10 1:41 PM, AVG wrote: >> So, anyone have any suggestions? > > Considering that you've had an open case with MS, some of suggestions may > already been tried but I want to make sure we covered all bases so we know > what has been already tried and failed (and why). > > It has been said before that to avoid field-by-field comparsion, one would > add a RowVersion column to the table (in older SQL Server, that's > misleadingly known as Timestamp, which is not in fact a timestamp). Access > will then compare only the RowVersion and thus avoid the problem of losing > the pointer to the changed entry. Did you try that? > > If it did not address the problem, can you make use of SCOPE_IDENTITY()? > You said there was no way of getting the ID back from SQL Server, which is > strange as SCOPE_IDENTITY() should do that for you, but maybe I'm not > fully understanding the problem here. > > Thirdly, you say the problem occurs when the user changes an edit prior to > the saving the record. Do you have any VBA code that saves the record > behind the scene or could it be in partial state? I would expect that SQL > Server would never receive the edited rows until it was saved by > Access/your VBA code and thus that issue should not occur. > > Finally, if all of above does not apply/won't fix your problem, the > alternative to look at is to use ADO recordset and bind it to the form, > set the form's UniqueTable property and see if that help Access focus on > only one table. If that does not even work, then you may need to take a > step further and manipulate the ReSyncCommand property so it look at only > the identity column. However, I've yet to try that. > > HTH. |