【SqlServer】Accessからストアド実行して計算させるのに苦労した話

クエリーが糞すぎてストアドで計算

SqlServerにデータベースを置いて、Accessを端末として使用していますが、Accessからクエリーで計算させると、長時間必要な場合と、フリーズする場合とあり、どっちも糞の様な時が発生します。

Accessのクエリーで簡単に計算できるのはいいのですが、これでは使い物になりません。

そうなると仕方なく時間をかけてSqlServerの方でストアドプロシージャ（以下ストアドに略）で計算させるような方法に変更しますが、デバッグも大変だし、時間が必要な事になります。

今回もやりたい事がなかなかできずに苦労したので備忘録にしておくことにしました。

別の説明をすると、dbo.mst_wc（HNO、加工費、加工日数）というのが複数レコードあり、HNOをキーとして、HNOをグループ化して、加工費と加工日数の合計を計算させたものを、dbo.mst_parts（単価、納期予定日数）として代入するということをさせたいのです。

ストアドが実行されない！？

SqlServerから、ストアドを右クリックして、ストアドプロシージャの実行とすれば、直接ストアドを実行して結果をテーブルから確認することができます。

直接実行して動作確認してから、Accessから実行させてもストアドが実行されません。

【SqlServer】パラメーター付きVBAでストアドプロシージャを実行

散々いろんなサイトを探し回って、結局下記のコードにしたら動きました。

Function mst_parts_calc()
 Dim cnn As New ADODB.Connection
    Dim cmd As New ADODB.Command

On Error GoTo Err_parts_calc
    
    Set cmd = New ADODB.Command

    'SQLServer接続設定＆接続
            With cnn
                .Provider = "SQLOLEDB"
                .ConnectionString = "Data Source=サーバー名;" & _
                    "Initial Catalog=データベース名;" & _
                    "User ID=ユーザー名;" & _
                    "Password=パスワード"
                .Open
            End With


    'ストアドプロシージャ呼び出し設定＆呼び出し
            With cmd
                .CommandTimeout = 0             'タイムアウト設定を無制限に
                .ActiveConnection = cnn         'サーバー接続設定
                .CommandText = "wc_parts_calc"      'ストアド名セット
                .CommandType = adCmdStoredProc  'ストアドなのでadCmdStoredProc
                .Parameters.Refresh
                .Parameters("@hno").Value = Forms![mst_wc]![HNO]

                '実行※レコードは返ってこないのでadExecuteNoRecords
                .Execute , , adExecuteNoRecords

            End With

Exit_parts_calc:
        cnn.Close: Set cnn = Nothing
        Exit Function
Err_parts_calc:
        cnn.Close: Set cnn = Nothing
        MsgBox "エラー： " & Err.Description
End Function

Functionにして、他のVBAでも使いまわしできるようにしました。

以前のVBAでもちゃんと動作していたので何が違うのかよく見ていませんが、値の宣言だったり、Setだったりと細かい相違が関係しているのかもしれません。

ストアド（削除、追加、更新）

USE [データベース名]
GO
/****** Object:  StoredProcedure [dbo].[wc_parts_calc]    Script Date: 2022/03/25 11:11:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[wc_parts_calc] 
@hno int
AS
BEGIN TRY

    BEGIN TRANSACTION

--作業テーブルデータ削除
TRUNCATE TABLE [dbo].[job_wc_calc]

--作業テーブルにデータ抽出追加 
INSERT INTO [dbo].[job_wc_calc]
SELECT HNO, SUM(加工費) AS 加工費合計, SUM(加工日数) AS 加工日数合計
FROM dbo.mst_wc
GROUP BY HNO
HAVING HNO = @hno

--作業テーブル値でmst_partsの単価、納期予定日数を更新
UPDATE [dbo].[mst_parts] 
SET 単価 = 加工費, 納期予定日数 = 加工日数
FROM mst_parts
INNER JOIN job_wc_calc ON mst_parts.HNO = job_wc_calc.HNO 
WHERE mst_parts.HNO = @hno

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

ストアドのSQLは、AccessのSQL表示からコピーして参考にすることが多いです。しかし、作業テーブルのデータを削除するところから躓きました。

なんで！？

こんな簡単なSQLでさえ思ったように動作しないのは本当に困ります。結局

で消すことができました。理由はわかりませんが、こちらのコマンドの方が早いし良いようです。

Accessのボタンが動作しない謎

今回の記事とは関係ないかもしれませんが、突然Accessのフォームボタンの一つが動作しなくなりました。

まったく謎です！？

ボタンのコードを調べてみると、新規のプロシージャになっていました。そして、元々のプロシージャは残ってはいますが、赤文字になっています。まったく謎です。

本当に原因不明ですが、

を見つけました。

自分でも何で全角にしてしまったのか覚えていませんが、日本語の全角が原因でおかしな現象が出ることはあるかもしれませんね。

再表示されなくなった謎

値をVBAもしくは、ストアドで更新した後の、フォームの表示は、再表示させない限り最新の表示になりません。

そこで、

などを使って対処していますが、DoCmd.RunCommand acCmdRefreshで更新できていたのに、ストアドに変更したら更新されなくなってしまいました。

そこで、直接フォーム名を指定して再表示させることで回避することができました。