データベース Server

【SqlServer】AccessVBAからストアド実行させてもデータ変わらない!?ODBC更新失敗

AccessからSqlServerExpressにリンクテーブルをした仕組みを構築しましたが、データが1万件を超えるようになってから、

ココがダメ

修正するだけでもエラーがでる!?

ようになってしまいました。

 

ODBC・・リンクテーブルでの更新に失敗

ある日突然、ODBCのエラーが出るようになりました。

ODBC 更新エラー

なぜか?32ビットAccessでは、エラーが出ない事が多かったりしましたが、64ビットAccessでは100%エラーが出現しました。

32ビットAccessで作成したので、Microsoft365(64bit)にしたことで何かVBAコードを書き替えないといけないのかと調べまくりましたが、365Accessで開けて今回のエラー以外は問題なさそうなので原因は違うようでした。

※ネットで調べると64ビット版にすることでVBAコードの修正が発生するような記事がありましたが自分の場合は関係なかったようです。

※VBAの参照設定なども確認しましたが自分の場合は関係ありませんでした。

 

更新VBAの見直し

今回の更新VBAが下記のようになります。

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
    
            On Error GoTo ErrRtn
            
                       'アクションクエリ非表示設定
                        DoCmd.SetWarnings False

                Set cn = CurrentProject.Connection
                rs.CursorLocation = adUseClient
                rs.Open "orderdata", cn, adOpenKeyset, adLockOptimistic
                rs.Filter = "NO = " & edit_value
                   

                    ' トランザクションの開始
                    cn.BeginTrans
                    
                    ' 更新するフィールド
                    rs!shipper = Me!shipper_input

                    rs.Update

                    ' トランザクションの保存
                    cn.CommitTrans

                rs.Close: Set rs = Nothing
                cn.Close: Set cn = Nothing

つまり、NOにて、フィルターをかけて対象レコードを絞っています。

そして、下記の様なサイトを見つけました。

 

FindとFilterとはちょっと違うのかもしれませんが、Accessにとっては負荷のある関数なのかと考えてループで検索する事にしました。

今回の自分のケースでは、VBAコードをループにして、更新かけても問題解決にはなりませんでした。

 

ODBCの問題ではないようだ!

VBAコードに何か原因あるのだろうと推測をして、処理する部分をコメントアウトするとODBC更新エラーは無くなります。

こうなると、確実に、VBAに問題があるという回答になって行くかと思いますが、

ココに注意

Accessの限界じゃないかという推測もできます!

レコードが1万件を超えるころから処理に遅延が出始め、エラーも出始めました。

VBAの更新コードを、SQLに変更してみたり、ダメだとわかってもクエリーを試してみたりしました。

Accessというソフトは、リンクテーブルのデータを全部持ってきてから処理をするということを聞きましたが、それが本当ならばレコードが増えれば増えるほど使い物にならないって事になりますね。

 

SqlServer側で処理させることに変更

Accessに頼れないとすれば、SqlServerの方で処理させて、Accessで見る為のテーブルは、パススルークエリにするって事が必要かと思われます。

ココに注意

SqlServerの方で処理・・・ストアドプロシージャの設定が問題

 

何でもAccess側で設定ができるのがAccessの便利な所なのですが、限界じゃSqlServerのストアドプロシージャ(以下ストアド)を使うしか方法がありません。

でも、ストアドに慣れてない自分にとっては、ここからが無限地獄への入り口になりました。

 

エラーも出ないしデバッグ方法も不明

SqlServerのストアドの作成は、MSSMSにて行いましたが、

最新のMSSMSでは、デバッガーが無くなっているとか!?

じゃ何でデバッグすればいいんだよ!?VisualStudioだっせさ!

既に自分のPCにはインストールしてあったので、VisualStudioでデバックをやってみました。

ココがダメ

エラーも出ないしデバッグの方法も分からない(泣)

 

もうデバッグによる文法違反は諦めて、データだけを見る事にしました。

つまり、一つのテーブル内容から、別テーブルの内容を更新するストアドを実行させて更新されればOKという判断です。

ココがポイント

データ更新されないか、フリーズするという事でエラーと判断

しかし、本当にAccessからのストアドの呼び出し時にフリーズしたり、SqlServerの方でもクエリを動かしてフリースしたりと無限地獄です。

AccessでのVBAからストアドを実行するコードや、ストアドのコードを何度も見直しましたが、本当にエラーがでないでフリーズするので困りました。

 

2日かけ原因究明

とにかく、エラーが出ないのでどこが悪いのかが分かりません。

そして、2日目になってAccessのクエリを見ていたところ、一ヶ所だけおかしな記述が分かりました。

クエリ

なんで、AccessのクエリとSqlServerのストアドとは関係ないだろ?って思われるかもしれませんが、自分の場合は、クエリでもテーブルの更新を実験して、更新する項目は、このクエリをSQLにして、ストアドに利用していたりしているのでストアドとも関係があるのです。

つまり、このフィールドの設定は、ストアドでも間違っている可能性があるのです。

このクエリもフリーズせずに動く(かなり遅い)ようになりました。

ストアドの方も、動くようになりました。

USE [SGDB]
GO
/****** Object:  StoredProcedure [dbo].[order_edit]    Script Date: 2022/09/19 18:20:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[order_edit]
@eno VARCHAR(10)
AS
BEGIN TRY

    BEGIN TRANSACTION

--ここから処理コード

UPDATE                    [order]
SET                             
    flag = order_job.flag,
    reception_date = order_job.reception_date,
    reception_time = order_job.reception_time,
        省略
    deli_phone = order_job.deli_phone,
    deli_notes = order_job.deli_notes
FROM                         [order] INNER JOIN
                                      order_job ON [order].NO = order_job.NO
WHERE                       ([order].NO = @eno)

UPDATE                    [trader_ag]
SET                             
           trader1 = order_job.trader1,
           vehicle1 = order_job.vehicle1,
           total1 = order_job.total1,
           assistant1 = order_job.assistant1,
               省略
           deli_notes4 = order_job.deli_notes4,
           notes5 = order_job.notes5,
           deli_notes5 = order_job.deli_notes5

FROM                         [trader_ag] INNER JOIN
                                      order_job ON [trader_ag].od_no = order_job.NO
WHERE                       ([trader_ag].od_no = @eno)


--ここまで処理コード
 
    COMMIT TRANSACTION
 
END TRY
 
BEGIN CATCH
    ROLLBACK TRANSACTION
 
    RETURN ERROR_NUMBER()
 
END CATCH
 
RETURN 0;

 

MSSMSでのTips

パラメータ付ストアドのテスト方法

AccessVBAからパラメータ付で実行する仕組みですが、テストするのにAccessで行うのは大変です。

そこで、MSSMSからテストする方法がわかったので備忘録として書いておきたいと思います。

 

MSSMS パラメータ付ストアド実行

対象データベース>>プログラミング>>ストアドプロシージャ>>実行するプロシージャ」を右クリックすると「プロシージャの実行」というのをクリックします。

値という場所に、パラメータ値を入れて実行すると、Accessから実行しなくてもSqlServerのストアドが実行されます。

自分の場合は、そのパラメータ値によって、1つのテーブルから、別テーブルのレコード値を更新するようにしましたので、結局、ちゃんと動いたかを判断するのは、テーブルの値をみることにはなりました。

 

他のデータベースにテーブルやストアドを移行

MSSMSに慣れてないので、ストアドのテストしてちゃんと動くことが確認できたら、本稼働しているデータベースに復元してから、テストした内容の設定をしなければなりません。

今回の場合は、テーブル1個とストアド2個を増設することで目的は果たせたのですが、本稼働しているデータベースに移行するのは大変です。

ココがポイント

ストアドプロシージャをスクリプト化

ストアドプロシージャをファイルにして保存できることがわかりました。

ストアドプロシージャのスクリプト化

 

ファイルにしたスクリプトは、移行先のデータベースでファイルを読み込んでから、「クエリ実行」するだけでストアドに移行できました。

同じ様に、テーブルもスクリプト化できることがわかり同じようにして別のデータベースに移行することができました。

 

大きなデータベースの更新はストアドか!?

大きなと言っても1万レコードのデータベースですが、それでもAccessの場合は使い物にならないので、SqlServer側で処理するしかないようです。

今回の処理内容は、編集画面を出して編集するという普通に必要な処理ですが、大きなデータベースの場合は、下記の様なフローが必要でした。

(1)メインテーブルAから対象レコードを選択(編集ボタンをクリック)
(2)編集ボタンをクリック時点で、対象レコードのNOをストアドにパラメータ付で実行。
(3)NOより別テーブルBに対象レコードをコピー※実行前にレコード削除
(4)編集画面起動(テーブルBをソースとして現在のデータが表示)
(5)編集後編集ボタンをクリック(パラメータ付でストアド実行)

この流れで、データの更新をすることができました。

そして、圧倒的に更新スピードが改善されて、ODBC更新エラーになる事も無くなりました。

 

SqlServerとAccessの場合、本当にデバッグで苦労しますが、今回の経験から、1万件を超える様なレコードの場合での更新処理はストアドを使った方が良いという判断になるかと思います。

ただ、ストアドでのデバッグは、エラーが出ないのでテーブル値をみて判断するしかないというのが曲者です。

なんだか、最近、FileMakerServerも使う機会があるのですが、こんな苦労をするならFileMakerが良いような気にもなっています。

 

-データベース, Server
-, , , , , , , ,

Translate »

© 2022 PCTips