Access 2007 bug with SQL Server Back End
in [Help]
|
From: Banana on 13 Jun 2010 21:35 On 6/13/10 6:08 PM, AVG wrote: > For brevity, I did leave out the fact that I collected all of my info by > monitoring SQL Profiler I did ascribe as such in your original post. > 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) That is really tangential but I want to say I can't remember seeing that before - typically, it asks for primary key to use for scrolling then does lazy fetching thereafter. Are you saying it's sending a IS NULL when user enters a new record? > 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. Okay, I wasn't thinking clearly on how you were working with and can see why it won't have helped. Getting the ID would still need you to requery. I also assume you've tried Me.Refresh to see if it clear #Deleted, yes? > 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. Gotcha. I think that is the key to the puzzle. I assume that the INSERT INTO statement put in the NULL value, yes? I'm under the impression those fields are numeric and thus we can't just cheat our way by passing in a ZLS. Do you have any values that you can consider to be invalid or at least used in lieu of NULL? If that is the case, an option is to set all non-required fields to the default value and thus work around that problem. Is that an option for you? > 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. No. You can use and in fact, mix ADO with DAO in a *db file. I typically use 95% DAO and 5% ADO - ADO can be quite helpful in exceptional case where DAO/ODBC doesn't satisfy the requirement. > 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? Hmm. Unfortunately, the documentation are confusing on that point. Yes, it does seem to imply that it's a ADP-only feature but I can assure you, having used UniqueTable to great success in a *DB file, it's not. The only thing about those two properties is that it only works with an ADO recordset even though they're available when using DAO recordset (will throw an error obviously). For binding ADO to a Access form, take a look and see if this get you started: http://support.microsoft.com/kb/281998/en-us With UniqueTable, it's straightforward. After using the above KB article to guide you for the binding, after the line "Set Me.Recordset = <ADODB recordset>", put in this line: Me.UniqueTable = "<name of table that you are actually updating>" HTH.
From: AVG on 14 Jun 2010 09:56 Answers and one question below. -- AG Email: npATadhdataDOTcom "Banana" <Banana(a)Republic.com> wrote in message news:4C158750.2070405(a)Republic.com... > On 6/13/10 6:08 PM, AVG wrote: >> For brevity, I did leave out the fact that I collected all of my info by >> monitoring SQL Profiler > > I did ascribe as such in your original post. > >> 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) > > That is really tangential but I want to say I can't remember seeing that > before - typically, it asks for primary key to use for scrolling then does > lazy fetching thereafter. Are you saying it's sending a IS NULL when user > enters a new record? Yes, after the 'insert', Access requests the new record using PkField IS NULL as the where clause. When nothing is returned, it then resorts to using all of the fields that had anything entered in them, even if the entry was deleted prior to the 'insert'. > >> 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. > > Okay, I wasn't thinking clearly on how you were working with and can see > why it won't have helped. Getting the ID would still need you to requery. > > I also assume you've tried Me.Refresh to see if it clear #Deleted, yes? Yes, even though I did not expect it to work, I tried it and no luck. > >> 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. > > Gotcha. I think that is the key to the puzzle. I assume that the INSERT > INTO statement put in the NULL value, yes? I'm under the impression those > fields are numeric and thus we can't just cheat our way by passing in a > ZLS. Do you have any values that you can consider to be invalid or at > least used in lieu of NULL? If that is the case, an option is to set all > non-required fields to the default value and thus work around that > problem. Is that an option for you? Some fields are numeric and some text. Using ZLS was one of the suggestions from MS. They suggested changing all non-required numeric fields to text and don't allow nulls. It is totally impractical. This is a very large project (2 years with 10 months on hold) with many tables and relationships that is 90% done. There are many places in the system where searches, concatenations, 'can shrink' (in reports) depend on nulls that would all need to be changed. At last check, the current production version had over 60,000 (not including comments) lines of code. It would add a great deal of time and cost to the project. Neither of which would be acceptable to the client. > >> 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. > > No. You can use and in fact, mix ADO with DAO in a *db file. I typically > use 95% DAO and 5% ADO - ADO can be quite helpful in exceptional case > where DAO/ODBC doesn't satisfy the requirement. > >> 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? > > Hmm. Unfortunately, the documentation are confusing on that point. Yes, it > does seem to imply that it's a ADP-only feature but I can assure you, > having used UniqueTable to great success in a *DB file, it's not. The only > thing about those two properties is that it only works with an ADO > recordset even though they're available when using DAO recordset (will > throw an error obviously). > > For binding ADO to a Access form, take a look and see if this get you > started: > > http://support.microsoft.com/kb/281998/en-us This looks promising. Don't know if I can get to it today, but will definitely try it. > > With UniqueTable, it's straightforward. After using the above KB article > to guide you for the binding, after the line "Set Me.Recordset = <ADODB > recordset>", put in this line: > > Me.UniqueTable = "<name of table that you are actually updating>" The view uses 'INSTEAD OF' triggers for insert, update and delete, however, the PK is an identity column in one of the tables. Just how does Access utilize UniqueTable? Would it still be applicable? > > HTH. >
From: Banana on 14 Jun 2010 11:02 On 6/14/10 6:56 AM, AVG wrote: > Yes, after the 'insert', Access requests the new record using PkField IS > NULL as the where clause. > When nothing is returned, it then resorts to using all of the fields that > had anything entered in them, even if the entry was deleted prior to the > 'insert'. Okay. I'll need to test that out and see how it works out. Thanks for that piece of information. > Some fields are numeric and some text. > Using ZLS was one of the suggestions from MS. > They suggested changing all non-required numeric fields to text and don't > allow nulls. > It is totally impractical. I certainly can understand that. I wouldn't dare to suggest that you change the data type and will assume that there are no good "placeholder" to substitute for nulls for those non-text columns. > The view uses 'INSTEAD OF' triggers for insert, update and delete, however, > the PK is an identity column in one of the tables. Now that was a crucial piece of information. I typically don't work with triggers but have seen other report problems with using triggers, especially INSTEAD OF ones, which could interfere with Access' operations. In such context, it's usually better to work in disconnected fashion, obtaining the new ID via SCOPE_IDENTITY() or via a return value of a stored procedure. That typically is problematic when you want a live display of the data, though. Let's see if ADO will meet your needs, though. > Just how does Access utilize UniqueTable? Would it still be applicable? Basically, if you have a source that joins more than one table and that causes the query to be non-updatable because we can't uniquely identify all components back to their originating source, but if we only need to edit one table, we can set the UniqueTable property (which is actually a shortcut into ADO recordset's property of a similar name) that instructs Access/ADO that only one table needs to be updatable and the rest are just for display. I was not clear whether your view allow updates to both tables and if that is the case, then I am inclined to think we need to look at the ResyncCommand which is where we basically change how Access/ADO updates the local cache after it issues an update (be it DELETE/UPDATE or INSERT) to the source. Again, I've yet had the need to do that before but if the theory holds, you can use ResyncCommand to specify say, a stored procedure instead of a plain INSERT INTO statement that will then return the new PK after the trigger has fired so there's no confusion WRT finding the record. But that's all in theory and I may be off here. Or, we may get lucky and merely changing to ADO recordset is all we need to work around the problem of losing the pointer to the new records due to mismatch in what data it should contain. HTH.
From: Tony Toews on 14 Jun 2010 16:46 On Sun, 13 Jun 2010 20:00:37 -0400, "AVG" <NOSPAMagiamb(a)newsgroup.nospam> wrote: >Thanks for the quick reply Tony. >Hadn't thought of that, so just tried it - yes, the same thing happens. Darn, worth a try tough. >I'll address Banana's comments next. Sounds like Banana has some useful ideas. Tony
From: David W. Fenton on 14 Jun 2010 19:17
"AVG" <NOSPAMagiamb(a)newsgroup.nospam> wrote in news:4c154288$0$22523$607ed4bc(a)cv.net: > Datasheet form bound to an updatable view. I don't believe in making datasheet/continuous forms editable, as a general rule. There are a few exceptions (e.g., single-combobox subforms for creating many-to-many joins, invoice details), but in general I use a read-only list view and an editable single form, with the link master of the detail form being the PK of the list form. I've sometimes done the detail unbound, as well, though that's a lot more complicated, and not often justified. In general, though, I don't use multi-table recordsources in production apps. I'll often do them in quick-and-dirty data cleanup forms that I create for my own use, but I don't think they are a valid interface for end users. So, basically, while what you are doing *should* work, I generally would never encounter it because what you are doing violates what I consider some basic principles for application interface design. -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ |