ACC2K3: Multi-table Update Transactions Are Unreliable

[greyhawk]

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

 

[a a r o n . k e m p f]

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

 

[John W. Vinson]

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 !

Well, obviously nobody will be able to help without more info - table
structures, queries, the actual operations you're running, the nature of the
inaccuracy. I'm not aware of any changes that would cause erroneous data to be
stored without an error message (unless, of course, you are suppressing and
therefore not seeing VBA error messages).

You may want to arrange for someone to get into your database and "put a
second pair of eyes" onto it to see what the problem might be. It sounds
rather more complex a question than can be easily solved over the newsgroups!
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[David-W-Fenton]

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 !

I don't know about differences with transactions, but I do know that
A2003 treats references to controls on forms in SQL executed with
DoCmd.RunSQL completely differently than A97 did. That particular
problem can be fixed by defining the control references as
parameters in the saved queries, but the real solution is to write
on-the-fly SQL without any references to controls on forms.

Doesn't sound like this is your problem, but just so you know that
there are some really subtle differences that are very hard to find.
The app where I discovered this was written long ago by an
incompetent develper (whose initials are "David W Fenton") and it
broke the application of payments, so that both the amount and
invoice applied to of the payment records were never inserted, but
the payment itself was. It was a terrible bug, and it took several
days to find it, and then quite a while to figure out how to fix it.

 

[Access Developer]

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


message
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

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

 

[a a r o n . k e m p f]

YOUR JET BULLSHIT LOSES DATA THUS IT _IS_ALWAYS_ WORTH IT TO MIGRATE!

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

messageI'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

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

 

[David-W-Fenton]

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.

The stupid part of Aaron's suggestion is that avoiding the actual
problem could be accomplished simply by upsizing the back end to a
different database engine (doesn't matter if it's SQL Server or not)
and using ODBC linked tables. It would mean that all the transaction
code would have to be rewritten, but that's the case with his ADP
suggestion as well, since nothing can be converted from the existing
front end to an ADP.

But it would make more sense to me to figure out what's causing the
problem with the Jet/ACE back end, and simply fixing it. My bet is
that if we saw the code, something would jump out at somebody right
away.

 

[Tony Toews]

YOUR JET BULLSHIT LOSES DATA THUS IT _IS_ALWAYS_ WORTH IT TO MIGRATE!

Shouting does not make your point valid.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/

 

[David-W-Fenton]

"a a r o n . k e m p f @ g m a i l . c o m" <[email protected]>
wrote in
m:

YOUR JET BULLSHIT LOSES DATA THUS IT _IS_ALWAYS_ WORTH IT TO
MIGRATE!

Aaron, I have no doubt that when you use Jet, you lose data left and
right.

I suspect you lose data when you use SQL Server, too.

It's not the database engines that are at fault, but the idiocy of
the user.

 

[a a r o n . k e m p f]

BS, I've NEVER lost data with SQL. Access / Jet isn't reliable enough
for a single record and a single user.

only a RETARD would only have Access/Jet as their only platform

 

[a a r o n . k e m p f]

ODBC linked tables are unreliable and a pain in the ass.

I learned SQL Server using Access Data Projects. It's easier to develop in a reliable platform, and it doesn't make you look like an idiot for needing to kick everyone out of the database to compact and repair.

 

[Access Developer]

ODBC linked tables are unreliable and a pain in the ass.

I guess that's why the Access team in Redmond now recommends ODBC linked
tables as the method of choice for using Access with Microsoft SQL Server.

I learned SQL Server using Access Data Projects.

Must have brought a tear to your eye, then, to learn that ADP was now
"effectively deprecated".

You just can't resist an opportunity to make a fool of yourself in public,
can you?

Larry Linson