パソコン情報

【Access】戻り値で判別しながらストアド実行を確認する

Accessからストアドを実行するVBAを作成しましたが、実際にストアドがちゃんと動いたのかどうかを確認するのに、テーブルの値を確認してみたりと大変です。

もっと簡単に

ポイント

ストアドが実行されたのかを確認する方法としてSqlserverからの戻り値からのAccess側でのメッセージと、現時点の最もシンプルなストアドの実行方法

などをまとめておきたいと思います。

サンプルVBAコード

Private Sub job_btn_Click()
  Dim cn As New ADODB.Connection
  Dim cmd As New ADODB.Command

  Dim anser As String

On Error GoTo ERR_HANDLER
    
    'SQLServer接続設定&接続
    With cn
        .Provider = "SQLOLEDB"
        .ConnectionString = "Data Source=servername\SQLEXPRESS;" & _
            "Initial Catalog=DB2;" & _
            "User ID=user2;" & _
            "Password=japan2020$$"
        .Open
    End With

  'ストアドプロシージャの実行情報を設定
  cmd.ActiveConnection = cn
  cmd.CommandType = adCmdStoredProc 'コマンドの種類
  cmd.CommandText = "test_calc" 'ストアドプロシージャ名
  
  'ストアドプロシージャへの引数を設定
  '入力引数
  cmd.Parameters("@input_no").Value = Forms![dbo_data]![input_no]
  
  'ストアドプロシージャの実行
  cmd.Execute
  
	  '実行結果の表示
	   anser = cmd.Parameters("@ans").Value

'               戻り値の判断& 表示用メッセージ生成
                If cmd.Parameters(0).Value = 0 Then
                    MsgBox anser & "出荷予定数の計算は正常終了しました。"
                Else
                    MsgBox "異常終了しました" & vbCrLf
                End If

	  'オブジェクトのクローズ
	  cn.close
	        
	  'オブジェクトの開放
	  Set cmd = Nothing
	  Set cn = Nothing
  
Exit Sub
    
ERR_HANDLER:
  'エラーメッセージ
  MsgBox Err.Number & ")" & Err.Description
  'オブジェクトの開放
  Set cmd = Nothing
  Set cn = Nothing

End Sub

サンプルストアドコード

USE [DB2]
GO
/****** Object:  StoredProcedure [dbo].[test_calc]    Script Date: 2020/08/20 14:23:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[test_calc]
@input_no int,
@ans VARCHAR(50)OUTPUT
AS
BEGIN TRY

    BEGIN TRANSACTION

UPDATE dbo.[data]
SET dbo.[data].kazu = dbo.[data].kazu+1
WHERE dbo.[data].no = @input_no

SELECT @ans=dbo.[data].answer
FROM dbo.data
WHERE dbo.[data].no = @input_no

    COMMIT TRANSACTION
  
END TRY
  
BEGIN CATCH
    ROLLBACK TRANSACTION
  
    RETURN ERROR_NUMBER()
  
END CATCH
  
RETURN 0

実行すると戻り値のメッセージ

ストアドを実行して何の問題もなければ、Access側でメッセージを出すようにしました。

わざと、ストアドがエラーを出すように、

@input_no int,

を@input_no1 int,などの関係ない変数にしてエラーを返すようにしました。

つまり、今回のサンプルコードで、ちゃんとSqlserver上のストアドが実行されて、戻り値によりメッセージが変わることが確認することができました。

フォームでSqlServer接続設定をする

接続情報が変わったりすると、いちいちコードを編集するのが面倒だってことがあります。

ひとつテーブル(例odbc)を作成して、フォームにして簡単にODBC接続設定をするようにできるようにしました。

Private Sub job_btn_Click()
  Dim cn As New ADODB.Connection
  Dim cmd As New ADODB.Command
  Dim anser As String
  
'    Dim idsn As String
    Dim idatabase As String
    Dim iuid As String
    Dim ipwd As String
    Dim iserver As String
    
'    idns = DLookup("DSN", "odbc", "ID = 1")
    idatabase = DLookup("DATABASE", "odbc", "ID = 1")
    iuid = DLookup("UID", "odbc", "ID = 1")
    ipwd = DLookup("PWD", "odbc", "ID = 1")
    iserver = DLookup("SERVER", "odbc", "ID = 1")

On Error GoTo ERR_HANDLER
    
    'SQLServer接続設定&接続
    With cn
        .Provider = "SQLOLEDB"
        .ConnectionString = "Data Source=" & iserver & ";" & _
            "Initial Catalog=" & idatabase & ";" & _
            "User ID=" & iuid & ";" & _
            "Password=" & ipwd & ""

            Debug.Print .ConnectionString
        .Open
    End With

  'ストアドプロシージャの実行情報を設定
  cmd.ActiveConnection = cn
  cmd.CommandType = adCmdStoredProc 'コマンドの種類
  cmd.CommandText = "test_calc" 'ストアドプロシージャ名
  
  'ストアドプロシージャへの引数を設定
  '入力引数
  cmd.Parameters("@input_no").Value = Forms![dbo_data]![input_no]
  
  'ストアドプロシージャの実行
  cmd.Execute
  
      '実行結果の表示
       anser = cmd.Parameters("@ans").Value

'               戻り値の判断& 表示用メッセージ生成
                If cmd.Parameters(0).Value = 0 Then
                    MsgBox anser & "出荷予定数の計算は正常終了しました。"
                Else
                    MsgBox "異常終了しました" & vbCrLf
                End If

  'オブジェクトのクローズ
  cn.close
        
  'オブジェクトの開放
  Set cmd = Nothing
  Set cn = Nothing
  
Exit Sub

    
ERR_HANDLER:
  'エラーメッセージ
  MsgBox Err.Number & ")" & Err.Description
  'オブジェクトの開放
  Set cmd = Nothing
  Set cn = Nothing

End Sub

 

PC設定のDSNを使わずに、サーバーに接続しているので、コメントアウトしてあります。

 

このコードにて実験をしてストアドがちゃんと動いてコメントを返してくれることを確認できました。