Transactions in Access 2000

[Karen Hill]

Hi.

I need to use transactions to make sure data is consistent. I have a
combo box that when changed to a new value updates a table. I then
have a DoCmd.RunSQL statement following that in the same Sub. I would
like for it for those two things to be wrapped up in one transaction,
with the user being informed that a transaction has rolled back.

I have not implimented transaction in Access so code bits would be
helpful to me. I am also trying to avoid using any References like ADO
as to make the db as portable between Access versions greater than
2000.

Thank you in advance.

[John Mishefske]

Karen Hill wrote:

Hi.

I need to use transactions to make sure data is consistent. I have a
combo box that when changed to a new value updates a table. I then
have a DoCmd.RunSQL statement following that in the same Sub. I would
like for it for those two things to be wrapped up in one transaction,
with the user being informed that a transaction has rolled back.

I have not implimented transaction in Access so code bits would be
helpful to me. I am also trying to avoid using any References like ADO
as to make the db as portable between Access versions greater than
2000.

You'll have to abandon the DoCmd.RunSQL and use either the DAO or ADO Object
Model (which means setting a reference) but if you use ADO you shouldn't
have a portability problem.

You'll have to search for a transaction code sample. I'm familiar with
transactions in DAO but not ADO.

--
'---------------
'John Mishefske
'---------------

[David W. Fenton]

John Mishefske <jm**********@S PAMyahoo.com> wrote in
news:kn******** ******@tornado. rdc-kc.rr.com:

Karen Hill wrote:

Hi.

I need to use transactions to make sure data is consistent. I
have a combo box that when changed to a new value updates a
table. I then have a DoCmd.RunSQL statement following that in
the same Sub. I would like for it for those two things to be
wrapped up in one transaction, with the user being informed that
a transaction has rolled back.

I have not implimented transaction in Access so code bits would
be helpful to me. I am also trying to avoid using any References
like ADO as to make the db as portable between Access versions
greater than 2000.
You'll have to abandon the DoCmd.RunSQL and use either the DAO or
ADO Object Model (which means setting a reference) but if you use
ADO you shouldn't have a portability problem.

Eh? DAO will solve the portability problem, not ADO, which was not
natively supported in any version of Access before 2000.
You'll have to search for a transaction code sample. I'm familiar
with transactions in DAO but not ADO.

I don't understand why anyone would contemplate doing something as
"close to the metal" as transactions in anything but the db engine's
native data access language, in this case, DAO.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Tom van Stiphout]

On Fri, 10 Feb 2006 19:03:54 -0600, "David W. Fenton"
<XX*******@dfen ton.com.invalid > wrote:

Oh, come on. Show us one case where ADO transactions don't work and
DAO ones do.

-Tom.

John Mishefske <jm**********@S PAMyahoo.com> wrote in
news:kn******* *******@tornado .rdc-kc.rr.com:

Karen Hill wrote:

Hi.

I need to use transactions to make sure data is consistent. I
have a combo box that when changed to a new value updates a
table. I then have a DoCmd.RunSQL statement following that in
the same Sub. I would like for it for those two things to be
wrapped up in one transaction, with the user being informed that
a transaction has rolled back.

I have not implimented transaction in Access so code bits would
be helpful to me. I am also trying to avoid using any References
like ADO as to make the db as portable between Access versions
greater than 2000.

You'll have to abandon the DoCmd.RunSQL and use either the DAO or
ADO Object Model (which means setting a reference) but if you use
ADO you shouldn't have a portability problem.

Eh? DAO will solve the portability problem, not ADO, which was not
natively supported in any version of Access before 2000.

You'll have to search for a transaction code sample. I'm familiar
with transactions in DAO but not ADO.

I don't understand why anyone would contemplate doing something as
"close to the metal" as transactions in anything but the db engine's
native data access language, in this case, DAO.

[John Mishefske]

David W. Fenton wrote:

John Mishefske <jm**********@S PAMyahoo.com> wrote in

I need to use transactions to make sure data is consistent. I
have a combo box that when changed to a new value updates a
table. I then have a DoCmd.RunSQL statement following that in
the same Sub. I would like for it for those two things to be
wrapped up in one transaction, with the user being informed that
a transaction has rolled back.

I have not implimented transaction in Access so code bits would
be helpful to me. I am also trying to avoid using any References
like ADO as to make the db as portable between Access versions
greater than 2000.

You'll have to abandon the DoCmd.RunSQL and use either the DAO or
ADO Object Model (which means setting a reference) but if you use
ADO you shouldn't have a portability problem.

Eh? DAO will solve the portability problem, not ADO, which was not
natively supported in any version of Access before 2000.

Agreed. But the poster mentioned A2000 and later.

You'll have to search for a transaction code sample. I'm familiar
with transactions in DAO but not ADO.

I don't understand why anyone would contemplate doing something as
"close to the metal" as transactions in anything but the db engine's
native data access language, in this case, DAO.

I agree personally but could not authoritatively state that ADO is a bad choice.

--
'---------------
'John Mishefske
'---------------

[David W. Fenton]

Tom van Stiphout <no************ *@cox.net> wrote in
news:8k******** *************** *********@4ax.c om:

Oh, come on. Show us one case where ADO transactions don't work
and DAO ones do.

I didn't say they didn't. I just said I see no benefit to use ADO in
a situation where you're using Jet.

Now, if you're contemplating changing away from Jet as your data
store, that's a different story, but no one has mentioned that as
one of the issues in play.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[David W. Fenton]

John Mishefske <jm**********@S PAMyahoo.com> wrote in
news:Tb******** ********@tornad o.rdc-kc.rr.com:

David W. Fenton wrote:

John Mishefske <jm**********@S PAMyahoo.com> wrote in

You'll have to abandon the DoCmd.RunSQL and use either the DAO or
ADO Object Model (which means setting a reference) but if you use
ADO you shouldn't have a portability problem.

Eh? DAO will solve the portability problem, not ADO, which was
not natively supported in any version of Access before 2000.

Agreed. But the poster mentioned A2000 and later.

But there is absolutely no portability problem with DAO in A2K and
later, so the comment makes no sense at all in the context of A2K
and higher. That's why I read it as referring to pre-A2K.

You'll have to search for a transaction code sample. I'm familiar
with transactions in DAO but not ADO.

I don't understand why anyone would contemplate doing something
as "close to the metal" as transactions in anything but the db
engine's native data access language, in this case, DAO.

I agree personally but could not authoritatively state that ADO is
a bad choice.

In my opinion, it's a bad choice for any all-Jet application, even
if it would work just fine. The only thing worse than completely
replacing DAO with ADO in a Jet application would be mixing some DAO
and some ADO.

In a Jet context, there is simply no reason to use ADO for anything
that DAO can actually do. So far as I know there are only two things
ADO can do that Jet can't, and both of them have alternative
solutions that don't require ADO at all.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Tim Marshall]

Tom van Stiphout wrote:

On Fri, 10 Feb 2006 19:03:54 -0600, "David W. Fenton"
<XX*******@dfen ton.com.invalid > wrote:

Oh, come on. Show us one case where ADO transactions don't work and
DAO ones do.

Hi Tom,

Not transaction related, but I was bitterly, bitterly disappointed with
ADO for mdbs written against my normal back end, Oracle. I'd heard so
much about DSNless connections over a number of years here and when I
finally jumped into A2003 from A97 a year-and-a-half ago, I excitedly
looked into it. I write a lot of Access FE reporting applications for a
local network, but ADO recordsources are not compatible with Access
reports. 8(

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto