Groups | Search | Server Info | Login | Register

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

Re: ACC2K3: Multi-table Update Transactions Are Unreliable

Cross-posted to 2 groups.

Show all headers | View raw

Before you jump to try Mr. Kempf's "standard advice", you should investigate 
how easy / difficult it will be to convert using your backend datastore. 
ADP is _effectively_ deprecated in current versions of Access and has never 
worked well with anything but a MS SQL Server back-end datastore.

I am reasonably sure he has suggested you perform major surgery for a minor 
problem. (What the heck? It's not _his_ time, effort, or money.)

 Larry Linson
 Microsoft Office Access MVP


"a a r o n . k e m p f @ g m a i l . c o m" <aaron.kempf@gmail.com> wrote in 
message 
news:5771cfe3-2936-4f28-ac43-92f55cef2e73@18g2000prd.googlegroups.com...
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