Groups | Search | Server Info | Login | Register

Groups > comp.databases.ms-access > #768

Re: ACC2K3: Multi-table Update Transactions Are Unreliable

Cross-posted to 2 groups.

Show all headers | View raw

I'd recommend upsizing to Access Data Projects... I've never had any
trouble with multiple table updates there.. more importantly, you
could easily encapsulate those modifications into a stored procedure,
and then you can utility the try / catch functionality, it's BEAUTIFUL

BEGIN TRY
       Update table1 set value = 'XYZ'
END TRY
BEGIN CATCH
       Print 'ErrorInfo: ' -- there are a BUNCH of different variables
that you can use here :)
END CATCH

I can easily write transactions (on the server side _OR_ through ADO)

-Aaron

On Apr 2, 4:24 am, "greyhawk" <mr.roboto...@gmail.com> wrote:
> Folks:
>
> I recently converted a 13+ year old database app from Access 97 to Access 2003.
> The conversion was swift and completely uneventful.  There are dozens of tables,
> queries, forms, reports, macros, and code modules.  In particular, all of code
> modules have been recompiled w/o a single hiccup (and *every* form and report
> has quite a bit of code behind it !)
>
> Simple code seems to update the underlying tables w/o incident, but of course,
> the most important updates related to customer billing are no longer reliable !
> I've been scratching my head for the past month over this and manually fixing
> customer records w/ bogus info.  But w/ the start of April, there's (naturally) a
> brand new monthly billing cycle w/ new batch billing run and I can't continue fix
> balances (for hundreds of customers) on-the-fly anymore !
>
> There are the following tables:
>
> Customer (possessing multiple)
>     Orders (possessing multiple)
>         Invoices (possessing multiple)
>             Transactions
>
> Everything is wrapped in a single transaction via DAO/VBA and new Transactions
> and Invoices are created successfully.  Order balances are updated okay, but the
> final part of the transaction, to update the Customer balance, is simply wrong !
>
> When I wrote this app, I was an independent contractor, so I had lots of time to
> figure out stuff like this and also spent several hours/week on this newsgroup and
> other newsgroups.  I doubt that anyone can help w/ this, but I remembered the
> newsgroups (it's been several years) and am hopeful someone can [at least]
> recommend something for me to look into.  In the meantime, after I get off work,
> I'll be spending the remainder of this weekend reviewing the code and staging a
> dry run of this batch billing system.
>
> Thanx in advance for your attention....Jet

Back to comp.databases.ms-access | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

ACC2K3:  Multi-table Update Transactions Are Unreliable "greyhawk" <mr.roboto.ny@gmail.com> - 2011-04-02 07:24 -0400
  Re: ACC2K3: Multi-table Update Transactions Are Unreliable "a a r o n . k e m p f @ g m a i l . c o m" <aaron.kempf@gmail.com> - 2011-04-02 08:28 -0700
    Re: ACC2K3: Multi-table Update Transactions Are Unreliable "Access Developer" <accdevel@gmail.com> - 2011-04-03 13:14 -0500
      Re: ACC2K3: Multi-table Update Transactions Are Unreliable "a a r o n . k e m p f @ g m a i l . c o m" <aaron.kempf@gmail.com> - 2011-04-05 16:33 -0700
        Re: ACC2K3: Multi-table Update Transactions Are Unreliable Tony Toews <ttoews@telusplanet.net> - 2011-04-06 15:13 -0600
        Re: ACC2K3: Multi-table Update Transactions Are Unreliable "David-W-Fenton" <NoEmail@SeeSignature.invalid> - 2011-04-09 21:21 +0000
          Re: ACC2K3: Multi-table Update Transactions Are Unreliable "a a r o n . k e m p f @gmail.com [MCITP: DBA]" <aaron.kempf@gmail.com> - 2011-05-02 09:44 -0700
      Re: ACC2K3: Multi-table Update Transactions Are Unreliable "David-W-Fenton" <NoEmail@SeeSignature.invalid> - 2011-04-06 17:06 +0000
  Re: ACC2K3:  Multi-table Update Transactions Are Unreliable John W. Vinson <jvinson@STOP_SPAM.WysardOfInfo.com> - 2011-04-02 11:09 -0600
  Re: ACC2K3:  Multi-table Update Transactions Are Unreliable "David-W-Fenton" <NoEmail@SeeSignature.invalid> - 2011-04-03 15:40 +0000

csiph-web