Обсуждение: Dealing with Insert Problems with Access
I have just deployed a MS Access client-server application with a PostgreSQL 7.2.4 backend (not that the version matters in this case). I am bumping up against the famous "#Deleted#" in every field issue that comes up on this list from time to time. The problem is that, in this application, the data can be quite sparse for some records, with the result that some records only differ in their primary key value, which is a serial in PostgreSQL. When a new record gets created, Access fails to find a unique record with the same data values, so the error. I don't want to move to unbound forms/controls, so I am looking for a minimalist way to solve the problem, which would involve adding some "before insert" code which will specify a unique value to be inserted. I am looking at two choices: * Do a "nextval" call to get the next serial value, and specify it explicitly. This involves an extra network round trip (and an extra connection), but has the benefit of being unequivocal. * Add a timestamp field to the table, and specify the applicable value in my vba code. This app is not intensively used, so either solution would probably work fine in practice. Is there any reason why I should prefer one over the other? Or is there another kind of solution altogether that I could try? TIA __________________________________ Do you Yahoo!? Yahoo! Photos: High-quality 4x6 digital prints for 25� http://photos.yahoo.com/ph/print_splash
Philippe, Thanks for your response. See below for comments. --- Philippe Lang <philippe.lang@attiksystem.ch> wrote: > Hello, > > I have faced the same problem as you, although the > result was not a #Deleted# in fields. With my driver > version, Access chooses the first occurrence of a > field with the values that have been inserted. If > some previous records contain the same values, the > wrong id is being fetched back! I haven't seen this. Are you saying that different versions of the PostgreSQL driver behave differently in this case? > > There are 2 long threads regarding this, in this > mailing-list: > > [ODBC] Access - ODBC - serial problem... > and > RE: [ODBC] @@IDENTITY (Was: Access - ODBC - serial > problem) > > I suggest you have a look at them. I've read various threads on this subject. I don't believe that I have seen the possible solutions covered well so far. > > My only solution to this problem would be, at the > moment: > > 1) From the client: after an insert in a bound > subform, requery the whole subform. > 2) From the server: use a stored procedure that > inserts data, and returns the id to the client. That > does not allow you to insert data into a linked > table transparently, though... > > > Regarding your solutions: > > Solution 1) must be a problem when used in a > multiuser scenario. When tow different persons > insert data in the same time, this can be a problem. > Locking from the client won't work, I'm sure. This is the solution I chose: I added code to the "before insert" form event that does a "select nextval" on the sequence, and sets the record id in the form to that value. This is multiuser safe, BTW. I was concerned at first about the effect of an extra network round trip, but since the "before insert" event fires at the start of the entry, and entering a new record takes some time, the extra overhead is no issue at all. > > Solution 2) is quite elegant... I believe that this one (adding a timestamp field to the table, and setting the value every time a record is inserted or updated) is canonical in SQL Server-land. I found this solution less aesthetically pleasing, because adding a column to a table just to get around an Access problem seemed to me overkill, and knowing the primary key value up front is an unequivocal solution. I guess all of this is very much OT for a PostgreSQL group, but I am including it anyway in case this is of interest to other readers. I didn't find much useful stuff when I searched the Access groups, perhaps I just didn't use the right search terms. > > Bye > > Philippe > > > -----Message d'origine----- > De : pgsql-odbc-owner@postgresql.org > [mailto:pgsql-odbc-owner@postgresql.org] De la part > de Jeff Eckermann > Envoy� : mardi, 20. avril 2004 16:57 > � : psql-odbc@postgresql.org > Objet : [ODBC] Dealing with Insert Problems with > Access > > I have just deployed a MS Access client-server > application with a PostgreSQL 7.2.4 backend (not > that the version matters in this case). I am > bumping up against the famous "#Deleted#" in every > field issue that comes up on this list from time to > time. > > The problem is that, in this application, the data > can be quite sparse for some records, with the > result that some records only differ in their > primary key value, which is a serial in PostgreSQL. > When a new record gets created, Access fails to find > a unique record with the same data values, so the > error. > > I don't want to move to unbound forms/controls, so I > am looking for a minimalist way to solve the > problem, which would involve adding some "before > insert" code which will specify a unique value to be > inserted. I am looking at two choices: > > * Do a "nextval" call to get the next serial value, > and specify it explicitly. This involves an extra > network round trip (and an extra connection), but > has the benefit of being unequivocal. > > * Add a timestamp field to the table, and specify > the applicable value in my vba code. > > This app is not intensively used, so either solution > would probably work fine in practice. Is there any > reason why I should prefer one over the other? Or > is there another kind of solution altogether that I > could try? > > TIA > > > > > > __________________________________ > Do you Yahoo!? > Yahoo! Photos: High-quality 4x6 digital prints for > 25� http://photos.yahoo.com/ph/print_splash > > ---------------------------(end of > broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to > majordomo@postgresql.org > > __________________________________ Do you Yahoo!? Yahoo! Photos: High-quality 4x6 digital prints for 25� http://photos.yahoo.com/ph/print_splash
Hello Jeff, > > I have faced the same problem as you, although the result was not a > > #Deleted# in fields. With my driver version, Access chooses the first > > occurrence of a field with the values that have been inserted. If some > > previous records contain the same values, the wrong id is being > > fetched back! > > I haven't seen this. Are you saying that different versions of the PostgreSQL driver behave differently > in this case? I happened to see these #Delete# in fields, but only when there was some sort of conflict during the insert, not when records with the same values already existed in the table before the insert... I'm not sure wich driver version you have. I use the latest builds, 208 and 209. > > Regarding your solutions: > > > > Solution 1) must be a problem when used in a multiuser scenario. When > > tow different persons insert data in the same time, this can be a > > problem. > > Locking from the client won't work, I'm sure. > > This is the solution I chose: I added code to the "before insert" form event that does a "select > > nextval" on the sequence, and sets the record id in the form to that value. This is multiuser safe, > BTW. > > I was concerned at first about the effect of an extra network round trip, but since the "before insert" > event fires at the start of the entry, and entering a new record takes some time, the extra overhead is > no issue at all. Between the moment you have fetched the next id, and the moment Access does the insert into the table, another user or process could so the same thing, and fetch the exact same next id, which would result in an insert conflict, no? If I'm not wrong, in order to have something fully multiuser-safe, you should use a stored procedure, and even switch to a serializable isolation level. I personally use LOCK TABLE. Then, with the id your stored procedure gives you back, you open the record from Access. The problem, with that solution, is that it requires a stored procedure call prior to typing any data. It does not solve the problem we may encounter with a raw linked table or subform in Access, when the data being inserted is already present in another row of the table. In that case, as mentioned in posts last week, the id fetched back may be wrong! That's really dangerous... That's why I like your second solution: there must be a way of forcing Access to generate client-side a timestamp (with miliseconds) or some sort of unique random number, before each insert. In that case, the id fetched back by Access is 99.999999% the one you expect. If I'm not wrong, this kind of "trick" is even advised with an SQL Server backend, too... Philippe Lang
--- Philippe Lang <philippe.lang@attiksystem.ch> wrote: > > Between the moment you have fetched the next id, and > the moment Access > does the insert into the table, another user or > process could so the > same thing, and fetch the exact same next id, which > would result in an > insert conflict, no? If I'm not wrong, in order to No. The "nextval" function returns the next available sequence number, and advances the sequence at the same time. So a subsequent call will never get the same number. > have something fully > multiuser-safe, you should use a stored procedure, > and even switch to a > serializable isolation level. I personally use LOCK > TABLE. Then, with With MVCC, it is almost never necessary to lock a table (I've never needed to). > the id your stored procedure gives you back, you > open the record from > Access. > > The problem, with that solution, is that it requires > a stored procedure > call prior to typing any data. It does not solve the > problem we may > encounter with a raw linked table or subform in > Access, when the data > being inserted is already present in another row of > the table. In that > case, as mentioned in posts last week, the id > fetched back may be wrong! > That's really dangerous... > > That's why I like your second solution: there must > be a way of forcing > Access to generate client-side a timestamp (with > miliseconds) or some > sort of unique random number, before each insert. In > that case, the id > fetched back by Access is 99.999999% the one you > expect. If I'm not > wrong, this kind of "trick" is even advised with an > SQL Server backend, > too... Yes, I am sure this solution would work fine. Which is why I had to think a bit before choosing. > > > Philippe Lang __________________________________ Do you Yahoo!? Yahoo! Photos: High-quality 4x6 digital prints for 25� http://photos.yahoo.com/ph/print_splash
--- Greg Campbell <greg.campbell@us.michelin.com> wrote: > I am not absolutely sure about this, so I hope those > who are don't > crucify me. > I think using underlying tables with OIDs and/or > using a timestamp > (default value of Now()) > help the ODBC driver keep track of which record is > which. > I think the #DELETED# is because after some change - > it doesn't know > which record is which. > I do not think the VBA code even needs to refer to > the OID or the > timestamp for this to work. You are thinking of a different case, probably more frequent in practice. That one happens with updates: the story IIRC is that, before saving an update, Access fetches back the record and compares it with the saved "old" values, to make sure that the record has not been altered since it was first displayed to the user. Some oddities can cause Access to be fooled into giving spurious errors. I know that checking "row versioning" in the driver fixes it, apparently by substituting a guaranteed unique row id (ctid? I forget). There is more on this in the psqlodbc project site. > > Jeff Eckermann wrote: > > >I have just deployed a MS Access client-server > >application with a PostgreSQL 7.2.4 backend (not > that > >the version matters in this case). I am bumping up > >against the famous "#Deleted#" in every field issue > >that comes up on this list from time to time. > > > >The problem is that, in this application, the data > can > >be quite sparse for some records, with the result > that > >some records only differ in their primary key > value, > >which is a serial in PostgreSQL. When a new record > >gets created, Access fails to find a unique record > >with the same data values, so the error. > > > >I don't want to move to unbound forms/controls, so > I > >am looking for a minimalist way to solve the > problem, > >which would involve adding some "before insert" > code > >which will specify a unique value to be inserted. > I > >am looking at two choices: > > > >* Do a "nextval" call to get the next serial value, > >and specify it explicitly. This involves an extra > >network round trip (and an extra connection), but > has > >the benefit of being unequivocal. > > > >* Add a timestamp field to the table, and specify > the > >applicable value in my vba code. > > > >This app is not intensively used, so either > solution > >would probably work fine in practice. Is there any > >reason why I should prefer one over the other? Or > is > >there another kind of solution altogether that I > could > >try? > > > >TIA > > > > > > > > > > > >__________________________________ > >Do you Yahoo!? > >Yahoo! Photos: High-quality 4x6 digital prints for > 25� > >http://photos.yahoo.com/ph/print_splash > > > >---------------------------(end of > broadcast)--------------------------- > >TIP 1: subscribe and unsubscribe commands go to > majordomo@postgresql.org > > > > > > begin:vcard > fn:Greg Campbell > n:Campbell;Greg > org:Michelin North America;ENG-IT > adr;dom:;;2420 Two Notch Road;Lexington;SC;29071 > email;internet:greg.campbell@us.michelin.com > title:Systems Engineer > tel;work:803-951-5561 > tel;fax:803-951-5531 > x-mozilla-html:FALSE > version:2.1 > end:vcard > > __________________________________ Do you Yahoo!? Yahoo! Photos: High-quality 4x6 digital prints for 25� http://photos.yahoo.com/ph/print_splash