8 複数ユーザー利用を考慮したシステム変更

本題材のデータベースには、「人材検索」という機能があります。これは、画面上でいくつかの条件を指定して処理を実行すると、それに該当する人材データを一覧表示するというものです。

その処理においては、まず人材データに関するテーブルから指定された条件（プログラム上はSQLのWhere句）に一致するデータの「人材ID」フィールド値だけをピックアップし、それを検索結果用の作業テーブルに書き出します。

通常ですと、人材のメインテーブルに対してWhere句を指定してSELECTすればよいのですが、多少複雑な処理を行う都合上、いったん作業テーブルに書き出し、それを加工したあと、最終的にはそのテーブルと人材データのメインテーブルを結合したクエリのレコードを結果一覧として表示するような仕組みを取っています。

その際、問題となるのが、ネットワーク上の複数ユーザーが同時にこの処理を行った場合、どのデータがどのパソコンから実行された処理結果か分からなくなってしまうことです。作業テーブルはひとつしかないので、複数パソコンの実行結果が入り混じってしまったり、あとから実行した方のデータだけが生きてしまったりということが考えられます。本題材のデータベースは元々“スタンドアロン”での実行を前提としているため問題なかったのですが、アップサイジングする場合はおそらくほとんどは複数ユーザーでの共有となるはずですので、そのような状況も配慮して変更を加える必要があります（もちろんSQL Serverを使わずAccessだけでネットワーク共有する場合もこのような配慮は必要です）。


「作業テーブル」といった場合、Accessでは「処理途中の中間的なデータを一時的に保存するテーブル」という認識になりますが、SQL Serverの場合には、それと同様の考え方もできますが、『その都度テーブル自体を作成し、かつ処理完了後に明示的にテーブルを削除するかSQL Serverとの接続解除によって自動的に削除される「テンポラリテーブル」』というものがあります。テーブルを生成する際、それを意味する意図的なテーブル名を付けることによって、同名のテンポラリテーブルを複数ユーザーが同時に作ったとしても、内部的にはユーザーごとの別のテーブルとして扱えるというものです。

パフォーマンスを考えると、ストアドプロシージャにおいてテンポラリテーブルを作ってすべての処理をサーバー側で行うようにした方がよいのですが、今回はクライアント側のVBAで長い処理をしているのと、あとでフォームと連結してその内容を表示することを考え、最適ではありませんがもっともシンプルな方法を使ってその対応をしてみます。


それには次のような手順でデータベースをカスタマイズします。

この方法の考え方としては、作業テーブルにユーザー（あるいはコンピュータ）を認識するためのフィールドを追加し、作業テーブルにデータを書き込むときはその処理を実行しているユーザー情報も各レコードに付加します。そしてそのデータを読み込むときには、そのユーザー情報をWhere条件とすることで、自分が処理したレコードだけを取り出します。それによってひとつの同じテーブルであっても、競合することなく複数のユーザーが利用できるようになります。なお、ユーザー情報については、処理を実行しているパソコンから「ネットワークユーザー名」や「コンピュータ名」を取得する方法や、アプリケーションの起動時にログイン画面を表示させ、事前にユーザー名を取得しておくといった方法が考えられますが、ここでは「コンピュータ名」を使う方法でやってみます。

  1. まず、Windows APIを利用して「コンピュータ名」を取得するプロシージャを標準モジュール等に用意します。
    それには次のようなコードをモジュール上に作成します。「Declare」の部分がWindows APIの宣言で、「GetPCName」がコンピュータ名を返すFunctionプロシージャです。

    Option Compare Database
    Option Explicit

    Private Declare Function GetComputerName Lib "kernel32" Alias _
    "GetComputerNameA" (ByVal lpBuffer As String, nSize As Long) As Long


    Public Function GetPCName() As String

      Dim strCmptrNameBuff As String * 21

      GetComputerName strCmptrNameBuff, Len(strCmptrNameBuff)
      GetPCName = Left$(strCmptrNameBuff, InStr(strCmptrNameBuff, vbNullChar) - 1)

    End Function


    記述したら、イミディエイトウィンドウで「?GetPCName()」といった命令を実行し、そのコンピュータ名が取得されることを確認します。
    なお、コンピュータ名とは、[スタート]-[コンピュータ]の右クリックから[プロパティ]を選択したときに表示される画面に表示されているものです。

  2. 次に、今回の作業テーブルである「tbl検索結果」にvarchar型の「ユーザー名」というフィールドを追加します。


  3. 次に、この人材検索機能で利用するビューやストアドプロシージャを、すべて「ユーザー名」フィールドを加味したものに変更します。Where条件なしでレコードをSELECTするストアドプロシージャであれば、ユーザー名をパラメータとして追加し、かつそれをWhere条件として追加します。ビューであれば、ユーザー名をパラメータとするストアドプロシージャに作り変えます。

    【変更例】 ※あくまでも今回の場合のもので、一般的なルールを示したものではありません

    • いわゆるAccessの“選択クエリ”であるビューについて、必要に応じて「ユーザー名」フィールドを出力対象に追加します。




    • 全件を処理するストアドプロシージャに、「ユーザー名」をパラメータとして追加し、さらにそれをWhere句に追加します。・・・・ここではパラメータ名を「@UserName」としました




    • これまで全件を処理していたビューについては、パラメータを追加するためにストアドプロシージャに作り変えます。
      既存のビューのSQL文をコピーして、新規作成したストアドプロシージャに貼り付けた上で、「ユーザー名」をWhere句のパラメータとして追加します。





    • 上記題材では、Accessでよく言われるところの「クエリのクエリ」を使っています。クエリを作り、さらにそれを他のクエリのソースとする方法です。しかしSQL Serverのビューやストアドプロシージャはより高度な書き方ができるので、本来はひとつのビューなりストアドプロシージャにまとめるべきかもしれません。

  4. 次に、VBAのプログラムからビューやストアドプロシージャを呼び出している部分がありますので、上記変更に合わせて、その書き換えを行います。ビューからストアドプロシージャに変更になったものはコーディングを変え、パラメータなしからありに変わったストアドプロシージャはそのパラメータ指定のコードを追加します。
    変更前 変更後
    ユーザー名を格納する変数を宣言し、APIを使ってコンピュータ名を取得してここに格納、以降の処理ではこの変数を使うようにします。
    Dim strUserName As String
    strUserName = GetPCName()
    cnn.Execute "DELETE FROM tbl検索結果" cnn.Execute "DELETE FROM tbl検索結果 WHERE ユーザー名='" & strUserName & "'"
    cnn.Execute "INSERT INTO tbl検索結果 (人材ID) " & _
     "SELECT 人材ID FROM qsel人材検索 " & _
     "WHERE " & strWhere    		 cnn.Execute "INSERT INTO tbl検索結果 (人材ID, ユーザー名) " & _
     "SELECT 人材ID, '" & strUserName & "' FROM qsel人材検索 " & _
     "WHERE " & strWhere
    rstout.Open "tbl検索結果", cnn,〜〜〜〜 rstout.Open "SELECT * FROM tbl検索結果 WHERE ユーザー名='" & strUserName & "'", cnn, 〜〜〜〜
    With cmd
      Set .ActiveConnection = cnn
      .CommandType = adCmdStoredProc
      .CommandText = "qdel検索非該当者"
      .Execute
    End With

    		 With cmd
      Set .ActiveConnection = cnn
      .CommandType = adCmdStoredProc
      .CommandText = "qdel検索非該当者"
      .Parameters.Refresh
      .Parameters("@UserName") = strUserName
      .Execute
    End With
    intResult = DCount("人材ID", "qsel検索結果")

    		 With cmd
      Set .ActiveConnection = cnn
      .CommandType = adCmdStoredProc
      .CommandText = "ufn検索結果件数"
      .Parameters.Refresh
      .Parameters("@UserName") = strUserName
      .Execute
      intResult = .Parameters("@RETURN_VALUE")
    End With

    なお、最後の項目では、次のようなストアドファンクション（「テキストスカラ関数」や「スカラ値関数」とも言います）を新規に作成し、それを呼び出すように変更しています。

    ALTER FUNCTION dbo.ufn検索結果件数
    (
      @UserName varchar(25)
    )
    RETURNS int
    AS
    BEGIN
      DECLARE @RecCnt int

      SELECT @RecCnt = Count(*)
      FROM tbl人材
        RIGHT OUTER JOIN qsel検索結果固有
        ON tbl人材.人材ID = qsel検索結果固有.人材ID
      WHERE qsel検索結果固有.ユーザー名 = @UserName

      RETURN @RecCnt

    END


  5. 最後に、処理結果を表示するフォームの変更を行います。

    このフォーム「fdlg検索結果_sub」は単純なビューをレコードソースにしていましたが、今回それがパラメータ付きのストアドプロシージャに変更になったため、フォームを開くと次のようなパラメータ値入力を促すダイアログが表示されてしまいます。


    そこで、そのフォームのデザインビューを開き、プロパティシートにおいて「入力パラメータ」プロパティの設定を行います。ここでは次のような値を入力します。

    @UserName varchar(25)=GetPCName()



それでは最後に実行確認をしてみます。完全に瞬間的にも同時とはいきませんが、２台のパソコンで異なる検索条件で「人材検索」の処理を実行してみます。

手順 パソコンA パソコンB
１ 検索条件を指定します
 パソコンAとは異なる条件で検索条件を指定します
２ ヒットした件数が表示されます
 パソコンAとは異なるヒット件数が表示されます
３ 検索されたデータが一覧表示されます
 パソコンAとは異なる結果が表示されます

２台のパソコンで処理を実行したあとの「tbl検索結果」テーブルは次のような内容になっています。「ユーザー名」を見ることで、２種類のパソコンから処理が実行されていることが分かります




◆◆◆ 最後に ◆◆◆

ここまでざっとアップサイジングの実例を紹介してきました。しかしより現実的なアップサイジングの作業では、もっとさまざまなバリエーションがあります。またSQL Server側の仕組みやプログラミングの知識が必要になりますし、ADOの知識も必要になります。それら個々にまた専門分野ともいえる部分もありますので、ここではすべては網羅できません。また、たとえばパフォーマンスの面からは、ビューやストアドプロシージャの新規生成やVBAによってクライアント側で処理している内容のストアドプロシージャへの移行、複数レコードの読み込みの制限など、改善の余地は多々あると思いますが、今回の題材のデータベースについてはそこまでは行っていません。また動作的にもすべて問題なく仕上がったというところまで手を付けていません。今回は「アップサイジングの中の触りだけ」ということでご認識ください。
| Index | Prev | Next |

 

Copyright © T'sWare All rights reserved