Re: BeginTrans

From: Del M (Del.Murray_at_CreditHawk.Net)
Date: 01/05/05 

Date: Wed, 5 Jan 2005 11:38:57 -0500

Dave,

A lot of this I think depends on application requirements, philosophy and
how one writes programs. Here is my techinique / philosophy which is
probably worth about 8 cents.

First, when building an invoice, since I use tADODatasets, clientside
cursor, batchoptimistic locking, there is no need for any "key" info (such
as invoice number) for a record set (header/detail logical recordset, not
ado recordset) that is "new". For modifying invoices, the invoice # exists
so the point is moot. Since I use the same code for creating as well as
modifying existing data, I have a column in the ado recordset for invoice #
( I tend to put all columns in my "new" recordsets), but it is not used or
required for a new invoice and the clientside cursor isolates clerk 1's
invoice set from clerk 2's invoice set.

Now, this doesn't work for a multi-threaded app like an ISAPI (which is
about all I write), so I can have temp tables (or they cold be part of the
permanent tables) and the "key" or pseudo invoice number (for a new invoice)
is the users sessionid which is unique (else my application would mix
threads and all hell would reign). Once the user decides the data should be
saved for a new invoice, then the next problem is to get the invoice #
assigned and make sure that 2 users saveing 2 new invoices dont get dup
numbers. There are two good ways (I'm sure there are more ) to do this. 1.
Read for max existing number from the current invoice header table and add
one to it. 2. Have a table that is exclusively for control data which might
include a column named NextInvoiceNumber.

Method 1 requires that you have the header completely ready to commit to the
DB. Then, read for Max(invoicenumber), add 1 to it, slap it into your
header, apply the header to the DB. Do nothing else in between. The chances
of someone else getting in there at the same time are so high that I have
never had a duplicate invoice number and some of my database have actually
millions of invoice headers and gosh only knows how many details with out a
dup ever occuring.

Method 2 is a little bit safer. You get the header ready, read the control
record with pessimistic locking, or lockit directly with part of your sql
statement, get the "NextInvoiceNumber", update it by adding 1, commit it the
DB, then put it in your header, add it to the Invoice header, then unlock
the control record if all goes well, if not, then if all this is done in a
"transaction", you can just roll it back.

With a little bit of tweaking, this has seved me well for 20 years.For both
methods, if the application built pseudo invoices in the live tables, then
to apply the real invoice number (all the rest of the data is there), you
can use the ADO connection obect's "execute" method to do something like ...
ADOConnection.Execute( 'update inv headers set invnumber = ' +
quotedstr(RealInvNumber) + ' where invnumber = ' + quotedstr(sessionid) )
... or call a stored proc to the same effect. The DB will update the invoice
# fast as hell.

If this bored you, or you found it totally unhelpful or idiotic I appologize
<g>.

Personally, I never use data aware components to create new records, it seem
to me to introduce intricacies in data integrity that I dont care to deal
with. I like to use clientside cursors or pseudo records along with ado
recordsets and batchoptimistic locking. It makes it easier on the read when
you go to do maintenance. I never try to structure my code to try and make
the DB go faster, I think he is smarter than me, However, I do pay attention
to DB design and proper use of sql statements (never do "select * " when you
are only displaying or using 3 of 40 available columns). These simple rules
keep me from working the DB overtime. I have tuned some pretty big companies
SQL servers to make querries that ran for 20 hours, run in 2 minutes by
teaching "kids" how to write a proper and practical sql statment.

If this bored you, or you found it totally unhelpful or idiotic I appologize
<g>.

Regards,
Del

PS. To all who read this .. let the flameing begin .. jump on it .....
<g><g><g>

Relevant Pages

  • Re: Bound controls in Page Header section of Access 97 report
    ... Suppose we want to have a page header ... that lists the invoice number for the page as well as the product name. ... control with the product name in the page header. ... it simply select the first record in the set of records on that page to ...
    (microsoft.public.access.reports)
  • Re: page header on second page of invoice.
    ... You can create an InvNo group header in the subreport and use it in place of a subreport page header section. ... I am working on an invoice form in Access via Office XP Pro on Win7. ...
    (microsoft.public.access.reports)
  • Re: How do I add a sum of a subform on a parent form?
    ... > Kanga, don't store this total in the main form's table. ... >> The user can go in the form header and then open the subform with the line ... >> line amounts of the invoice line form, that upon closing the subform the ... >> invoice amount on the header is recalculated. ...
    (microsoft.public.access.queries)
  • Re: Problem with naming group headers.
    ... The simplest approach would be to create one invoice for each order. ... Invoice header ... Order footer ... To remove the Report Header and Footer, click the item on the View menu. ...
    (microsoft.public.access.reports)
  • Re: SPEED QUESTION
    ... header search and detail presentation. ... Supply criteria to the user to search through all those invoice headers, ... have another section of your form load the invoice details ... >> Dacon Software Consulting ...
    (microsoft.public.dotnet.languages.vb)