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を使わずに、サーバーに接続しているので、コメントアウトしてあります。
このコードにて実験をしてストアドがちゃんと動いてコメントを返してくれることを確認できました。