从VBA调用时，存储过程中的删除会以静默方式失败

I am trying to delete certain rows out of my database by a Artikelnr (articleNr in Dutch).

我试图通过Artikelnr删除我的数据库中的某些行（articleNr in Dutch）。

When I try to execute my code from access it doesn't throw an error, but it just doesn't delete anything either.

当我尝试从访问中执行我的代码时，它不会抛出错误，但它也不会删除任何内容。

Am I missing something here?

我在这里错过了什么吗？

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name =      'spVerwijderArtikel') 
    DROP PROCEDURE spVerwijderArtikel
GO 

CREATE PROCEDURE spVerwijderArtikel
    (@ArtikelNr integer)
AS 
BEGIN Transaction
    DELETE FROM artikelprijs
    WHERE ArtikelNr = @ArtikelNr

    DELETE FROM Artikel 
    WHERE ArtikelNr = @ArtikelNr

    IF @@ERROR <> 0
    BEGIN
        ROLLBACK
        RAISERROR ('Error tijdens het uitvoeren', 16 , 1)
        RETURN
    END 

    COMMIT
GO

And my code from access trying to execute the stored procedure:

我的代码来自访问尝试执行存储过程：

Private Sub Command2_Click()

Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sConnString As String
sConnString = "Provider=SQLOLEDB;Data Source=.\SQLEXPRESS;" & _
  "Initial Catalog=KlantArtikelOpdracht;" & _
  "Integrated Security=SSPI;"

Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset

conn.Open sConnString
Set rs = conn.Execute("EXEC spVerwijderArtikel'" & TxTArtikelNr & "'")

End Sub

Could someone help me with this issue?

有人可以帮我解决这个问题吗？

1 个解决方案

#1

Add the statement

添加语句

SET NOCOUNT ON;

as the very first statement of your stored procedure (before BEGIN Transaction). That will allow the RAISERROR to be passed back to the VBA code if an error occurs when you execute the stored procedure.

作为存储过程的第一个语句（在BEGIN事务之前）。如果在执行存储过程时发生错误，则允许将RAISERROR传递回VBA代码。

Other suggestions:

其他建议：

Don't use a Recordset since the stored procedure does not return rows.

不要使用Recordset，因为存储过程不返回行。

Consider using an ADODB.Command object with .CommandType = adCmdStoredProc and a proper ADODB.Parameter to specify the @ArtikelNr value.

考虑使用带有.CommandType = adCmdStoredProc的ADODB.Command对象和正确的ADODB.Parameter来指定@ArtikelNr值。

#1