Problem Executing Stored Procedure from VBA Code

[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

************************************************************
And here is the stored procedure itself:
************************************************************

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

 

[markros]

Why do you need transaction here? Try to comment out begin transaction / commit part.

Also the EXISTS check seem to be redundant - see if we can comment it out too.

 

[TTops]

I found my own answer. A stored procedure that runs before all the code I provided earlier was locking the table because I was returning a value before reaching the "Commit" statement. This was my own fault, but thanks for the response, markros.

Thanks,
T-Tops