Problem Executing SQL Stored Procedure in VBA

[TTops]

Greetings,

I have created an Access 2007 ADP file that is connected to a SQL Server 2000 database. When I execute a certain stored procedure that updates a record, the data is apparently saved because I requery the underlying form and can see the new values, but then I can't open the table in Enterprise Manager (error: Timeout Expired) until I close the Access project entirely and then the table will open but the data is not there. There are no exceptions thrown when running the code, neither Access or SQL.

Here is the call to the stored procedure:
***********************************************************
Dim cnxn As ADODB.Connection
Set cnxn = Application.CurrentProject.Connection
Dim cmdsp As New ADODB.Command

cmdsp.ActiveConnection = cnxn
cmdsp.CommandText = "PutBOMPartDescription"
cmdsp.CommandType = adCmdStoredProc
cmdsp.Parameters.Refresh
cmdsp.Parameters("@BOMPieceMarkID").Value = BOMPieceMarkID
cmdsp.Parameters("@PartModelBOMDescription").Value = BOMPartDescription

x = cmdsp.Execute()

cnxn.Close
Set cnxn = Nothing
************************************************************

Here is the stored procedure:
************************************************************

CREATE PROCEDURE PutBOMPartDescription

@BOMPieceMarkID int,
@PartModelBOMDescription nvarchar(500)

AS
Begin Transaction
Begin
if exists (Select * from tblBOMPieceMarks where BOMPieceMarkID = @BOMPieceMarkID)
Update tblBOMPieceMarks
Set PartModelBOMDescription = @PartModelBOMDescription
where BOMPieceMarkID = @BOMPieceMarkID
End
Commit
GO
************************************************************



Thanks,
T-Tops

 

[genomon]

Try getting rid of the transaction - changes can't post until trans is comitted.

If quizzes are quizzical, what are tests?

 

[TTops]

That fixed it...thanks, Genomon.

Thanks,
T-Tops