Hi,

I am kind of baffled with the problem that I am currently encountering. I have a Access 97 application that was converted to Access XP. The function listed below was executing correctly in Access 97 and still continues to execute correctly in Win 2000 machines and some Win XP machines. However, in most of the Win XP machines, the function seems to execute correctly at run time. However, when access is closed completely and go back in, it appears that the function did not execute at all. The attached database is SQL Server 2000 database. Also, it appears that the function is creating locks on the SQL Server tables (at run time). However, when Access is closed, the locks are released and all the results seems to be rolled back to previous state. IF I remove the BeginTrans/CommitTrans then the function works fine. I am using DAO 3.6 Object Library(reference checked). Would appreciate quick responses :-)

The function looks like this:


Dim qryArBatch As DAO.QueryDef
Dim qryPostPayment As DAO.QueryDef
Dim qryArBatchByYear As DAO.QueryDef
Dim qryARPayment As DAO.QueryDef
Dim i As Integer
Dim j As Integer
' Dim rsBatchList As DAO.Recordset
' Dim rsARBatch As DAO.Recordset
Dim fMyForm As Form
Dim ctlTargetList As Control
Dim ntransactionFlag As Integer
Dim g_wsMyWorkSpace As DAO.Workspace


On Error GoTo PostSelectedBatch_err

DoCmd.Hourglass True
bFunctionSuccess = False
ntransactionFlag = False

Set fMyForm = Forms("ReportManager")
Set ctlTargetList = fMyForm("lbxTarget")
If ctlTargetList.ListCount < 1 Then
Beep
MsgBox "Select Batch first"
DoCmd.Hourglass False
Exit Function
End If


Set g_wsMyWorkSpace = DAO.DBEngine.Workspaces(0)


g_wsMyWorkSpace.BeginTrans ' g_wsMyWorkSpace.BeginTrans
For i = 1 To ctlTargetList.ListCount

ntransactionFlag = True

Set qryPostPayment = g_wsMyWorkSpace.Databases(0).QueryDefs("q_PostPaym entToARHistory")
qryPostPayment.Parameters(0) = ctlTargetList.ItemData(i - 1)
qryPostPayment.Execute
qryPostPayment.Close
DoEvents

Set qryArBatchByYear = g_wsMyWorkSpace.Databases(0).QueryDefs("q_DeleteAR BatchByYear")
qryArBatchByYear.Parameters(0) = ctlTargetList.ItemData(i - 1)
qryArBatchByYear.Execute
qryArBatchByYear.Close
DoEvents

Set qryARPayment = g_wsMyWorkSpace.Databases(0).QueryDefs("q_DeleteAR Payment")
qryARPayment.Parameters(0) = ctlTargetList.ItemData(i - 1)
qryARPayment.Execute
qryARPayment.Close
DoEvents

Set qryArBatch = g_wsMyWorkSpace.Databases(0).QueryDefs("q_UpdateAR BatchControl")
qryArBatch.Parameters(0) = ctlTargetList.ItemData(i - 1)
qryArBatch.Execute
qryArBatch.Close
DoEvents

Next
g_wsMyWorkSpace.CommitTrans
'g_wsMyWorkSpace.CommitTrans

Forms!ReportManager!lbxTarget.RowSource = ""
Forms!ReportManager!lbxSource.Requery
bFunctionSuccess = True

GoTo PostSelectedBatch_end

PostSelectedBatch_err:
DoCmd.Hourglass False
If ntransactionFlag Then
g_wsMyWorkSpace.Rollback
End If
sMsgText = "Access Basic Error " & Err & "; " & Error$
sMsgTitle = "PostSelectedBatch"
nMsgResponse = MsgBox(sMsgText, MB_OK, sMsgTitle)
Resume PostSelectedBatch_end
PostSelectedBatch_end:
DoCmd.Hourglass False
g_wsMyWorkSpace.Close
Set g_wsMyWorkSpace = Nothing
DoEvents
PostSelectedBatch = bFunctionSuccess