このブログ記事は、SqlServerにてデータベースをODBC接続してAccessを端末として利用している環境で、
注意ポイント
データベースをコピー（複製）した時に、Accessからストアドを実行しても動かないんじゃないか！？
という疑問から始まった実験です。
今回は、その実験の最初の段階である実験データベースを準備する時に、ストアドからの戻り値をどうやってAccess側で受け取るのか？を備忘録にしてみました。
実験用データベースの準備
SqlServerの方に実験用データベース「DB1」というのを作成して、テーブル「dbo.data」というのを作成しました。
テーブル内は、適当なサンプルデータを入力しておきました。
これから何の実験をするのか？は下記の項目で説明します。
ODBC接続設定の準備
ODBCの接続設定は、MSSMSから新規ログインユーザーを追加しました。
データベースを指定するために、ユーザーマッピングにて、DB1を指定しました。
次にAccessを使うパソコン（Windows10）にて、ODBCの設定をしました。
ODBCにより、データベースを選択して切り分けができるんじゃないかと考えています。
データベースによって接続できるユーザーを分けたいので今回もDB1には、ODBC1での接続、DB2には、ODBC2での接続を準備して実験を進めていく予定です。
ストアドの準備
この記事を書く前に、散々実験をして悩んだ末のストアドコードになります。
本当にこのコードでいいのか？は全く自信がありませんが、動いたので良しとしています。
ストアド名は、「test_calc」にしました。
USE [DB1]
GO
/****** Object: StoredProcedure [dbo].[test_calc] Script Date: 2020/08/14 11:13:39 ******/
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
--DECLARE @ans VARCHAR(50)
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
--RETURN @ans
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
RETURN ERROR_NUMBER()
END CATCH
RETURN 0
--RETURN @ans;上記のコードでは、正常終了したら、０が戻り値になるのと、@ansという変数が返されるようにしました。
RETURN 0,@ans;
とかにするとエラーになるので、他に書き方があるのかは知りません。
（追記）RETURN @ans;は無くてもAccess端末で戻り値として受け取っていることがわかりましたので、コメントアウトしました。
Accessでの端末側の準備
SqlServerのdbo.dataを、Accessのリンクテーブルにして、帳票フォームにて作成しました。
やりたい事メモ
「処理」ボタンをクリックすることで、「みかん、パイナップル、リンゴ」の数が１加算されるような仕組みです。
もちろん、AccessのVBAの方では、ストアドを実行させることを目的として、Accessの方でストアドが動いたのかを確認するための物です。
下記にAccessのVBAコードを記載しておきます。
Private Sub job_btn_Click()
On Error GoTo ERR_HANDLER
Dim strSQL As String
Dim i As Long
'オブジェクトの作成
Dim cn As New ADODB.Connection
'接続文字列の設定
cn.ConnectionString = "DSN=ODBC1;Uid=user1;Pwd=japan2020$$;Database=DB1"
'接続
cn.Open
'ストアドプロシージャの実行情報を設定
Dim cmd As New ADODB.Command
cmd.ActiveConnection = cn
cmd.CommandType = adCmdStoredProc 'コマンドの種類
cmd.CommandText = "test_calc" 'ストアドプロシージャ名
'ストアドプロシージャへの引数を設定
'入力引数
cmd.Parameters("@input_no").Value = Forms![dbo_data]![input_no]
'ストアドプロシージャの実行
cmd.Execute
'実行結果の表示
Dim anser As String
anser = cmd.Parameters("@ans").Value
Debug.Print anser
Debug.Print cmd.Parameters(0).Value
'
' 戻り値の判断& 表示用メッセージ生成
If cmd.Parameters(0).Value = 0 Then
MsgBox "出荷予定数の計算は正常終了しました。"
Else
MsgBox "異常終了しました" & vbCrLf
End If
'オブジェクトのクローズ
cn.Close
'オブジェクトの開放
Set prm = Nothing
Set cmd = Nothing
Set cn = Nothing
Exit Sub
ERR_HANDLER:
'エラーメッセージ
MsgBox Err.Number & ")" & Err.Description
'オブジェクトの開放
Set prm = Nothing
Set cmd = Nothing
Set cn = Nothing
End Sub
実験用なので、ODBC接続設定もそのままを載せてあります。
ODBC用情報としては、
- ユーザー名 user1
- パスワード japan2020$$
- ODBC接続名 ODBC1
- データベース名 DB1
以前のストアド実行時には、サーバー名も必要でしたが、Access端末のパソコン（Windows10）のODBC接続設定にサーバー名が入っているので、必要ありませんでした。
まだSqlServerにおいては初心者なので、理解できませんが、もしかしたらVBAにて、サーバー名を指定してストアドを動かす時にはODBCの設定っていらないのかな？その辺はやってみないとわかりません。
実験した事とポイント
この回では、AccessのVBAからストアドを実行して、Access側に戻り値を表示させるというのが目的でした。
参考
Accessの方で、処理ボタンをクリックすることで、（「みかん、パイナップル、リンゴ」のレコードから選択する）ストアドが実行され、そのレコードのkazuというフィールド値に、１が加算させれて、問題なければ、０と@ansという２つの内容が戻り値になるという流れです。
@ansの変数に関しては、宣言時に、出力なので、OUTPUTを付けてみました。
へプル等を見ていたら、OUTPUTを付けると、プロシージャの終了時にその値が呼び出し元のプログラムに返されるらしいが、使い方があっているのか？どうかは不明。結果良しとしている。
（追記）OUTPUTが無いとAccess側に戻り値として帰ってこない事がわかりました。
結果がストアドから戻り問題なければ、VBAの方で、メッセージ「"出荷予定数の計算は正常終了しました。"」が出るということで、ちゃんとストアドが動いていることがAccess上でも確認することができました。
@ansの内容に関しては、debug.printにてイミディエイトウインドウで確認しました。メッセージと一緒に表示させてもいいですね。
MsgBox anser & "出荷予定数の計算は正常終了しました。"
また、フォームを更新すれば、数が１加算されているかどうかでストアドの計算もされているかどうかも確認できました。
それから、
Debug.Print cmd.Parameters(0).Value
のパラメーターを呼び出すコードですが、現状では、どのレコードを選択しても、０が表示されます。
(1)にすると、レコードのno部分、(2)にすると、anser部分（みかん、パイナップル、リンゴ）(3)にするとエラーになります。
今回は、RETURN @ansで戻していますが、パラメーターで戻す方法もありそうです。
この辺の解説本も見つからないし、ネットを探し回っても理解できる内容がありませんでした。
自分が時間をかけて実験した内容なので記述的には正解なのか？どうかもわかりません。
この実験は、端末でAccessなどを使うような場合ですが、WEBで作業する段階になると必要ないことかもしれませんね。
【Sqlserver】データベースを複製してAccessで動くようにODBC設定VBAの実験準備（データベース複製実験２）