Re: Cant use autonumber

From: TC (no_at_email.here)
Date: 05/14/04 

Date: Fri, 14 May 2004 13:45:36 +0930

Hi Neil

Another respondent has pointed you to his own answer. he feels the problem
is solvable (using his answer). I'm not convinced yet, so I'll need to do
some further testing. That will take a few days, then I will post back to
this thread.

As for wrapping multiple updates into a single transaction:

dim ws as workspace
set ws = dbengine(0) ' default workspace.
ws.begintrans ' start the transaction.
- do various updates here -
ws.commit ' commit the transaction.

The purpose of a transaction is to ensure that all the updates permed
therein, are written to the database as a single unit. If anything goes
wrong inside the treansaction, you can "roll it back" using the rollback
nethod, then any of the updates performned so far (in that transaction) are
automatically not written to the database. So a transaction is an "all or
nothing" affair. You can read more about this in online help.

As I say, I'm not convinced that simply locking the recordset solves the
problem 100% in all cases. So I'll do more testing, & post back here in due
course.

Cheers,
TC

"news.btinternet.com" <neil@haywood55.freeserve.co.uk> wrote in message
news:c7vp76$t5h$1@sparta.btinternet.com...
> TC,
>
> This is one problem that I have always thought I would run into and to be
> honest I was not too sure how to solve it until I read your response to
> Michael's problem. Would you be able to give an example of how you would
> wrap >all< the relevant updates into a single transaction? I am not too
sure
> what u mean by this.
>
> Sorry for jumping in Michael (i dont know if this would have been your
next
> question :-)), but I thought it would be easier to continue on in this
> thread instead of starting a new one and totaly confusing everyone.
>
> TIA,
>
> Neil.
>
> "TC" <no@email.here> wrote in message
> news:40a2f3f9$1_2@news.chariot.net.au...
> > This is a classic problem that occurs in any multiuser database - even
the
> > big commercial ones.
> >
> > To solve it, you need to do something like this.
> >
> > (1) Lock the table containing the #s.
> > (2) Determine the next available #.
> > (3) Save all updates pertaining to the new #.
> > (4) Free the table containing the #s.
> >
> > Step (4) ensures that no >other< user can do step (2), untill the
> >current<
> > user has finished step (3). If you let any other user do step (2),
before
> > the current user has finished step (3), you will run the risk of holes,
in
> > the following scenario:
> >
> > - user 'A' gets next available #123;
> > - user 'B' is allowed to get next available #124 >before user 'A's
changes
> > have saved<;
> > - user 'A's changes fail (for whatever reason).
> >
> > Now the #s go 122, 124, and 123 has disappeared!
> >
> > The sequence described above is relevant regardless of how many tables
are
> > involved in the process.
> >
> > I believe (but am happy to be shown otherwise!) that this problem >can
not
> > be solved using normal bound forms<. This is because you need to
increment
> > the # somehow, and you can only do that through code, but there is no
way
> > you can wrap that update into the transaction used by the bound form.
> >
> > It could be solved, of course, by using an unbound form, where you could
> > wrap >all< the relevant updates into a single transaction.
> >
> > HTH,
> > TC
> >
> >
> > "Michael S. Montoya" <blank@blank.com> wrote in message
> > news:eetoc.66474$vn.43674@newssvr25.news.prodigy.com...
> > > I have a table that is using InvoiceNumber as the primary key,
however,
> I
> > > can't have it to be autonumber because we don't want a user to back
out
> of
> > > adding a new entry and skip the invoice number. What I do is before
> > update,
> > > assign the invoice number to dmax+1.
> > >
> > > This usually works, however, there have been a couple occasions where
> two
> > > users start the new record at the EXACT same moment and both grab the
> same
> > > number. Is there a way around this?
> > >
> > > Thanks
> > > Michael
> > >
> > >
> >
> >
>
>

Relevant Pages

  • RE: Master/Detail Transactions
    ... If any of the updates fails, ... pending parent and child rows within the scope of a transaction, ... cascade newly retrieved parent identity values down to pending child rows ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Recordset.AddNew and the recordset objects data retaining
    ... I absolutely think that in extremely large jobs, ... I know, I could send a single transaction containing 10,000 records but it ... > physical memory to accommodate the TWO sets of 100,000 records: ... >> The statement you make about the individual updates not hitting the ...
    (microsoft.public.data.ado)
  • Re: Recordset.AddNew and the recordset objects data retaining
    ... The statement you make about the individual updates not hitting the database ... to do with the updates being in a transaction but rather on whether you specify ... spill over and the memory starts paging out to disk that concerns me). ...
    (microsoft.public.data.ado)
  • Re: Using transactions to insert into to a table while allowing read access to existing data
    ... purpose but to roll back the transaction. ... rows with uncommitted updates are marked with locks. ... isolation level is READ COMMITTED, ... then finally committing the transaction. ...
    (microsoft.public.sqlserver.programming)
  • Master/Detail Transactions
    ... If any of the updates fails, ... attempt to run the transaction as an ADO.net SqlTransaction, ... just don't know how to deal with the Master/Detail relationship. ... accomplish this with an Identity Field? ...
    (microsoft.public.dotnet.framework.adonet)