ACCESS　VBAでクエリのフィールドプロパティを一括変換

前提・実現したいこと

ACCESS、VBA初心者、teratail新参者です。何か不手際あったら指摘してください。
使用しているACCESSは2019の64ビットです。
現在レポートを作成しており、250個ほどのクエリの既成フィールドに対して
プロパティを一括変換したいです。
各フィールドの参照元のテーブルでの設定は、全て（IDのオートナンバー型を除くと）

データ型：数値型
フィールドサイズ：倍精度少数点型
書式：標準　　　　　　　　　です。

以下が見よう見まねで書いたコードです。

VBA
1
2Dim db As DAO.Database
3Dim rs As DAO.Recordset
4Dim fld As DAO.Field
5Dim prp As DAO.Properties
6
7Set db = CurrentDb
8Set rs = db.OpenRecordset("クエリ１")
9
10'各フィールドプロパティ書き換え
11For Each fld In rs.Fields
12     With fld
13         ’データ型が数値であれば
14          If .Type = dbInteger Then
15               '書式を「標準」、小数点を第3位に設定
16                Set prp = .CreateProperty("Format", dbDecimal, "Standard")
17                Set prp = .CreateProperty("DcimalPlaces", dbDecimal, 3)
18                .Properties.Append prp  
19          End If
20     End With
21Next
22

発生している問題・エラーメッセージ

エラーメッセージ
「データ型が変換できない」と怒られます。
そもそもこのコードはあっていますでしょうか。
これを解消するにはどうすればよいでしょうか。

該当のソースコード

下記2行目です。

VBA
1If .Type = dbDouble Then
2     Set prp = .CreateProperty("Format", dbDecimal, "Standard")
3     Set prp = .CreateProperty("DcimalPlaces", dbDecimal, 3)
4EndIf

試したこと

CreatePropertyの2番目の引数(データ型)を間違えていると思い、以下のものはすべて試しましたが、うまくいきませんでした。

dbBigInt
dbBinary
dbBoolean
dbByte
dbChar
dbCurrency
dbDate
dbDecimal
dbDouble
dbFloat
dbGUID
dbInteger
dbLong
dbLongBinary
dbMemo
dbNumeric
dbSingle
dbText
dbTime
dbTimeStamp
dbVarBinary

補足情報（FW/ツールのバージョンなど）

if文にある　　「If .Type = dbInteger Then」　　ですが、
dbLong,dbDoubleでも入りました。
エラーは起きませんでしたが、書いた本人がなぜなのか全く理解できていません。。。(蛇足かもしれませんが関係あるかもしれません。)
知恵をお貸しください。よろしくおねがいします。

hatena19

2019/09/26 12:58

コードはマークダウン記法のコードタグ内にいれてください。下記のようになります。 ```vba コード ``` あるいは、コードを選択してツールバーの<code>をクリック

行動規範の内容に同意します

回答1件

ベストアンサー

いろいろ間違いかあります。下記の点について理解してください。

フィールドプロパティ等のクエリの設定の変更は、Recordsetではできません。

Recordsetはデータ操作するときに使用するものです。
プロパティ設定はQueryDef（クエリ定義）に対して実行します。テーフルならTableDef。

プロパティには、初期状態で存在しているものと、設定したときに生成されるものがあります。

書式プロパティは初期状態では存在しません。小数点以下表示桁数は存在しています（ただし演算フィールドの場合は初期状態では存在しない）。

存在しているプロパティはそのまま設定できますが、存在していないプロパティは生成（CreateProperty）してから追加します。一度、追加するとその後は必要ありません。存在しているのに追加するとエラーになります。

書式プロパティは初期状態では存在していませんか、一度設定した後は存在しています。存在していない状態で設定するとエラーになりますので、その場合は、生成して追加する、という処理にします。

　
1.~~ 演算フィールドの小数点以下表示桁数プロパティは初期状態で存在しているので、そのまま設定できます。~~演算フィールドの小数点以下表示桁数プロパティは初期状態では存在していないので、同様にエラーチェックしてエラーの場合は生成して追加。

上記の点に留意してコーディングすると下記のようになります。

vba
1Public Sub クエリプロパティ変更実行()
2    Dim qd As DAO.QueryDef
3    Set qd = CurrentDb.QueryDefs("クエリ1")
4
5    Call クエリプロパティ変更(qd)
6End sub
7
8Public Sub クエリプロパティ変更(qd As DAO.QueryDef)
9    
10    Dim Fld As DAO.Field
11    For Each Fld In qd.Fields
12        With Fld
13            '倍精度少数点型(dbDouble)であれば
14            If .Type = dbDouble Then
15                On Error Resume Next
16                '書式プロパティを標準に設定
17                Dim prp As DAO.Property
18                Fld.Properties("Format").Value = "Standard"
19                '書式プロパティが存在しないとエラーになる
20                If Err <> 0 Then
21                    '書式プロパティ生成してフィールドに追加する
22                    '書式プロパティのプロパティ値はテキスト型(dbText)
23                    Set prp = Fld.CreateProperty("Format", dbText, "Standard")
24                    Fld.Properties.Append prp
25                    Set prp = Nothing
26                    Err.Clear
27                End If
28                '小数点以下表示桁数を3に設定
29                Fld.Properties("DecimalPlaces").Value = 3
30                If Err <> 0 Then
31                    '書式プロパティ生成してフィールドに追加する
32                    '書式プロパティのプロパティ値はテキスト型(dbText)
33                    Set prp = Fld.CreateProperty("DecimalPlaces", dbByte, 3)
34                    Fld.Properties.Append prp
35                    Set prp = Nothing
36                    Err.Clear
37                End If
38                On Error GoTo 0
39            End If
40        End With
41    Next
42End Sub
43

処理するクエリが多数あるとのことなので、プロパティ設定は関数にしました。
引数を変更するだけで処理するクエリを指定できます。
一つのクエリの処理に成功したら、あとは、For Each qd In CurrentDb.QueryDefs でループ処理すればすべてのクエリのプロパティを一気に変更できます。

補足

クエリでプロパティを変更しても、それをレコードソースとする作成済みのレポートには反映されない場合があります。プロパティ変更後に作成したレポートなら、反映されますが。
今後、プロパティ変更が発生する可能性があるなら、レポートに対してプロパティ変更する処理を作成して、それを実行したほうが確実です。

投稿2019/09/26 14:29

編集2019/10/02 12:59

hatena19

総合スコア34073

m.trsk

2019/09/27 01:53

質問修正への指摘と、早速の回答ありがとうございます。よく理解しないままにコードを書いて試行錯誤していたので、非常に参考になりました。コードまで書いていただきありがとうございます。一つだけ質問なのですが、適用の仕方が悪かったのか、実行するとエラーが出ます。エラー箇所は以下の一文です。 ```vba '小数点以下表示桁数を3に設定 Fld.Properties("DecimalPlaces").Value = 3 ``` エラーメッセージは「プロパティが見つかりませんでした」　　　　です。（クエリのデザインビュー上のプロパティでは引っかかったフィールドにも小数点以下表示桁数の欄があったのですが、、、）クエリで式ビルダーを使った演算フィールドでエラーになっているようで、テーブルからもらったものはちゃんと書き換えられています。ちなみに問題の箇所はただの算術演算です。書式プロパティの不在エラーのif文を小数点についても真似てみようとしたのですが、そもそも入らないようでした。コードまで書いていただいたのに恐縮なのですが、どうすればいいか教えていただけないでしょうか。