前回は、AccessのVBAからSqlServerのストアドプロシージャを実行するというのをやりましたが、
【SqlServer】ストアドプロシージャを使って初めての処理
今回は、
ココがポイント
Accessの方でパラメーターを付けたVBAでストアドプロシージャを実行する
ということをやってみました。
簡単だと思っていましたが結局１日手こずりしました。
SSMSでSQLコードを作成
だんだんと、SQLにも慣れてきました・・・というより、SSMS内で「新しいクエリ」からの、「エディターでクエリをデザイン」モードを使えば簡単にSQLが作成できることがわかりました。
今回は、Accessの方からパラメーター付きでSqlServerに実行させたいので、ストアドプロシージャの中にもパラメーターを埋め込みます。
ココがポイント
@arrange_idというパラメーターでデータを絞る
ような事をさせてみます。
パラメーターを設定したVBA作成
Sub shipping_schedule_calc()
Dim Cnn As New ADODB.Connection
Dim cmd As New ADODB.Command
' Dim param As ADODB.Parameter
' Dim strMsg As String
On Error GoTo Err_shipping_schedule_calc
' Debug.Print Forms![stock_inquiry]![arrange_id]
'SQLServer接続設定＆接続
With Cnn
.Provider = "SQLOLEDB"
.ConnectionString = "Data Source=サーバー名;" & _
"Initial Catalog=データベース名;" & _
"User ID=lユーザー名;" & _
"Password=パスワード"
.Open
End With
'ストアドプロシージャ呼び出し設定＆呼び出し
With cmd
.CommandTimeout = 0 'タイムアウト設定を無制限に
.ActiveConnection = Cnn 'サーバー接続設定
.CommandText = "shipping_schedule_calc_add" 'ストアド名セット
.CommandType = adCmdStoredProc 'ストアドなのでadCmdStoredProc
.Parameters("@arrange_id").Value = Forms![stock_inquiry]![arrange_id]
'戻り値設定
' .Parameters.Append .CreateParameter("@rtn", adInteger, adParamReturnValue)
'実行※レコードは返ってこないのでadExecuteNoRecords
.Execute , , adExecuteNoRecords
'戻り値の判断＆表示用メッセージ生成
' If .Parameters.Item("@rtn").Value = 0 Then
' strMsg = "出荷予定数の計算は正常終了しました。"
' Else
' strMsg = "異常終了しました" & vbCrLf & _
' "ErrNO=" & .Parameters.Item("@rtn").Value
' End If
End With
Exit_shipping_schedule_calc:
'接続解除
Cnn.Close: Set Cnn = Nothing
'メッセージ表示
' MsgBox strMsg
Exit Sub
Err_shipping_schedule_calc:
' DoCmd.Close acForm, "jobwait", acSaveNo
'ストアド以外の場所でのエラーなので、VBAのErrの情報セット
' strMsg = "異常終了しました" & vbCrLf & _
' "ErrNO=" & Err.number & " ErrMsg=" & Err.description
' Resume Exit_shipping_schedule_calc
MsgBox "エラー： " & Err.description
End Sub
ちょっと上のコードは、コメントアウトが多すぎるので見ずらいとは思いますが、ポイントは、
.Parameters("@arrange_id").Value = Forms![stock_inquiry]![arrange_id]
の部分で、
フォームの値をパラメーターとして設定している
という事です。このようにして、フォームの値をストアドプロシージャに渡すことができれば、本当に便利ですよね。
VBAのエラー戻り値があるとエラー
'戻り値設定 ' .Parameters.Append .CreateParameter("@rtn", adInteger, adParamReturnValue)
コメントアウトしてある、ストアドプロシージャでエラーがあった時の戻り値の処理部分ですが、このコードを入れるとエラーになります。
私の現時点でのスキルでは何でエラーになるのかが理解できないので、コメントアウトして消す事にしました。
ストアド内でエラーが起きたら、ロールバックさせるかと思いますが、エラーがあったという事は通知されないのかもしれません。
時間があるときにもっと追及してみたいと思います。
ストアドプロシージャのコード
USE [Logistics]
GO
/****** Object: StoredProcedure [dbo].[shipping_schedule_calc_add] Script Date: 2020/03/14 14:05:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[shipping_schedule_calc_add]
@arrange_id VARCHAR(50)
AS
BEGIN TRY
BEGIN TRANSACTION
UPDATE stock
SET waiting_shipping = waiting_shipping + View_shipping_schedule_calc.出荷予定数
FROM View_shipping_schedule_calc INNER JOIN stock
ON View_shipping_schedule_calc.product_sku = stock.jan_sku_code
WHERE View_shipping_schedule_calc.arrange_date = @arrange_id
UPDATE dbo.[order]
SET dbo.[order].progress = 2
WHERE dbo.[order].arrange_date = @arrange_id
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
RETURN ERROR_NUMBER()
END CATCH
RETURN 0
上のストアドプロシージャでの注意ポイントとしては、
@arrange_id VARCHAR(50)
といいうように、ちゃんとパラメーターの宣言をしてあげないとエラーがでます。
そして、SSMSで作成したSQLを組み込みますが、
WHERE View_shipping_schedule_calc.arrange_date = @arrange_id
の様に、「@arrange_id」のようにダイレクトに入れればいいらしく、とくにカンマやダブルコーテーションなども必要ないようです。
今まではストアドプロシージャを敬遠していましたが、ちょっと覚えればサーバー側で処理してくれるので端末側のAccessの負担が減って処理が楽になります。