Transaction problem

  • Thread starter Thread starter Wei Wang
  • Start date Start date
W

Wei Wang

This transaction deals with two linked SQL Server tables from same database,
tblRmaster and tblRitem, which has a master-detail relationship through a
composit key.

transaction code is like this:
////////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////////////////
///////////////////////////
WS.BeginTrans

lngRectNo = DLookup("[Receipt#]", "[tblRect#]") + 1
Set qdef1 = Db.CreateQueryDef("", "INSERT INTO
tblRmaster([EntityNo],[Receipt],[SeqNo],[RecType],[RecDate],[RecTime],[Name]
,[RecDesc],[RecQty],[RecTotal],[RecCode],[TransAmt],[CheckNo],[AmtTend],[Cas
e#],[CtCode],[Year],[CtDiv],[Seq#],[PreNumb],[PartyCode],[PartyNumber],[OPNA
ME],[Bond],[BaseFine]) " & _
"SELECT [EntityNo]," & lngRectNo &
",[SeqNo],[RecType],[RecDate],[RecTime],[Name],[RecDesc],[RecQty],[RecTotal]
,[RecCode],[TransAmt],[CheckNo],[AmtTend],Case#],[CtCode],[Year],[CtDiv],[Se
q#],[PreNumb],[PartyCode],[PartyNumber],[OPNAME],[Bond],[BaseFine] FROM
tblRmasterTemp ORDER By SeqNo")
qdef1.Execute

Set qdef2 = Db.CreateQueryDef("", "INSERT INTO tblRitem (EntityNo,
Receipt, SeqNo, RecType, FundType, FundDesc, TransAmt, DebitCredit,
RectAcct, DisbAcct, VendorNumb, RecDate, RecCode, PayCode, SumDetail,
OPNAME, CalCode) " & _
"SELECT EntityNo, " & lngRectNo & ", SeqNo, RecType,
FundType, FundDesc, TransAmt, DebitCredit, RectAcct, DisbAcct, VendorNumb,
RecDate, RecCode, PayCode, SumDetail, OPNAME, CalCode " & _
"FROM tblRitemNew")
qdef2.Execute

WS.CommitTrans
////////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////////////////
////////////////////////////

if I don't use transaction, the code will work.

if I use transaction, the code qdef2.Execute gave error message
"ODBC--Insert on a linked table 'tblRitem' failed".
If I remove the relationship between those two tables, the code will work.
 
I don't believe that Access transactions play happily with SQL Server. The
preferred way to do this is to create a SQL stored procedure and wrap your
statements in a transaction there. Here's a sample structure. Note that
you need to check the global error condition after each statement to break
out of the transaction:

BEGIN TRAN

INSERT INTO tblRmaster (
EntityNo
,Receipt
,...
) VALUES (
@EntityNo
,@Receipt
...
)
IF @@ERROR <> 0 GOTO ErrHandler

INSERT INTO tblRitem (
EntityNo
,Receipt
,...
) VALUES (
@EntityNo
,@Receipt
...
)
IF @@ERROR <> 0 GOTO ErrHandler

COMMIT TRAN
RETURN

ErrHandler:
ROLLBACK TRAN
RETURN

Note that if you want to insert multiple items, you could have a temporary
holding table in SQL Server, and insert from that. If this system is
multi-user, you'd have to ensure that the temporary data could be kept
separate for your current session.

Wei Wang said:
This transaction deals with two linked SQL Server tables from same database,
tblRmaster and tblRitem, which has a master-detail relationship through a
composit key.

transaction code is like this:
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
///////////////////////////
WS.BeginTrans

lngRectNo = DLookup("[Receipt#]", "[tblRect#]") + 1
Set qdef1 = Db.CreateQueryDef("", "INSERT INTO
tblRmaster([EntityNo],[Receipt],[SeqNo],[RecType],[RecDate],[RecTime],[Name],[RecDesc],[RecQty],[RecTotal],[RecCode],[TransAmt],[CheckNo],[AmtTend],[Case#],[CtCode],[Year],[CtDiv],[Seq#],[PreNumb],[PartyCode],[PartyNumber],[OPNA
ME],[Bond],[BaseFine]) " & _
"SELECT [EntityNo]," & lngRectNo &
",[SeqNo],[RecType],[RecDate],[RecTime],[Name],[RecDesc],[RecQty],[RecTotal],[RecCode],[TransAmt],[CheckNo],[AmtTend],Case#],[CtCode],[Year],[CtDiv],[Se
q#],[PreNumb],[PartyCode],[PartyNumber],[OPNAME],[Bond],[BaseFine] FROM
tblRmasterTemp ORDER By SeqNo")
qdef1.Execute

Set qdef2 = Db.CreateQueryDef("", "INSERT INTO tblRitem (EntityNo,
Receipt, SeqNo, RecType, FundType, FundDesc, TransAmt, DebitCredit,
RectAcct, DisbAcct, VendorNumb, RecDate, RecCode, PayCode, SumDetail,
OPNAME, CalCode) " & _
"SELECT EntityNo, " & lngRectNo & ", SeqNo, RecType,
FundType, FundDesc, TransAmt, DebitCredit, RectAcct, DisbAcct, VendorNumb,
RecDate, RecCode, PayCode, SumDetail, OPNAME, CalCode " & _
"FROM tblRitemNew")
qdef2.Execute

WS.CommitTrans
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
////////////////////////////

if I don't use transaction, the code will work.

if I use transaction, the code qdef2.Execute gave error message
"ODBC--Insert on a linked table 'tblRitem' failed".
If I remove the relationship between those two tables, the code will work.
Click to expand...
 
Maybe stored procedure and SQL server temperary solution is best from design
perspective, but I am here fixing a system using access temperary table
heavily... Change to SP will cause me much more time.

I hope I can fix my problem and keep the original code if possible.

Jim said:
I don't believe that Access transactions play happily with SQL Server. The
preferred way to do this is to create a SQL stored procedure and wrap your
statements in a transaction there. Here's a sample structure. Note that
you need to check the global error condition after each statement to break
out of the transaction:

BEGIN TRAN

INSERT INTO tblRmaster (
EntityNo
,Receipt
,...
) VALUES (
@EntityNo
,@Receipt
...
)
IF @@ERROR <> 0 GOTO ErrHandler

INSERT INTO tblRitem (
EntityNo
,Receipt
,...
) VALUES (
@EntityNo
,@Receipt
...
)
IF @@ERROR <> 0 GOTO ErrHandler

COMMIT TRAN
RETURN

ErrHandler:
ROLLBACK TRAN
RETURN

Note that if you want to insert multiple items, you could have a temporary
holding table in SQL Server, and insert from that. If this system is
multi-user, you'd have to ensure that the temporary data could be kept
separate for your current session.

Wei Wang said:
This transaction deals with two linked SQL Server tables from same database,
tblRmaster and tblRitem, which has a master-detail relationship through a
composit key.

transaction code is like this:
Click to expand...
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
///////////////////////////
WS.BeginTrans

lngRectNo = DLookup("[Receipt#]", "[tblRect#]") + 1
Set qdef1 = Db.CreateQueryDef("", "INSERT INTO
Click to expand...
tblRmaster([EntityNo],[Receipt],[SeqNo],[RecType],[RecDate],[RecTime],[Name],[RecDesc],[RecQty],[RecTotal],[RecCode],[TransAmt],[CheckNo],[AmtTend],[Case#],[CtCode],[Year],[CtDiv],[Seq#],[PreNumb],[PartyCode],[PartyNumber],[OPNA
ME],[Bond],[BaseFine]) " & _
"SELECT [EntityNo]," & lngRectNo &
Click to expand...
",[SeqNo],[RecType],[RecDate],[RecTime],[Name],[RecDesc],[RecQty],[RecTotal],[RecCode],[TransAmt],[CheckNo],[AmtTend],Case#],[CtCode],[Year],[CtDiv],[Se
q#],[PreNumb],[PartyCode],[PartyNumber],[OPNAME],[Bond],[BaseFine] FROM
tblRmasterTemp ORDER By SeqNo")
qdef1.Execute

Set qdef2 = Db.CreateQueryDef("", "INSERT INTO tblRitem (EntityNo,
Receipt, SeqNo, RecType, FundType, FundDesc, TransAmt, DebitCredit,
RectAcct, DisbAcct, VendorNumb, RecDate, RecCode, PayCode, SumDetail,
OPNAME, CalCode) " & _
"SELECT EntityNo, " & lngRectNo & ", SeqNo, RecType,
FundType, FundDesc, TransAmt, DebitCredit, RectAcct, DisbAcct, VendorNumb,
RecDate, RecCode, PayCode, SumDetail, OPNAME, CalCode " & _
"FROM tblRitemNew")
qdef2.Execute

WS.CommitTrans
Click to expand...
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
////////////////////////////

if I don't use transaction, the code will work.

if I use transaction, the code qdef2.Execute gave error message
"ODBC--Insert on a linked table 'tblRitem' failed".
If I remove the relationship between those two tables, the code will work.
Click to expand...
Click to expand...
 
Back
Top