Access (VBA)

Access VBAに関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 11 Pro : Access 2019)
十進型フィールドの検索で正しい結果が取得できない
投稿日時: 22/11/04 14:16:37
投稿者: teruteruru

お世話になります。
 
SQLの検索条件として数値型(十進型)の項目を指定した場合に、
検索結果が取得できない(データなしになる)という現象が発生しております。
 
------------------------------------------------------------
1.新規データベース(mdb/accdbのどちらでもOK)を作成
 
2.新規テーブル(TEST00)を作成
 
3.作成したテーブルにフィールドを4つ(A,B,C,D)追加
 
  ・A,B,C,Dのデータ型を「数値型(十進型)」に設定
  ・A,B,C,Dをまとめて主キーに設定
 
4.モジュールから以下の関数(test)を実行
 
Public Sub test()
    Debug.Print "test - start"
     
    Dim db As Database
    Set db = CurrentDb
     
    ' テーブル内のデータをすべて削除
    db.Execute "delete * from TEST00"
     
    Dim lngLpI As Long
    For lngLpI = 1 To 10000 Step 1
        ' テーブルにデータを追加
        ' 「A=1, B=連番(1〜), C=1, D=1」を各項目にセット
        db.Execute "insert into TEST00 (A,B,C,D) values (1," & lngLpI & ",1,1)"
     
        ' テーブル内のデータ件数を取得
        ' 「A=1, C=1, D=1」の条件ならデータ件数に影響しないはず
        Dim rs As Recordset
        Set rs = db.OpenRecordset("select count(*) as CNT from TEST00 where A=1 and C=1 and D=1")
        Dim lngCnt As Long
        lngCnt = rs("CNT").Value
        rs.Close
        Set rs = Nothing
 
        ' ○回目とデータ件数は同じになるはず
        If lngCnt = 0 Then
            Debug.Print lngLpI & "回目:" & lngCnt
        Else
' Debug.Print lngLpI & "回目:" & lngCnt
        End If
    Next lngLpI
     
    db.Close
    Set db = Nothing
 
    Debug.Print "test - end"
End Sub
------------------------------------------------------------
 
test関数を実行すると、イミディエイトウィンドウに
「2501回ごとにデータ件数がゼロ」として出力されてしまいます。
 
同様の現象が発生しますでしょうか?
何か情報をご存じの方がおられましたら、コメントを頂けると助かります。
 
以上、よろしくお願いいたします。

回答
投稿日時: 22/11/04 18:09:12
投稿者: MMYS

Debug.Printのコードが実行される条件はlngCntがゼロの時ですよね。
当然、lngCntの値もゼロですから「2501回ごとにデータ件数がゼロ」となるのは当然の結果でしょう。
 
------------------------------------------------------------
        ' ○回目とデータ件数は同じになるはず
        If lngCnt = 0 Then
            Debug.Print lngLpI & "回目:" & lngCnt
        Else
------------------------------------------------------------
 

回答
投稿日時: 22/11/04 18:37:49
投稿者: sk

引用:
・A,B,C,Dのデータ型を「数値型(十進型)」に設定
A,B,C,Dをまとめて主キーに設定

引用:
test関数を実行すると、イミディエイトウィンドウに
2501回ごとにデータ件数がゼロ」として出力されてしまいます。

確かに再現されますね。
 
引用:
select count(*) as CNT from TEST00 where A=1 and C=1 and D=1

・Sum, Count などの SQL 集計関数の呼び出し、もしくは
 GROUP BY 句によるグループ化を実行しようとしている。
 (上記の SQL の場合は GROUP BY 句が記述せずに
 Count 関数を呼び出しているため、テーブル[TEST00]の
 レコード全体で 1 つのグループという扱いになる)
 
・WHERE 句において、十進型のフィールドを対象とする比較条件が含まれている。
 
・その十進型のフィールドが定義されているテーブルにおいて、
 そのフィールドを含むインデックスが定義されている。
 (そのフィールドのみで構成された単一フィールドインデックス(重複あり)、
 またはそのフィールドを含む複数フィールドインデックス)
 
・そのテーブルにおいて、その単一の比較条件に該当するグループの
 レコード件数が 2501 の倍数である。
 
こちらで検証した限りでは、上記の全ての条件が満たされたグループについては
本来抽出されるべきはずのレコードが抽出されないという状態になりました。
 
引用:
For lngLpI = 1 To 10000 Step 1

例えばループ回数を 2501 にした上でプロシージャを実行して
[TEST00]に 2501 件のレコードが格納された状態にしてから、
Access 上で同様の選択クエリを実行すれば確認できるはず。

投稿日時: 22/11/07 14:18:34
投稿者: teruteruru

コメントありがとうございます。
 
もう少しテーブル構造をシンプルにした上で、
WHERE句を変えつつ、EOFの判定も加えて調査してみました。
 
------------------------------------------------------------
1.新規テーブル(TEST01)にフィールドを2つ(A,B)用意
 
  ・A 数値型(長整数型) Aのみ主キーに設定
  ・B 数値型(十進型)  インデックス:はい(重複あり)
 
2.モジュールから以下の関数(test01)を実行
 
Public Sub test01()
    Debug.Print "test01 - start"
 
    Dim db As Database
    Set db = CurrentDb
 
    '************************************************************
    ' テーブル内のデータをすべて削除
    '************************************************************
    db.Execute "delete * from TEST01"
 
    Dim lngLpI As Long
    For lngLpI = 1 To 10000 Step 1
         
        '************************************************************
        ' テーブルに1件ずつデータを追加
        '************************************************************
        ' A=連番(1〜)、B=1
        db.Execute "insert into TEST01 (A,B) values (" & lngLpI & ",1)"
 
        '************************************************************
        ' テーブル内の全データを取得(EOFを判定)
        '************************************************************
        Dim rs1 As Recordset
' Set rs1 = db.OpenRecordset("select * from TEST01") '@
' Set rs1 = db.OpenRecordset("select * from TEST01 where A>0") 'A
' Set rs1 = db.OpenRecordset("select * from TEST01 where B=1") 'B
        Set rs1 = db.OpenRecordset("select * from TEST01 where A>0 and B=1") 'C
 
        ' 常にデータはあるはず(Debug.Printによる出力はないはず)
        If rs1.EOF Then
            Debug.Print lngLpI & "回目:データなし EOF=" & rs1.EOF
        Else
' Debug.Print lngLpI & "回目:データあり EOF=" & rs1.EOF
        End If
 
        rs1.Close
        Set rs1 = Nothing
         
        '************************************************************
        ' テーブル内の全データ件数を取得(COUNTを判定)
        '************************************************************
        Dim rs2 As Recordset
' Set rs2 = db.OpenRecordset("select count(*) as CNT from TEST01") '@
' Set rs2 = db.OpenRecordset("select count(*) as CNT from TEST01 where A>0") 'A
' Set rs2 = db.OpenRecordset("select count(*) as CNT from TEST01 where B=1") 'B
        Set rs2 = db.OpenRecordset("select count(*) as CNT from TEST01 where A>0 and B=1") 'C
        Dim lngCnt As Long
        lngCnt = rs2("CNT").Value
        rs2.Close
        Set rs2 = Nothing
 
        ' ループ回数=データ件数のはず(Debug.Printによる出力はないはず)
        If lngLpI <> lngCnt Then
            Debug.Print lngLpI & "回目:○回目≠データ件数 CNT=" & lngCnt
        Else
' Debug.Print lngLpI & "回目:○回目=データ件数 CNT=" & lngCnt
        End If
 
    Next lngLpI
 
    db.Close
    Set db = Nothing
 
    Debug.Print "test01 - end"
End Sub
------------------------------------------------------------
 
イミディエイトウィンドウへの出力を確認すると、
以下の結果となりました。
   
 ・@、Aの場合 EOF :正常
         COUNT:正常
 ・B  の場合 EOF :正常
         COUNT:異常(レコード件数が2501の倍数になる度にゼロ)
 ・C  の場合 EOF :異常(レコード件数が2501の倍数になる度にデータなし)
         COUNT:異常(レコード件数が2501の倍数になる度にゼロ)
 
数値型(十進型)フィールドのインデックスが参照された場合に、
今回の現象が発生しているようですね…。

回答
投稿日時: 22/11/07 15:47:26
投稿者: sk

引用:
数値型(十進型)フィールドのインデックスが参照された場合に、
今回の現象が発生しているようですね…。

そうですね。
フィールドのデータ型が十進型ではなく長整数型であれば正常に抽出されますし、
十進型のままであってもインデックスに含まれていなければ同じく抽出されます。
 
実行されているコードや SQL の内容については何ら不備はなく、
Access のデータベースエンジン側の不具合であると思われます。
(何故 2501 の倍数なのか、という理由に関してはまるで見当もつきませんが)
 
テーブル定義を変更しない場合における現時点での回避策は、
そのフィールドの値を CLng や CDbl などの関数によって
型変換した結果と数値リテラルを比較するように条件式を
組み上げることぐらいかと。
 
引用:
・A 数値型(長整数型) Aのみ主キーに設定
・B 数値型(十進型)  インデックス:はい(重複あり)

引用:
select count(*) as CNT from TEST01 where A>0 and B=1

そのフィールドの値が Null であるレコードが存在しないのであれば、
例えば次のように書き換えればよいはず。
 
-------------------------------------------------------------
 
select count(*) as CNT from TEST01 where A>0 and CLng(B)=1
 
-------------------------------------------------------------
 
もしその十進型のフィールドに、長整数型や通貨型の有効範囲を
超える数値が格納され得るのであれば、多少困ったことになりますね。

投稿日時: 22/11/07 15:47:56
投稿者: teruteruru

コメントありがとうございます。
 
なるほど。
たしかにSQLを書き換える(CLngで変換する)ことで正常に動作しますね。
型変換によってインデックス自体が無効になるのでしょうか…。
 
今回は対象フィールドに格納する数値の桁数に余裕があるので、
フィールドサイズを「十進型」→「長整数型」に変更する予定です。
 
最初からAccessでテーブルを作成する場合だと
「十進型を選択」かつ「インデックスを設定」することはあまりないかと思いますが、
SQLServer等からテーブルをAccessにインポートした場合は要注意ですね…。
(numeric型は十進型としてインポートされる)