DAO operation issues runtime err3003: could not start transaction

[Kosei B]

Hi there,
If you have any idea why I get the below error message when I execute sql
using DAO?
The error message says: Error 3003 Could not start transaction/ Too many
transaction already nested.

I was testing some example codes that use transactions, but after getting
this error, I removed all the transactions and workspace, like ws.begintrans,
ws,committrans.
For my purpose, using transaction is not so crutial. I just need to append
and delete records as users change the selections on the list, and get record
set to show the current state of the table.
The codes I get this error for are:

Private Sub Form_Current()

dim rs as DAO.recordset
dim strSQL as string

Set rs = CurrentDb.OpenRecordset("Select [_camellia_id] From [Tea has
Camellia] Where [_tea_product_detail_id] = " & Me.txtTeaProductDetailID.Value)
End Sub

Private Sub lstCamelliaKind_AfterUpdate()
dim db as DAO.Database
dim strSQL as string
dim varItem as Variant

set db = DBEngine(0)(0)
strSQL = "Delete FROM [Tea has Camellia] Where [_tea_product_detail_id] = "
& Me.txtTeaProductDetailID.Value

With Me.lstCamelliaKind
For Each varItem In .ItemSelected

strSQL = "Insert INTO [Tea has Camellia] (_tea_product_detail_id,
_camellia_id) VALUE (" & me.txtTeaProductDetailID &" , " & .ItemData(varItem)
& ")"
Next varItem
End with

Thank you very much in advance,

Kosei

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Use

Set db = CurrentDB

Correct this:

strSQL = "Delete FROM etc. ..."

to

strSQL = "Delete * FROM etc. ..."

This assumes you are using Access (JET) tables.

Use the word "VALUES" instead of "VALUE" in the INSERT INTO statement.

Not as important, but use ! instead of . to indicate a form's control,
e.g.:

Me!txtTeaProductDetailID

Sometimes when developing and using DAO Transactions, the DB gets
"confused," because of some errors during the VBA run of the
Transactions. It is better to save all changes to the project, close
Access & then reopen the project & try the Transaction routine again.
The closing will get rid of any lingering Transactions that were not
completed due to the errors in the VBA code.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRI7rboechKqOuFEgEQJUDgCfQF11BJBSdSFCrUZA5fKe8IXwAXkAoKB5
MtczLUMZmNUZyMUbuOgQ2NvP
=KRu6
-----END PGP SIGNATURE-----

 

[Kosei B]

Thank you, MG Foster!
It worked out fine, and I am embarrased not to have turned off and on the
system before asking you questions.
Thanks again!

Kosei

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Use

Set db = CurrentDB

Correct this:

strSQL = "Delete FROM etc. ..."

to

strSQL = "Delete * FROM etc. ..."

This assumes you are using Access (JET) tables.

Use the word "VALUES" instead of "VALUE" in the INSERT INTO statement.

Not as important, but use ! instead of . to indicate a form's control,
e.g.:

Me!txtTeaProductDetailID

Sometimes when developing and using DAO Transactions, the DB gets
"confused," because of some errors during the VBA run of the
Transactions. It is better to save all changes to the project, close
Access & then reopen the project & try the Transaction routine again.
The closing will get rid of any lingering Transactions that were not
completed due to the errors in the VBA code.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRI7rboechKqOuFEgEQJUDgCfQF11BJBSdSFCrUZA5fKe8IXwAXkAoKB5
MtczLUMZmNUZyMUbuOgQ2NvP
=KRu6
-----END PGP SIGNATURE-----

Hi there,
If you have any idea why I get the below error message when I execute sql
using DAO?
The error message says: Error 3003 Could not start transaction/ Too many
transaction already nested.

I was testing some example codes that use transactions, but after getting
this error, I removed all the transactions and workspace, like ws.begintrans,
ws,committrans.
For my purpose, using transaction is not so crutial. I just need to append
and delete records as users change the selections on the list, and get record
set to show the current state of the table.
The codes I get this error for are:

Private Sub Form_Current()

dim rs as DAO.recordset
dim strSQL as string

Set rs = CurrentDb.OpenRecordset("Select [_camellia_id] From [Tea has
Camellia] Where [_tea_product_detail_id] = " & Me.txtTeaProductDetailID.Value)
End Sub

Private Sub lstCamelliaKind_AfterUpdate()
dim db as DAO.Database
dim strSQL as string
dim varItem as Variant

set db = DBEngine(0)(0)
strSQL = "Delete FROM [Tea has Camellia] Where [_tea_product_detail_id] = "
& Me.txtTeaProductDetailID.Value

With Me.lstCamelliaKind
For Each varItem In .ItemSelected

strSQL = "Insert INTO [Tea has Camellia] (_tea_product_detail_id,
_camellia_id) VALUE (" & me.txtTeaProductDetailID &" , " & .ItemData(varItem)
& ")"
Next varItem
End with