Unable to BeginTransaction

[Jean-Marc]

Sorry for posting again the same problem but I had no answer on my
first try last week.

I am Using Access 2003 on a straight .mdb database.

I made the following code in VBA code of a Form callback function just
to prove transactions are working fine:

CurrentProject.Connection.BeginTrans
Dim rsTAnnee As New ADODB.Recordset
rsTAnnee.Open "SELECT * FROM Annee", CurrentProject.Connection,

adOpenDynamic, adLockOptimistic
rsTAnnee.AddNew
rsTAnnee!annee = 1999
rsTAnnee.Update
CurrentProject.Connection.RollbackTrans

when execution reaches 'RollbackTrans', the system always throw the
error: 'You tried to commit or rollback a transaction without first
beginning a transaction'.

Documentation say that if the 'Transaction DDL' entry exists in the
properties of the connection then the underlying database supports
transactions. I checked and it is there ... actually, its value is 16
which I can't figure the meaning of.

Is there something to be done in order to have transactions enabled?
Is there something wrong in the code?

Thank you for helping.

Jean-Marc.

 

[Baz]

Sorry for posting again the same problem but I had no answer on my
first try last week.

I am Using Access 2003 on a straight .mdb database.

I made the following code in VBA code of a Form callback function just
to prove transactions are working fine:

CurrentProject.Connection.BeginTrans
Dim rsTAnnee As New ADODB.Recordset
rsTAnnee.Open "SELECT * FROM Annee", CurrentProject.Connection,

adOpenDynamic, adLockOptimistic
rsTAnnee.AddNew
rsTAnnee!annee = 1999
rsTAnnee.Update
CurrentProject.Connection.RollbackTrans

when execution reaches 'RollbackTrans', the system always throw the
error: 'You tried to commit or rollback a transaction without first
beginning a transaction'.

Documentation say that if the 'Transaction DDL' entry exists in the
properties of the connection then the underlying database supports
transactions. I checked and it is there ... actually, its value is 16
which I can't figure the meaning of.

Is there something to be done in order to have transactions enabled?
Is there something wrong in the code?

Thank you for helping.

Jean-Marc.

I don't know about this specific problem, but it is not a good idea to use
ADO with mdb databases. In my experience it works very poorly, with
transactions being about the worst area. Stick to DAO, it works much
better.

 

[Dirk Goldgar]

Sorry for posting again the same problem but I had no answer on my
first try last week.

I am Using Access 2003 on a straight .mdb database.

I made the following code in VBA code of a Form callback function just
to prove transactions are working fine:

CurrentProject.Connection.BeginTrans
Dim rsTAnnee As New ADODB.Recordset
rsTAnnee.Open "SELECT * FROM Annee",
CurrentProject.Connection,

adOpenDynamic, adLockOptimistic
rsTAnnee.AddNew
rsTAnnee!annee = 1999
rsTAnnee.Update
CurrentProject.Connection.RollbackTrans

when execution reaches 'RollbackTrans', the system always throw the
error: 'You tried to commit or rollback a transaction without first
beginning a transaction'.

Documentation say that if the 'Transaction DDL' entry exists in the
properties of the connection then the underlying database supports
transactions. I checked and it is there ... actually, its value is 16
which I can't figure the meaning of.

Is there something to be done in order to have transactions enabled?
Is there something wrong in the code?

It seems to work if you set a Connection object and work with that:

Dim conn As ADODB.Connection
Dim rsTAnnee As New ADODB.Recordset

Set conn = CurrentProject.Connection

conn.BeginTrans
rsTAnnee.Open "SELECT * FROM Table1", conn, adOpenDynamic,
adLockOptimistic
rsTAnnee.AddNew
rsTAnnee!annee = 1999
rsTAnnee.Update
rsTAnnee.Close
conn.RollbackTrans