Access 予めクエリを作っておきたい2

質問

http://okwave.jp/qa/q9003552.html
の続きです。

インポートしたファイルAの赤枠のフィールド名が毎回変わるので、
画像のようなクエリを毎回作成しないといけません。

毎回作成しなくていいように、青枠のフィールド名を可変にしたい・・・
ということです。

回答No.9

piroin654 · ベストアンサー

いちおう、コードを先に載せておきます。

エクスポートのコードです。
ファイルは現在のプロジェクトに出てきます。
簡単なコードですが、場合によってはクエリ名も
変数に格納してもいいかもしれません。
なお、同じ名前のファイルが存在しないかをDir関数を
使って
xlBook.SaveAs strPath & strFile
の前で、確認してもいいかもしれません。
それから、Excelファイルの拡張子は「xls」になっていますが
実際にあわせてクエリ名共々変更してください。

Sub test()
Dim xlApp As Object
Dim xlBook As Object
Dim strPath As String
Dim strFile As String
Dim rs As New ADODB.Recordset
Dim strm1 As String
Dim strm2 As String
Dim strm3 As String
Dim strm4 As String
Dim strm5 As String

'フィールド名用
strm1 = Format(DateAdd("m", -3, Date), "m") & "月"
strm2 = Format(DateAdd("m", -2, Date), "m") & "月"
strm3 = Format(DateAdd("m", -1, Date), "m") & "月"
strm4 = Format(Date, "m") & "月"
'ファイル名用（来月）
strm5 = Format(DateAdd("m", 1, Date), "m") & "月"

strPath = CurrentProject.Path & "\"
strFile = "ファイル" & strm5 & ".xls"
Set xlApp = CreateObject("excel.application")
xlApp.Visible = True
Set xlBook = xlApp.Workbooks.Add

rs.Open "Qクエリ", CurrentProject.Connection, adOpenForwardOnly, adLockOptimistic

'必要なだけクエリのフィールド「型番」以降に追加
'クエリのフィールドの並びの順に
With xlBook.Worksheets("Sheet1")
.Range("A1").Value = "ID"
.Range("B1").Value = strm1
.Range("C1").Value = strm2
.Range("D1").Value = strm3
.Range("E1").Value = strm4
.Range("F1").Value = "メーカー"
.Range("G1").Value = "型番"
'データの先頭位置の指定とデータの貼り付け
.Range("A2").CopyFromRecordset rs
End With

'ファイルのファイル名と保存先の指定
xlBook.SaveAs strPath & strFile

Set xlBook = Nothing
Set xlApp = Nothing
rs.Close: Set rs = Nothing
End Sub

お礼

度々のご回答ありがとうございます。

勉強不足で申し訳ございません。。

こちらのコードを実行する為に、予めQクエリを作成しておく
必要があるんですよね？
で、今月が6月だった場合、そのクエリには「3月」「4月」「5月」の
フィールドがあって、Excelのstrm1（＝3月）、strm2（＝4月）、
strm3（5月）の列のところに貼り付けられるってことですよね。

このコードでExcelに書き出した場合と、クエリを右クリック⇒エクスポート
した場合と、何が違いがあるのか理解できておりません。

結局クエリを手動で作る必要があるのであれば、このコードを実行して
Excelに書き出すメリットって何なのでしょうか。

フォーム上のボタンに当該コードを実行するようにして、ボタン一発で
書き出す・・ってことなのでしょうか。
だとしたら、
DoCmd.TransferSpreadsheet acExport, 10, "Qクエリ",パス＋ファイル名,True
でいいのかな・・・なんて思うのですが。

回答No.10

山田 太郎（@f_a_007）

【追加】テーブル構造の変更を列名に自動反映することは可能でした。

先のテストのようにドロップ＆アドするのではなく列名を変更すれば、それは自動的にクエリに反映されます。もちろん、このAccessの自動反映機能を使うには、インポート手順を見直す必要がありますが・・・。

１、不要なレコードを削除。
２、列名を更新する。
３、データをインポート。

回答No.8

山田 太郎（@f_a_007）

もしかして・・・。

【テーブルの列名を変更してもクエリを修正する必要がないケース】

要は、[テーブルA]の列の取得を個別列名でなくワイルドカード="*"にすれば良いようですよ。

回答No.7

山田 太郎（@f_a_007）

Q、テーブル構造に応じて列名をかえることは？
A、私の力量の範囲では無理です。

１、テーブルの複数の列名が変わってもエラーが発生しない。
２、テーブルの複数の列名が変わってもSQL文の実行結果は変わらない。
３、テーブルの複数の列名が変わったら表示名も変わる。

１と２とは実現可能。でも、３のアイデアは湧いてきませんでした。
つまり、[当月][前月][前々月]などの列名でよければ、クエリは同じのを使えます。

PS、自動列名変換機能は働かず・・・。

手動でテーブルの列名を変更するとクエリにその変更は反映されます。そこで、一縷の望みをかけて添付図のように列名を変更。だが、こういう　ALTER　を使っての変更はクエリには反映されませんでした。よって、３は私の力量では無理でした。

補足

度々のご回答ありがとうございます。

＞Q、テーブル構造に応じて列名をかえることは？

今回はテーブルの列名（フィールド名）をかえる必要はりません。

インポートしたテーブルに、2月、3月、4月、5月とあった場合、
今月が6月なら3月、4月、5月のフィールドを使いたいだけなのです。
※要は、今月を基準に、－3ヵ月、－2ヵ月、－1ヵ月のフィールドを
　使いたい。

回答No.6

piroin654

4ヶ月のデータの推移を見るために、データを
1ヶ月毎にずらしてExcelの内容を変更する、
ということなのでしょうが、もし、Excelに
挿入するデータがAccessにあるならば、やはり
Excelファイルのデータの更新をコードで
自動変更、たとえば古いデータの列の削除、
新しいデータの挿入、ヘッダの書き換え、などを
自動化するのが妥当だと、思いますが。
（ファイルは常に一つしかいらない、という前提ですが）
それならば、データをインポートすることは
ないと思います。Excelファイルのデータが初期とは変更
されるとか、そのログをとるためにインポートが
必要であるとか、インポートしないと載せられないデータ
がある、というようなことなら別ですが。
こういうことは、もしかしたらとっくに実行しておられる
かもしれませんね。

お礼

＜追記＞
ちなみに、Bは毎月業者から送られてくるファイルで、
Aは自社にて更新しているファイルです。

よって、エクスポートする内容としては、業者の情報に
自社の情報が付加されたものとなります。
※このエクスポートしたファイルが翌月のAになります。

Bは契約期間が記載されており、満了前後5ヵ月分が
記載されているものとなるので、毎月スライドしている
感じのものです。

なので、Aも併せてスライドさせる必要があります。
※スライドしないと、どんどん横長の表になってしまうので。

補足

前の質問に記載しておりますが、2つファイルをインポートして、
Bの全件に対してAを紐付けて（Aの情報を付加）して
Excelにエクスポートする必要があります。
で、Aの方に「ｘ月」があります。

なのでインポートする必要はあります。

回答No.5

piroin654

いろいろなオブジェクトの階層の中で、
名前の一意性が維持されなければ
プログラムが成り立たないことは
おわかりだと思いますので、説明は
必要ないとは思いますが、動的に
フィールド名が変更できるならば
いつかは必ず同じ名前のフィールド名が
出現できるようなことが可能になります。
一方が100というフィールド名で、一方が
50というフィールド名なら100のフィールドを
開くたびに1減らし、50のフィールドを
開くたびに1加えると・・・、ということです。

補足

フィールド名を変更する・・・という訳ではありません。
インポートしたテーブルのフィールド名は一切変更しません。

例えば、
インポートしたテーブルに2月　3月　4月　5月　とフィールド名があったら、
クエリに3月　4月　5月　のフィールドだけを採用したい・・・その為に、
クエリのデザイン画面のフィールドの箇所(※)で「今月-3ヵ月」「今月-2ヵ月」
「今月-1ヵ月」っていう指定が出来るのかなーと思った次第です。
※画像の青枠

回答No.4

piroin654

クエリのフィールド名はテーブルのフィールド名
と同じで、たとえばフィールド名を関数のDate
などで設定出来るとするならば開くたびにフィールド名が
変化します。そのために何が起こるか想像が
できると思いますが。フォームのレコードソース
などには設定できません。

ようするに、作成したクエリが、動的にフィールド名が
変更できないかということならば「NO」です。

前回の質問の回答でのコードをのせる必要があるのか
わかりませんが、フィールド名が動的に変更できない
となれば、クエリを作る必要があるのかないのか
わからないので、そのあたりはどうでしょう。
「予め」作る、ということはいずれは必要、ということですか。
あるいは、単にファイルの書き換えなのか、あるいは
エクスポート先がサーバーとか・・・・。
変更できなければ、現状でGOということですか。

補足

インポートしたファイルのテーブル名が固定で、かつ
フィールド名も固定であれば、予めクエリを作成しておく
ことができますよね。

テーブル名は固定だけどフィールド名が変わってしまう
ケースで、「予め」作っておくことが出来るのかな？と
思い質問させて頂きました。

＞たとえばフィールド名を関数のDate
＞などで設定出来るとするならば開くたびにフィールド名が
＞変化します。そのために何が起こるか想像が
＞できると思いますが。

これが想像できなかったので、フィールド名に「今月-1ヵ月」で
できるのかなーと思った次第です。

＞フィールド名が動的に変更できない
＞となれば、クエリを作る必要があるのかないのか
＞わからないので、そのあたりはどうでしょう。

今更ながら仰る通りですね。
確かにクエリ結果をExcelにエクスポートするだけであれば
クエリを作成しておく必要が無いですね。

今まで、2つのファイルをインポート⇒これらでクエリ作成⇒
エクスポート　とやってたので、クエリ作成に執着してしまいました。

勉強になりました。
ありがとうございました。

回答No.3

山田 太郎（@f_a_007）

補足：

３の処理においては、実際には

SELECT ・・・・　XXXXX, YYYYY, ZZZZZ　・・・・
FROM ・・・・ INNER JOIN ・・・・;

という雛形を用意して Replace() で置換した方がてっとり早いかもしれません。

PS、ADOXを利用したくない場合は、月時を指定してXXXXX YYYYY ZZZZZ を書き換えるという手もあります。

もちろん、信頼性は落ちますが・・・。

回答No.2

山田 太郎（@f_a_007）

＞テーブル構造が毎回違います。
＞ですから、実行すべきSQL文をその都度作成しています。

× SQL文中の列名を可変にしたい・・・。
〇 SQL文中の列名にテーブルの列名を反映したい・・・。

私なら・・・

１、テーブルAの列名を取得する。
２、SQL文の列名と照合する。
３、ミスマッチしていたら取得した列名と入れ替える。

ということをやります。

今、次のようなSQL文をテーブル構造とマッチするように書き換えることに挑んでいます。

＞フォームに配置した
＞１、テキストボックス[現SQL文]に現状のSQL文を表示したい。
＞２、テキストボックス[テーブルAの列リスト]に新しいテーブル情報を表示したい。
＞３、テキストボックス[新SQL文]に新しいSQL文を作成し表示したい。
＞できれば、コマンドボタン[エクスポート]でエクスポートも実行したい。

このようなことに挑んでいます。

と、もう少し質問の焦点を絞らえたがいいかも知れません。

PS、70歳も近くなって最新のやり方に疎いので・・・。

まあ、昔は　ADOX　を利用して作ったものです。

補足

ご回答ありがとうございます。

＞と、もう少し質問の焦点を絞らえたがいいかも知れません。

質問に書かせて頂いておりますが、単純に
　・毎回インポートするファイルの見出し（フィールド名）が変わる
　・このようなケースで予めクエリを作成しておくことはできるか
というだけなんです。

出来るのか出来ないのか、出来るのではあればどのような方法で？
ということをご教示頂ければと思い質問させて頂いた次第です。

・・・とはいっても、わたしとしては「クエリを作成」＝デザイン画面上で
作成という意味で書きましたが、その辺が分かり辛かったかもしれ
ません。すみません。

回答No.1

piroin654

前回の質問の回答で、

＞Excelへの書き込みはどのような方法でファイルのヘッダ
＞などを書き込んでいるのでしょうか。

としたのは、クエリのフィールド名をエクスポート
するときに、VBAであらかじめ新規のファイルを
作り、フィールド名をExcelファイルに設定するのですが、
そのときに、VBAコードの中で、クエリのフィールド名を

3ヶ月前　　format(dateadd("m",-3,date),"m")
2ヶ月前　　format(dateadd("m",-2,date),"m")
1ヶ月前　　format(dateadd("m",-1,date),"m")
今月　　　　format(date,"m")

のように設定します。たとえば、Excelの列で、A1から
F1=ID
F2=format(dateadd("m",-3,date),"m") & "月"
F3=format(dateadd("m",-2,date),"m") & "月"
F4=　　　　・
　　　　　　・
　　　　　　・
　　　　　　・
のように。
それから、クエリのコピー位置をA2からにして、
ヘッダを除いてエクスポートあるいは書き込み
というコードを書けばいいのでは、ということです。

これくらいなら、コードはかけますよね？それとも・・・・。

補足

エクスポートする際に、フィールド名を変更するってこと
だったんですね。失礼しました。

ということは、結論としてはこのようなケースの場合は
「予めクエリを作っておけない」ってことになるのでしょうか。

今回の質問の主旨といたしましては、
　・インポートするファイルの見出し（フィールド名）が毎回変わる
　・このようなケースで予めクエリを作成することはできるか
ということです。

確かに最終的にエクスポートする際に、フィールド名が変われば
それはそれでよいのですが、ローカルにクエリを作成するときに
（デザイン画面上で）フィールド名が変わったとしても何か方法が
あるのでは？ということで質問させて頂きました。

CreateQueryDefを使ってSQL文中のフィールド名の箇所を
変数にして目的のクエリを作成することは可能ですが、今回の
肝としてはクエリのデザイン画面上で・・ということでした。

質問の書き方が悪かったですね。すみません。