Microsoft Access 掲示板

良いか悪いかだと、うーん、ちょっとだけ、よくないですかねぇ
Eのテーブルは、変更があったものだけ、ではなく、履歴としてすべてを記録するようにすると余計な存在有無チェックがなくなるのでいろいろとはかどります（Dlookupを使うにせよ、SQLで全部やるにせよ高速化になります）

特定日のデータを期間の設定（開始日、終了日）から見つけ出す作業は、ACCESSでは特に苦手とする部類だと思います。モジュール化して対応しようとすると、どうしても「DLookupは遅い」のボトルネックに引っかかる形になるので、SQLを駆使してクエリ一つに書き上げるというのが一般的な回答になるんじゃないでしょうか？（SQLを直接記述するんでそもそもACCESSレベルでは一般的ではない）

遅い早いに関しては個人の感覚によるところなので、実際に運用する件数n件に対して、処理時間yでそれが、実運用に耐える/耐えない時間であるという評価基準が必要になります
それによって、手法のシンプルさ（メンテナンス性）と実処理時間のバランスが取れるように、最適な回答が変わってくるでしょう

自分はSQLゴリゴリ書くのはあまり得意ではないので、そっちの方向は他の人に任せます（お願いします）
VBAの話をするのであれば、まずは質問にあるところで、

このモジュールでいくつかの値（支払条件テキスト、締め実日等）も同じパターンで値を抽出している

これは、遅くなる原因としてかなりの比重がありそうです

Dlookup("ID")
Dlookup("支払条件テキスト")
Dlookup("締め実日等")

Set dbs = CurrentDb
Set rs = dbs.OpenRecordset("～")
id = rs!ID
支払条件 = rs!支払条件テキスト
締め実日 = rs!締め実日
rs.Close

上記Dlookupだけで処理した場合はDlookupのボトルネック＋検索捜査が3回発生するのに対し、下記、レコードセットを取得した処理では、ボトルネック部分が1回、捜査処理も1回でそのほかのデータ取得は一瞬で終わります
先に挙げたリンク先でも、こういう処理をしたいならSQL文を使ったほうが良いと言っていますね
特に、一つのレコードから複数のフィールドの値を参照したいのであればDlookupの出番はないです

もう少し、VBAの高度な話をすると、

Set dbs = CurrentDb

これにかかる時間が相当にありそうだ、と、先に挙げたリンク先に指摘があります。オリジナル関数の呼び出し事（実処理のレコード数分だけ）実行しているとこれもボトルネックになるので、これをやらないように組むということもできます

オリジナル関数の外で変数を用意しておく

Public dbs As Database
Public rs As Recordset

Sub Init()
  Set dbs = CurrentDb
End Sub

Function オリジナル関数()
  Set rs = dbs.OpenRecordset("～")

Init関数を事前にどこかで1度だけ呼び出して、「ボトルネックになるDatabaseオブジェクトの生成」を1回のみにするなんて方法も考えられるでしょう

先に挙げたように、許容点はどこか？という話になるので、詳しい解説は抜きにしてひとまずこのくらいにしておきます

hiroton様ありがとうございます。「なぜDLookupが遅いのか？」はなんとなくレベルですが理解しました。
今回作成のモジュール概要は　Ａ：顧客テーブル　Ｂ：売上テーブル　Ｃ：締め日テーブル　Ｄ：売上明細テーブル 等があります。各テーブルにはオートナンバー型ＩＤがあります。売上毎締め日期日クエリでそれぞれ連結させて売上毎の締め日付、支払期日等を他のモジュールで算出してまして、それは問題なく機能してました（この時は処理速度も速かったです）。
Ａには顧客毎の支払条件があります（Ｃから選択）。その支払条件が変更になる場合が発生しました（例えば売上日付が2026/2/1から締め日が変更）。Ａテーブルの支払条件を改定後の内容にしてしまうと、過去の売上分も改訂された締め日になってしまうので、対策としてＥ：変更前支払条件テーブルを作成し（顧客ＩＤ、変更前の締め日ＩＤ、初期日、最終日　等のフィールド）それに変更のあった顧客分のみ変更前の支払条件を入力してます。今回作成のモジュールにＤＣｏｕｎｔでテーブルＥから対象顧客分をカウントして、それが０の場合はＡテーブルからから、1以上ならＥテーブルから締め日ＩＤを抽出しました。今後同一顧客で２回以上の変更もあるかもを想定して売上日付をＥテーブルの初期日、最終日の期間内かどうかも判定させて条件に合う締め日ＩＤを抽出させてます。このモジュールを売上毎締め日期日クエリに組み込み実行すると結構時間かかるので、Ｄｌｏｏｋで締め日ＩＤを抽出が原因？でしたのでＳＱＬでしたらと思ったのです。
このモジュールでいくつかの値（支払条件テキスト、締め実日等）も同じパターンで値を抽出しているのも遅い原因と思いますが。
この方法が良くないでしょうか？

いろいろ突っ込みどころはあるんだけど、
パッと見た感じ、「Dlookupが遅い理由をそのままに、Dlookupと同じ機能をわざわざ自前で作ろうとしている」感じですね
「なぜDLookupが遅いのか？」を理解しないと改善にはならないです

定義域集計関数とSQLの比較（T'sWare さん）

hatena様ありがとうございました。以前使っていたんですがどこにしまったか忘れまして。これを使えば
かなり体裁がよくなります。

下記のページの方法3の手法かな？

定型枠に一文字ずつはめ込み印刷 - hatena chips

コントロールソースを
=Format([フィルード名],"@@@@@@@@@@")

文字配置を均等割り付けに設定

@の数で桁数を指定してテキストボックスの幅で文字の間隔を調整します。

普通の方法でいくと123456と間隔が短く読みづらいため
　質問を編集して現状のスクリーンショットを追加できますか？

　その手法(以前レポートの数値コントロールに＠＠＠等の記号で調整した記憶)をご教示下さい？

Access レポートで数値を入力する際間隔を調整する方法をおたずねします。
普通の方法でいくと123456と間隔が短く読みづらいため、以前レポートの数値コントロールに＠＠＠等の記号で調整した記憶がありますので、その手法をご教示下さい。

JOINを使用すればテーブルを分けても、一度でデータを取り出せるケースは増えます。
しかし、フィールド数が少ないと、複数回SELECTする事は避けられないですよね。

「どのような構造を持ったテーブルを参照し、どのような条件に該当するレコードを抽出し、どのような演算、変換処理を行い、その実行結果を、どこに、どのようなレイアウトで出力しようとしているのか」によるとしか。

現時点では具体的にそれが示されていないので何とも答えようがありませんが、JOIN も SELECT も「する必要があればそうする」だけのことでしょう。

今度は、配列を大きくし過ぎると止まるという制約が出てきます。

配列を使わないでシートに書き出していたら、この状態に至る以前に処理できなくなってるはずです。

同上。
唐突に配列やシート書き出しの話をされましても、その辺りの詳しい事情や背景が一切不明であるため、答えようがありません。

例えば「参照元のテーブルに膨大な件数のレコードが格納されていて、その全てのレコードの全てのフィールドを取得しようとしている」といった場合、レコードの件数やフィールドの数、格納されているデータの大きさに比例してアウトプットに時間がかかってしまうのは当然の結果と言えます。
その上で「出来るだけ実行時間を短縮できるようにするにはどうすればよいか」という問題は全く別の話です。

必要事項には回答しないと許可が下りないので、項目を減らすわけには行きません。

「必要事項には回答しないと許可が下りない」がどういうことなのかが不明瞭ですが、その項目が本当に必要なものなのであれば、無理に減らす必要はありません。
ただ「リレーショナルデータベース上で管理するテーブルとして適切に構造化されているか」ということが問題なのです。

過去のスレッドから推定し得る限り、実際のフロントエンドは Access ではなく Excel であると思われますが、もし「元々 Excel で作っていた表（やたら列の多い非正規形テーブル）のレイアウトをそっくりそのまま Access のテーブルに落とし込んだ」ということであるなら、少なくともそのようなテーブル設計手法は Access に限らず、あらゆるリレーショナルデータベースシステムに適していません。

「どのような構造のテーブルをデータベース上に定義するか」
「どのようなインターフェースを用いてデータベース上のデータにアクセスするか」
「データベースに対してどのような問い合わせを行い、どのようなレイアウトの出力結果を得るか」
は、基本的に分けて考えるべきでしょう。

hatenaさん　曖昧な返信になってました（すみません）。区分条件をそれにするならば同じ区分内での重複は不可にしなくてはなりませんので「編集を続けるか」確認はなしになります。

JOINを使用すればテーブルを分けても、一度でデータを取り出せるケースは増えます。
しかし、フィールド数が少ないと、複数回SELECTする事は避けられないですよね。
複数回SELECTしても、配列に追加すれば済む話なので大したロスにはなりませんが、
今度は、配列を大きくし過ぎると止まるという制約が出てきます。

配列を使わないでシートに書き出していたら、この状態に至る以前に処理できなくなってるはずです。
必要事項には回答しないと許可が下りないので、項目を減らすわけには行きません。

難しくて、色々費用も掛かってくるであろうMy SQLは使いたくはないので、できるだけしがみつきますが、、、
「そろそろ、お前もっと金使えよ」という、IT君からの忠告げなんでしょうね。

どうしようもなくなったらMySQL当たりに変更するしかなさそうです。
 どこに変更しても上限はあるんですよ。根本的に見直しましょう。

フィールド名が限界に近づいていたのが原因でした。
とりあえず、テーブルを分割して凌ぎましたが、
どうしようもなくなったらMySQL当たりに変更するしかなさそうです。

ただ、AIで他の言語の書き換えは簡単にできるようになっているので、
いままで作ったものが、全く無駄になる事が無いのは救いです。
おそらく、AIに命令しただけでは、同じ処理ができるシステムを作ってくれないと思います。

当面の対応策としては、
[ファイル]タブ > [オプション] > [現在のデータベース] を選択し、「閉じるときに最適化する」にチェックを入れて最適化を試してみます。

strSQL = "INSERT INTO 事業所データ([事業所番号]) VALUES('test2')"
adoCn.Execute strSQL 'SQLを実行して対象を追加

実行時エラー-2147467259(80004005)　引数が無効です

手動でデータを入力してみたところ、１回目は引数が無効ですで入力できず
２回目には登録ができる状態が、２回続けて起こりました。

状況的には、既に行われた「XMLファイルのエクスポート→インポート」が最も有効な解決方法であると思われますが。

このテーブルはフィールドが238ありまして限界に近いのですが、

どうも、200超えたあたりから動作が怪しくなる気がしますが、こんなことがあるのでしょうか？

• Access において、1 つのテーブルに定義できるフィールドの数は 255 個までである。

• 1 つのテーブルにおいて、フィールドの追加/変更/削除を繰り返しているうちに、「実際に画面上で確認できるテーブルのフィールド数よりも多いフィールド数分の設定情報が保持されている」と認識される状態となることがある。

• この「フィールド数のずれ」が含まれているテーブルに対してレコードを追加しようとすると、Access データベースエンジンが正常にデータを処理することが出来ず、件のエラーが発生する。

上記の「設定情報」を Access のユーザーインターフェース上で閲覧、編集することは出来ませんので、前述の方法によってそのテーブルをまるごと作り直すことでしか修正できません。

参考: access 実行時に、「引数が無効です」のエラー

いずれにせよ、この現象の性質上、フィールドの数を上限に近いほど増やしたり、1 つのテーブルのデザインの変更（フィールドの追加/削除からの上書き保存）を頻繁に繰り返すことはあまり推奨されません。

[事業所番号]と[事業所名]の他にどのようなフィールドが定義されているのか不明ですが、可能であればテーブル構成の再設計や正規化を検討されることをお奨めします。

strSQL = "INSERT INTO 事業所データ([事業所番号]) VALUES('てすと')"
adoCn.Execute strSQL

「事業所名フィールドに値を入力してください」（主キーは事業所番号に設定されています）で、エラー

テーブル[事業所データ]のフィールド[事業所名]の[値要求]プロパティはどのように設定されているのでしょうか。

事業所データテーブルのフィールドを190にまで減らして

 190まで減らしても多い。その数字が明らかに杜撰な設計とバレバレだから正規化やり直しましょうね。

事業所データテーブルのフィールドを190にまで減らして試してみましたが、同じエラー。
テーブル削除してから、手動で新規作成事業所番号のフィールドを作ったところ、処理はできました。
しかし、XMLファイルのエクスポート→インポートで、テーブルを入れ替えられないとなると、
エラーが起きた時に、データが取り出せなかった場合は、致命的な問題となってしまいます。
取り出せたとしても、フィールドを手動で作り直していたのでは、お話になりません。

そもそも、バックアップしてあったファイルも同時に書き込みできなくなったのでは、バックアップに意味がありません。

正しい対応方法をご存じの方がいらっしゃいましたら、ご指導願います。

仕方ないので、XMLファイルでエクスポート→テーブル削除→インポートで、テーブルを入れ替えました。
今度は、
strSQL = "INSERT INTO 事業所データ([事業所番号]) VALUES('てすと')"
    adoCn.Execute strSQL
「事業所名フィールドに値を入力してください」（主キーは事業所番号に設定されています）で、エラー
試しに
strSQL = "INSERT INTO 事業所データ([事業所名]) VALUES('てすと')"
「事業所番号フィールドに値を入力してください」（主キーは事業所番号に設定されているので当然ですが）で、エラー

いったい、なぜこんなエラーが発生するのでしょうか？

追伸ですが、
手動でデータを入力してみたところ、１回目は引数が無効ですで入力できず２回目には登録ができる状態が、２回続けて起こりました。
やはり、フィールド名が増えてACCESSがキャパオーバーになっていることが原因のエラーなのでしょうか？

hatenaさん　ありがとうございます。流れとしてはそうなります。部品区分があり、異なる区分で同じ品名を登録する場合がありますので。うっかりしてましたが条件を下記の様にした方がいいですね　
Dim　Kubun　AS　Long　　　Kubun=Me.[区分]　の変数追加して　
joken = "[部品型番] = '" & buName & "' And [部品ID] <> " & buID & " AND [区分]=" ＆ kubun
これで同じ区分内での同じ品名の重複をチェック（編集中の部品ID分は除く）
宜しくお願いします。

バックアップのACCESSファイルでも、エラーが出るのでテーブルが壊れたの原因とは思えないのですが、
コードに間違えは無いので、問題はACCESSですよね。
このテーブルはフィールドが238ありまして限界に近いのですが、
こういう事も、エラーが起きる原因になりうるのでしょうか？

実は、当初はEXCELだけで処理していたのですが、
シート数やデータが多くなると不具合が頻発するようになってSQL ＋ ACCESSに移行しましたが、
ACCESSでもフィールド数が増えると、原因不明のエラーが増えているような気がします。
フィールド名の合計バイト数みたいなものもあったような気がします。
フィールド数が多いとここに抵触する可能性も出てきますが、
そうであればオーバーした時点で、フィールドが登録できなくなるはずですし。

どうも、200超えたあたりから動作が怪しくなる気がしますが、こんなことがあるのでしょうか？

確かにテーブルインデクスで重複なしにするといいのですが、品名入力時に注意喚起メッセージを出せるといいと思ったのです。

なるほど。レコード保存時（フォームの更新前処理）ではなく品名入力時（テキストボックスの更新前処理）でチェックしたいと言うことですね。そういうUIもありますね。
重複を排除するにはインデックスとこれの両方とも設定しておくと確実ですね。

今回は部品名や会社名等の入力フォームで名前の重複入力チェックを下記コードで行っています

ということなので、今回の[部品型番]だけでなくいろいろな場面での重複チェックを共通化したいと言うことですね。

まずは共通化する前に品名テキストボックスの更新前処理にコードを書くことになります。
その場合、質問のコードだと下記のようなフローになってます。

自レコード以外のIDで[部品型番]に重複がないかチェック(DCount）
　↓
重複がなければ Exit Sub
重複があるときはメッセージで「編集を続けるか」確認
　↓
「はい」で更新して次へ
「いいえ」で[部品型番]の入力を元に戻す

これだと、重複があった場合に「はい」を選択すると部品型番の重複を許すことになりますが、
それでいいのでしょうか。
とりあえずこれを確認したいと思います。

「insert into 引数が無効です」でググってみるとテーブルが壊れているんじゃないか？的な対応策があるようです

漢字の諸々をアルファベットに変更してもエラーになるかしら？

hatenaさん ありがとうございます。確かにテーブルインデクスで重複なしにするといいのですが、品名入力時に注意喚起メッセージを出せるといいと思ったのです。[部品ID] <>としてますので編集中の分以外をチェックさせています。というのは例えば品名を”あいう”でレコード保存後に”え”を追加入力後に”え”を削除した場合にはメッセージが出ない様にしたいからです。あと Me.Undo にすると確かにすべての入力が取り消されるので品名部のみを元に戻した方がいいですね。

車輪の再開発に意味があるのかしら？データベース設計が杜撰なだけでしょう。

共通化する前に、そのコードで問題ないのか疑問があります。

部品IDと部品型番の組み合わせで重複をチェックしてますが、部品IDが異なれば部品型番に重複があってもいいのでしょうか。現状のコードだと部品型番に重複があっても登録可能になっています。

あと、更新前処理で Me.Undo してしまうとすべての入力が取り消されてしまいますがそれで問題ないですか。

あと、データベース設計で重複チェックは、テーブル設計の方でインデックスなどを使用して設定するのが確実ですが、その設定はしていますか。

プリンタの設定で出力されるデータが変わることはありえません。

マクロの「where条件式」に設定した式が間違っているのでしょう。
設定した式を提示してください。

支払に関しては、本社が支払う場合と工場が支払う場合とでは口座が違うので、メインには格納できないかと。

そういうことなら、両者の関係は一対多の関係になりますので、口座情報は別テーブルにすればいいでしょう。

一例をあげると下記のようなテーブル構成になります。

会社マスター
※会社ID
　会社名
　代表者名
　・・・

本社・支社マスター
※本社・支社ID
　会社ID
　支払口座ID
　本社・支社区分
　本社・支社名
　住所
　・・・

支払口座情報
※支払口座ID
　金融機関名
　支店名
　預金種類（普通 or 当座）
　口座番号
　口座名義
　・・・・
　
リレーションシップ設定
会社マスターﾎ.会社ID → 本社・支社マスター.会社ID
支払口座情報.支払口座ID → 本社・支社マスター.支払口座ID

hatena様
メインテーブルに会社のデータと支払情報のデータを格納し、
サブテーブルに本社・支社区分と住所のデータを格納するような感じの設計になるということでしょうか。

支払に関しては、本社が支払う場合と工場が支払う場合とでは口座が違うので、メインには格納できないかと。

かといってサブに支払情報を持たせると、
ある会社によっては複数ある工場が同じ支払情報を持っているので、同じ支払情報のデータが重複してテーブル内に格納されます。

話が食い違っていたらすみません。

上記の投稿は私のものです。
外出先からログインせずに投稿したので名前なしになりました。

支払いを、本社がする場合と各工場・事業所がする場合があります。

これに対するひとつの解決法としては、下記のような設計にします。

メインテーブルには会社名、代表者名、支払先情報などの共通データを格納（会社マスター）
本社と各工場・事業所データはサブテーブルに格納する(本社・支社マスター)
このテーブルには種別フィールドを追加して、そこで本社か支社か区別できるようにする

なので、支払情報が会社マスターにだけでなく、工場・事業所マスタにも必要となります。

これは、ルックアップ機能で参照するようにすればいいでしょう。

もうひとつ面倒なことがありまして、
支払いを、本社がする場合と各工場・事業所がする場合があります。
なので、支払情報が会社マスターにだけでなく、工場・事業所マスタにも必要となります。

hatena様
ありがとうございます。

その場合の設計は

メインフォームの元テーブルのフィールドは
会社ID
会社名
住所
など

サブフォームの元テーブルのフィールドは
事業所ID
会社ID
事業所名
住所
など

会社IDで紐づけ、事業所IDを主キーにする感じですか？

売上が工場・事業所なので、
売上入力の際は、売上先顧客のフィールドとしては、事業所IDとするのでしょうか？

ただ、工場・事業所がなく本社のみの会社もあります。

請求書の発行は会社単位でまとめる会社もあれば、
工場・事業所で発行する会社もあります。

会社単位でまとめる場合もあるのなら、
会社と工場・事業所は一対多の関係になるので、データベース設計の基本としては、
一側から先に入力してから多側を入力するというのが基本になります。
会社マスターの方に会社情報、支払情報データを持たせます。

私がマスター登録フォームを作成するなら、メインサブフォーム形式にして、
メインフォームに会社マスター、サブフォームに工場・事業所テーブルを配置するという設計にしますね。

hatena様
請求先情報テーブルと支払情報テーブルは分ける必要はありませんでした。1つにします。

作成中の画面をアップします。
画面上下スクロールで合計2枚です。

オートルックアップ機能をやってみます。

請求書の発行は会社単位でまとめる会社もあれば、
工場・事業所で発行する会社もあります。

「請求先情報テーブルや支払情報テーブルを作成して、顧客テーブルとIDで紐づければと思いましたが」
というのは、請求先情報テーブル、支払情報テーブルのフィールドにそれぞれ請求先情報ID、支払情報IDを主キーとして設け、
顧客テーブルにも請求先情報ID、支払情報IDを設け、リレーションで紐づけるという意味です。

上記の点は了解しました。

もうひとつ確認したいのですが、請求先情報テーブルと支払情報テーブルというように２つに分けるのはどういう理由でしょうか。請求先（会社）と支払情報は一対一の関係ならば分けずに一つにまとめたほうがいいでしょう。
一つの会社に対して支払先が複数ある（一対多の関係）ならば分ける必要はありますが。

一対一の関係ならば請求先情報と支払情報をまとめたテーブルを作成してこのテーブルの主キーを顧客テーブルに外部キーとして持たせて、クエリでリンクさせればオートルックアップ機能が働くので検索せずとも自動で表示させることは可能です。

すみません。
「請求先情報テーブルや支払情報テーブルを作成して、顧客テーブルとIDで紐づければと思いましたが」
というのは、請求先情報テーブル、支払情報テーブルのフィールドにそれぞれ請求先情報ID、支払情報IDを主キーとして設け、
顧客テーブルにも請求先情報ID、支払情報IDを設け、リレーションで紐づけるという意味です。

ユーザーは請求先情報、支払情報を入力する際、請求先情報IDが分からないので、検索しないといけなくなるかと思います。という意味でした。

hatena様
オートルックアップクエリを調べてみました。
顧客IDは連番数字なので、どの顧客が何番かは検索（検索フォーム等で）しないとわかりません。

ざっとオートルックアップクエリをネットで見てみた感じの感想ですが、よく研究してみます。

hatena様
請求書の発行は会社単位でまとめる会社もあれば、
工場・事業所で発行する会社もあります。

請求先情報テーブルや支払情報テーブルを作成して、顧客テーブルとIDで紐づければと思いましたが、
顧客テーブルに登録の際、請求先情報のIDを検索して入力というのも、どうかと思いました。
ユーザー側としてはすんなり請求先情報を入力したいかなと思い。

これに関しては、「オートルックアップクエリ」という機能を使えば、顧客IDと紐づいた請求先情報、支払情報を参照して出力することが可能です。

「Access オートルックアップクエリ」でWEB検索すると解説ページが見つかるのでそれで研究してみてください。