エクセルとＭｙＳＱＬを使った伝票入力プログラム作成

質問

1980年代に、当時のオフィスコンピュータのディーラでプログラマーとして働いていて、ＣＯＢＯＬで売上システムなどを開発していました。

コンピュータのＯＳは、マルチジョブ／マルチユーザでしたので、何台もターミナルを接続できました。ジョブコンも完備してましたので、一台のターミナルでバッチ処理を流しながら、オンラインの伝票入力なども同時にできました。

その後、一般企業へ転職しましたので、仕事でエクセルのＶＢＡも多少記述しました。退職後ボランティアで地域活動のお手伝いをしていますが、マルチユーザの売上伝票入力のような作業の必要性が発生したので、プログラムを作りたいと思います。

利用できるのは、エクセルとＭｙＳＱＬだけで、メルコのテラステーションのような、ＲＤＢ付きのファイルサーバは使えて、とりあえず同一ＬＡＮ内の複数ユーザです。

当時のＣＯＢＯＬでは、次のようにファイルを扱いプログラムを作っていましたが、現在のエクセルとＭｙＳＱＬでは、どのようにＲＤＢを扱い、プログラムを作るのでしょうか？ アドバイスや、参考情報(ＷＥＢサイト・文献）紹介などお願いします。

＜当時のファイルの扱い方＞
　１．参照用の得意先／商品／単価マスタ・ファイルを「索引ファイル」で用意する。
　２．伝票入力したデータを記憶するジャーナル・ファイルを「相対ファイル」で用意する。
　３．制御用のコントロール・ファイルを「相対ファイル」で用意する。
　４．ターミナル画面上で、一枚の伝票が完了しオペレータが[ＯＫ]ボタンを押すと、コントロール・ファイルの予め決められたレコードを、排他ロックして読み込み、レコード内の最終伝票番号とジャーナルのＥＯＤ(ポインタ)を参照して、一意的伝票番号とジャーナル位置を得て、明細データのジャーナルを書き込み、伝票番号とＥＯＤをそれぞれインクルメントして制御レコードを上書きする。（マルチユーザに対応するため）

＜特にアドバイスいただきたい点＞
１．当時の「索引ファイル」は、順読み込み・乱読み込み・キー設定後順読み込みなど色々手法がありましたが、ＳＱＬでは、何でもかんでもSELECT文で必要あればWHERE句を使うのでしょうか？
２．一意的な伝票番号を得るには、ＲＤＢでも上のようにテーブルのレコードロックを使うのが一般的ですか？

回答No.2

bardfish · ベストアンサー

MicrosoftのVisual Studioのフリーエディション「VisualStudio Express」というものがあり、データベースもMicrosoft SQLServer Expressがあります。
VisualStudioはVisualStudio DesktopというものならばWindowsタブレットではなくWindowsデスクトップ用のソフトも解決できるしVisualBasicも使えます。
SQLServer Expressもライセンスに制約があるだけで機能としては最低限のものが揃っています。運用管理ツールが使えないだけと思ってください。

で、リレーショナルデータベース(SQLServerやOracle、MySQLなど)ではテーブルを作成するとき(CREATE TABLE)にプライマリーキーとインデックスキーを作るのが一般的ですが、インデックスキーはあとからでも追加でき作らなくてもWHERE(抽出)やORDER BY(ソート)で使うことができます。ただ、抽出やソートのときにインデックスを張っているときと貼っていないときとでは結果を得るまでの時間が全然違います。
Accessの場合ですが、インタデックスのないテーブルをループさせる処理で10分かかっていたものがインデックスを張ったら3分かからなくなったということがあります。

レコードロックに関してですが、RDB(リレーショナルデータベース)ではトランザクション処理と呼びます。
PL/SQLとかT-SQLとRDB製品によって呼び方は違いますが、トランザクションの開始のはBEGIN TRANS、処理が正常終了して更新内容を反映させたいときはCOMMIT、エラーが発生し更新開始前の状態に戻したいときはROLLBACK。という流れになります。
.NET版のVBusial BasicではSystem.Data.sqlclientという.NETライブラリを使用するのですが、SqlConnection、SqlCommand、SqlDataReaderとかDataAdapterなどを駆使してトランザクションを実現します。
残念ながらExcel365のVBAでは.NETは使えない感じです。Excel2019では試したことありません。
あとはDataTable、DataRowという変数と同じ使い方が出来るデータオブジェクトがあるのですが、これがまたすごく便利で配列変数とか構造体を使わなくなりました。
Dim TableData as DtaaTable
TableDtaa = New DataTable("構造型の形をとった配列みたいなもの")
TableData.Columns.Add("列1")
TableData.Columns.Add("列2")
TableData.Columns.Add("列"3)
' ここまでがCREATE TABLEみたいなモノ。
Dim RowData as DataRow
RowData = TableData.NewRow　　　' 新しい行の追加を宣言
RowData("列1") = "データ。Excelのセルの値を代入してもいいしテキストボックスに入力された値でもなんでもいい"
' 以降、列3まで値を代入。
TableData.Rows.Add(RowData)　　　' セットした値をテーブルに反映

これを繰り返すことで構造体と同じような配列が使えます。
更新対象では泣いてデータベースのテーブルの内容をメモリに持ち続けることができ、DataTable型で宣言した変数に対してSQLみたいに抽出したりソートを掛けたりすることもできます。多少バグがあるみたいですが…

VisualBasicでSQLServerに接続するコードとかテーブルのデータを取り出したりINSERTなど結果を返さないコードの書き方はネットを探せばサンプルがたくさん見つかります。

頑張って使い方を覚えてください。

私はVB4とAccess2.0のときに覚えて、VB6にSQLServer4.0を最後に最近までプログラミングから遠ざかっていましたが、今年に入ってから覚え直しました。

お礼

早速のご回答ありがとうございます。参考にさせていただきます。

回答No.1

FEX2053

excelを使うなら、面倒な事をしないでも、各端末で別々の入力用ブックを立ち上げさせ、各端末での処理が終わったら、マスターになるブックに上書き処理すればオッケー、別にSQLなんぞ走らせなくても済みます。

端末数が少なければ、同時に書き込むことはほぼないので、Excelそのものの排他処理（要は誰かが書いていれば、他の人の書き込みはエラーになる）で十分、同じレコードの複数書き込みも「後に書いた結果が上書きされる」という、作業側の共通認識ですべてが終わります。

企業ではないのですから、きちんとしたマニュアルが継承されることはまず考えられません。ですから、システムの構成は可能な限り単純に誰でもわかるようにしておくことが肝要ですよ。

お礼

早速のご回答ありがとうございます。参考にさせていただきます。
随分昔から市販のパソコン売上パッケージでも在庫機能（在庫確認／在庫引き当て）は標準装備なので、質問に詳しく述べて無くて申し訳なかったのですが、オンライン／リアルタイム処理を前提にしています。