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

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 Access2003.
> 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

On Sat, 2 Apr 2011 07:24:09 -0400, "greyhawk" <(E-Mail Removed)> 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 !

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/For...-US/accessdev/
http://social.answers.microsoft.com/.../en-US/addbuz/
and see also http://www.utteraccess.com

"greyhawk" <(E-Mail Removed)> wrote in
news:4d97077d$0$29300$(E-Mail Removed):

> 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.

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/

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" <(E-Mail Removed)> wrote in
message
news:5771cfe3-2936-4f28-ac43-(E-Mail Removed)...
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

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



On Apr 3, 11:14*am, "Access Developer" <accde...@gmail.com> wrote:
> 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.ke...@gmail.com> wrotein
> messagenews:5771cfe3-2936-4f28-ac43-(E-Mail Removed)...
> 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

"Access Developer" <(E-Mail Removed)> wrote in
news:(E-Mail Removed):

> 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.

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/

On Tue, 5 Apr 2011 16:33:25 -0700 (PDT), "a a r o n . k e m p f @ g m
a i l . c o m" <(E-Mail Removed)> wrote:

>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/

"a a r o n . k e m p f @ g m a i l . c o m" <(E-Mail Removed)>
wrote in
news:ce7d7362-f933-42c9-ba22-(E-Mail Removed)
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.

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/

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


On Apr 9, 2:21*pm, "David-W-Fenton" <NoEm...@SeeSignature.invalid>
wrote:
> "a a r o n . k e m p f @ g m a i l . c o m" <aaron.ke...@gmail.com>
> wrote innews:ce7d7362-f933-42c9-ba22-(E-Mail Removed)
> 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.
>
> --
> David W. Fenton * * * * * * * * *http://www.dfenton.com/
> contact via website only * *http://www.dfenton.com/DFA/