アクセスUNIONクエリでの文字切れについて

質問

アクセス2010を使用しています。
UNIONクエリで結合した時に、文字数が255文字で切れてしまう回避方法をお教え願います。

リンクテーブルとして、エクセルファイル5個とリンクしています。
この５個のリンクテーブルをUNIONクエリで一つのデータにしました。
リンク元となるエクセルファイルの中に「内容」セルがあり、このセルの中には100文字から500文字まで入力してあります。
UNIONクエリで結合すると文字が255文字で切れてしまうため、これの回避方法をお教え願います。
リンクしてあるエクセルファイルはこの例で５個ですが、実際は20個ほどあり、一つのエクセルファイルに500件ほど入力されています。
「内容」の項目をメモ型で結合できれば回避できそうですが、UNIONクエリでデータ型を指定できるのでしょうか。
インターネットで調べましたが分かりませんでしたので、どうぞよろしくお願いします。

【このような感じで結合しています】
SELECT 氏名,年齢,内容 from T_01
UNION SELECT * from T_02
UNION SELECT * from T_03
UNION SELECT * from T_04
UNION SELECT * from T_05;

回答No.14

kkkkkm · ベストアンサー

すみません訂正です。
回答No.2で回答したリンクテーブルをもとにした更新クエリを利用
も255文字以上が最初のほうに無いと駄目でした。

お礼

今後ともよろしくお願いいたします

Powered by GRATICA

補足

kkkkkmさん、おはようございます。
いろいろとお教えいただきまして、ありがとうございます。
今回、このようにエクセルファイルをアクセスにリンクテーブルとして設定し、一つのデータにしたかった理由は次の通りです。
実際のエクセルファイルは、各月ごと一つのファイルになっています。業務上、各月ごとに分けておく必要があります。
ただ、これまで入力してきた過去のデータを検索するとき、エクセルファイルが各月ごとに分かれているため、一度で検索できません。
これを一度で検索するためにUnionクエリを使用して、各月ごとに分かれているエクセルデータを一つにまとめようとしたものです。
一つのデータにした結果、検索がとても楽になり業務が格段にはかどるようになりました。

今回は大変勉強になりました。
困ったときは、またお助けくださいますようお願いいたします。

回答No.16

yumi-access

No15 の補足
Access2010ではもしかしたらできないのかもしれませんが
Access2019ではできました。バージョン違いでの差異もあります。

しかし、そもそもエクセルシートのリンクテーブルはAccess側で参照しかできないため、本来はAccess側にデータを持たせる使い方をするのが一般的となります。

お礼

今後とも、よろしくお願いいたします。

Powered by GRATICA

補足

yumi-accessさん、いろいろとお教えいただきましてありがとうございます。
リンク元となるエクセルファイルの最初の行にダミーとして255文字以上のデータを入れて、アクセス側で当該フィールドをメモ型として認識させたいと思います。
困ったときは、またお教えくださいますようお願いいたします。

回答No.15

yumi-access

No6より
＞どのようにリンクの設定を見直せば、
＞文字切れを起こさず表示をさせることができますでしょうか。
こちらですが、リンクテーブルを生成する際に255文字以上のデータをいれておけば長いテキストになりましたので試してみて頂ければと思います。
再度リンクテーブル貼りなおせば「短いテキスト」→「長いテキスト」になると思われます。
よろしくお願いします。

回答No.13

kkkkkm

> 「アクセスVBAでやれば255文字以上の選択クエリも可能です。」とのことですが、VBAを使用してリンクテーブルを設定し、さらに各フィールドのデータ型を指定ができるのでしょうか。

リンクテーブルは以下の理由でやらない方がいいのかなと思います。
なおVBAでやるというのはリンクテーブルではなくインポートです。
仕様としてリンクを記載したマイクロソフトのページの「回避策」にインポートがあげられています。

リンクテーブルのデータ型を長いテキストにしたとしても
回答No.4で
UNIONクエリをもとにした選択クエリで集計して重複を回避
と回答しましたが

なんだか気になって文字数を調べたら255文字で切り取られてました

集計ではなくUNIONクエリをもとにした選択クエリ
でしたら255文字以上表示されました。

リンクテーブルをもとにした選択クエリ
では切り取られました。

リンクテーブルの場合、状況(クエリの種類とか)によって切り取られたり切り取られなかったりするのでUNIONクエリで終わるのでしたら問題は無いと思いますがそれ以降なにかしらの作業をするのでしたら

回答No.2で回答したリンクテーブルをもとにした更新クエリを利用して更新と追加で一時的なテーブルにデータを保存し以降そのデータをもとに作業をしたほうが無難かもしれません。 マクロに登録しておくと複数の更新クエリでもワンクリックで実行できます。

インポートをVBAでやる場合以下に参考になるコードがありますのでそれをもとに作成することになります。
ankleさんが手動でやろうとしていたダミーのデータを1行追加してという手法でリンクではなく既存のテーブルにインポートするという方法です。リンクもVBAでできると思いますが上に書いた理由でやらない方がいいのではと思います。

Excelインポート時に255文字を超える文字列が切れる問題
https://access-excel-vba.net/access_import_trouble/5/

回答No.12

kkkkkm

回答No.10の補足を見る前に回答No.11を投稿したので一部話が前後したみたいです。

リンクテーブルのデータ型がうまくセットされていなかったというのが原因みたいですね。
原因が判明してよかったです。

回答No.11

kkkkkm

仕様としては
リンクされた Excel スプレッドシート列のデータは、Access データベースで 255 文字に切り捨てられます
https://learn.microsoft.com/ja-jp/office/troubleshoot/access/linked-table-data-truncated

ですが、記載されているように表示上ということなので実際にデータはあります。

回答No.10で回答した手順で確認することはできます。
ただ、リンクテーブルのプロパティは保存できないのでその場での確認だけになります。
リンクテーブルをUNIONクエリでALLすると255文字以上表示されますが選択クエリだと255文字までしか表示されません(選択クエリで何か策があるかもしれませんが発見できませんでした)

ですので、今回の場合結合はエクセルVBAではなくUNIONクエリでやったほうがいいような感じです。もしくは、結合したブックのインポートを毎回手動でやるかエクセルVBAもしくはアクセスVBAでやれば255文字以上の選択クエリも可能です。

補足

kkkkkmさん、ご丁寧にお教えいただきありがとうございます。
今後もこのような形でエクセルへのリンクテーブルとして設定するかもしれないので、お聞きいたします。
「アクセスVBAでやれば255文字以上の選択クエリも可能です。」とのことですが、VBAを使用してリンクテーブルを設定し、さらに各フィールドのデータ型を指定ができるのでしょうか。
今後同様の症状が出た場合は、VBAで対応したいと思います。

回答No.10

kkkkkm

あと、リンクテーブルを直接開くと255文字しかデータが出てきませんが
リンクテーブルのデザインモードで
長いテキスト型(メモ型)のフィールドのプロパティの「書式」のところに「@」が入っていたら削除してください。そうすると元の文字数分でてきます。

補足

kkkkkmさん、何度もお教えいただきましてありがとうございます。
デザインモードで「内容」フィールドを見るとテキスト型になっており、これを当然ですがメモ型にデータ型を変更はできませんでした。
テキスト型の書式のところの「@」を削除してから表示しても、やっぱり切れていました。ちなみに、再度デザインモードで開くと「@」は復活している状況です。
調べるとリンクテーブルのデータ型は、リンク元となるエクセルファイルの最初の行の状態で判定しているとのことですので、面倒でもリンク元の各エクセルファイルの１行目にダミーの行を入れ、そして内容のセルに500文字ほど入れて、メモ型として認識させたいと思います。
ダミーとなる１行目の番号を「０」として管理し、Unionクエリで取り込む際に、「where 番号<>0」と付け加えて１行目を取り込まないようにしたいと思います。

回答No.9

kkkkkm

> エクセル側で改行コードを削除して試してみるといいかもしれません。

これでうまくいった場合、これから毎回手動で20個のブックで改行コード削除は面倒だと思いますからエクセルのVBAですませるようにすれば楽だと思います。

アクセスでの必要なデータが個々のブックのデータなのか結合したデータだけでいいのか分かりませんが、結合したデータだけでいいのでしたらデータの結合もエクセルVBAでやってしまって改行コードを削除して結合したブックをアクセスのリンクテーブルにしてしまえばアクセスでそのテーブルを開くだけでUNIONクエリを使うことなくそれ以降の作業が出来るようになります。

回答No.8

kkkkkm

> 内容のセルには改行があったり無かったりと、バラバラの入力状態です。
> たぶん、これが影響しているのかもしれません。

インポートしたらどうなるのだろうとか思いますが毎回インポートも面倒かも

エクセル側で改行コードを削除して試してみるといいかもしれません。

【Excel】改行コードを一括で置換・削除・追加する
https://moripro.net/excel-delete-newline/

ただし、UNION ALLにしないとUNIONでは255文字に切られてしまうと思います(2013では切られました)

ちなみに2013だと改行があっても切られないです
添付画像は左がエクセルのセル、右がアクセスのクエリ結果の値です。
エクセルの上2行の最後に改行があります。

回答No.7

kkkkkm

ん？
> T_00のダミーテーブルを作成して、内容フィールドをメモ型にしても切れてしまいました。

回答No.1の補足では

> ２行目からの「UNION ALL SELECT～」 は切れずにいけたようですが、１行目は「All」がないからなのか、切れてしまっているようです。

T_02以降は切れなかったとなってましたが切れてたのでしょうか。

どこが切れていてどこが切れていないのか状況がよく分かりません。

切れていたと思えるデータが元々255文字なくて切れてないデータは255文字以上あったとかのオチではないでしょうね。

補足

よく見てみると、T_01でも255文字以上の文字数がったり、T_02以降でも255文字で切れているいるものがありました。
こうなると、リンク元のエクセルの方に問題がありそうです。
内容のセルには改行があったり無かったりと、バラバラの入力状態です。
たぶん、これが影響しているのかもしれません。

回答No.6

yumi-access

UNIONクエリではなくUNIONクエリで使用されているテーブル(リンクテーブル)がテキスト255文字になっているのだと思います。リンクテーブルの設定を見直すことで可能なような気がしますが如何でしょうか？
T_01～T_05の各リンクテーブルを表示すると255文字になっていると思います。

補足

yumi-accessさん、ご回答ありがとうございます。
ご指摘のとおりリンクテーブルの中身を見ると、255文字で切れています。
どのようにリンクの設定を見直せば、文字切れを起こさず表示をさせることができますでしょうか。