Re: Dealing with Insert Problems with Access

--- Philippe Lang <philippe.lang@xxxxxxxxxxxxxx>
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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster