Microsoft Access Club Access超初心者対象Forum Access初級者対象Forum Access初・中級者対象Forum Access VBA Tips Forum DAO、ADO、SQL Forum

     

リストへもどる

投稿記事の一括表示

タイトルSQLのUNIONで予めデータ型を指定する方法 OR 下記症状を改善する方法をお教えいただけませんか?
記事No16856
投稿日: 2017/04/01(Sat) 21:48
投稿者Cosmos_18
解決済: ON
OS:Windows 10
Access Version: Access 2016


AccessのSQLビューで、下記のようなSQL文を記述すると、いつもすんなり複数テーブルが単一の新しいテーブルが生成されます。UNIONクエリです。

SELECT * INTO T_UNION
FROM (SELECT T_1.*
FROM T_1
UNION ALL
SELECT T_2.*
FROM T_2
UNION ALL
SELECT T_3.*
FROM T_3
UNION ALL
SELECT T_4.*
FROM T_4) AS T;

けれども、問題はやはり「データ型」です。
このSQL文ではデータ型を指定していないので、Access側から勝手に適当なデータ型を割り振ってきます。
かなり大きいサイズのテーブルなので、デザインビューでデータ型の定義を指定し直そうとすると、次のような警告メッセージが返ってきます。

「データ型を変換できません。ディスクの空き領域またはメモリが不足しています」

今まですべて上記のSQL文でうまくいっていたのですが、なぜかある時点から、このような警告メッセージがでてくるようになりました。ちゃんと新しいテーブルがみごとにできるので、SQL文自体にエラーがあるとは思えません。

「ディスクの空き領域」を増やしたり、「メモリ不足」を解消するためにタスクマネージャーを起動して最大限にプロセスやサービスなどを停止させて、メモリ消費量を半減以下に落として見たりしても、やはり同じ警告メッセージが返ってきます。

この件に対する対処方法を具体的にご教授いただければ幸いです。

1. ディスクの空き領域またはメモリをもっともっと激減させる。
2. UNIONするテーブルの数を減らす。
3. レコードを減らす。
4. SQL文自体であらかじめデータ型を指定する。

1.2.3.はもうできなくなったので、あとは4か、その他の方法などございましたらご教授いただけませんか
何かヒントや、ご体験談のようなものでも結構です。

何卒よろしくお願い申し上げます。

タイトルRe: SQLのUNIONで予めデータ型を指定する方法 OR 下記症状を改善する方法をお教えいただけませんか?
記事No16858
投稿日: 2017/04/03(Mon) 02:12
投稿者hatena
解決済: ON
事前に希望のデータ型のフィールドを持つテーブルを作成しておく。
そこに追加クエリで追加する。

というのでは駄目ですか。


INSERT INTO T_UNION
SELECT * FROM
(SELECT T_1.*
FROM T_1
UNION ALL
SELECT T_2.*
FROM T_2
UNION ALL
SELECT T_3.*
FROM T_3
UNION ALL
SELECT T_4.*
FROM T_4) AS T;


テーブルは一度作成しておけば、
削除クエリでデータを削除 → 追加クエリを実行する
という処理で使いまわしもできます。

タイトルRe^2: SQLのUNIONで予めデータ型を指定する方法 OR 下記症状を改善する方法をお教えいただけませんか?
記事No16859
投稿日: 2017/04/03(Mon) 16:59
投稿者Cosmos_18
解決済: ON
hatenaさん、みなさま、ありがとうございます。


> 事前に希望のデータ型のフィールドを持つテーブルを作成しておく。
> そこに追加クエリで追加する。
>
> というのでは駄目ですか。
>
>
> INSERT INTO T_UNION
> SELECT * FROM
> (SELECT T_1.*
> FROM T_1
> UNION ALL
> SELECT T_2.*
> FROM T_2
> UNION ALL
> SELECT T_3.*
> FROM T_3
> UNION ALL
> SELECT T_4.*
> FROM T_4) AS T;
>
>
> テーブルは一度作成しておけば、
> 削除クエリでデータを削除 → 追加クエリを実行する
> という処理で使いまわしもできます。

ありがとうございます!
この方法を試してみましたが、エラーが帰って来ました。
でも、確かに、これも一つの解決策なので、またきちんと再挑戦してみましょう。

さて、この問題をSQL文だけで解こうとするのではなく、Access VBAと組み合わせてやってみたら、なんと次のようなVBAを記述・実行すると、少なくとも1回は解決できました。

-------------------------------------------------------------------------

Sub LargeUpdate()
On Error GoTo LargeUpdate_Error
Dim db As DAO.Database, ws As DAO.Workspace

'SetOption メソッドを使用して MaxLocksPerFile 値を一時的に変更する。
' Set MaxLocksPerFile.
'トランザクション内で更新処理を実行する前に MaxLocksPerFile の値を 200,000 に設定しています。

DBEngine.SetOption dbMaxLocksPerFile, 200000

Set db = CurrentDb
Set ws = Workspaces(0)

' Perform the update.
ws.BeginTrans
db.Execute "UPDATE T_UNION SET Field2 = 'History'", _
dbFailOnError

ws.CommitTrans

db.Close
MsgBox "Done!"
Exit Sub

LargeUpdate_Error:
MsgBox Err & " " & Error
ws.Rollback
MsgBox "Operation Failed - Update Canceled"
End Sub

------------------------------------------------------------------------

Microsoft公式サイト
[ACC2003] Access テーブルに対する処理の実行中にエラー メッセージ "ディスクの空き領域またはメモリが不足しています" が表示される
https://support.microsoft.com/ja-jp/help/286153/you-may-receive-a-there-isn-t-enough-disk-space-or-memory-error-message-when-you-perform-an-operation-on-an-access-table

解決方法
警告 : レジストリ エディタの使い方を誤ると、深刻な問題が発生することがあります。最悪の場合、オペレーティング システムの再インストールが必要になることがあります。マイクロソフトは、レジストリ エディタの誤用により発生した問題に関しては、一切責任を負わないものとします。レジストリ エディタは、自己の責任においてご使用ください。
この問題を回避するには次のようないくつかの方法があります。
Regedit.exe を使用してレジストリを編集し、MaxLocksPerFile の値を永続的に変更できます。
DBEngine オブジェクトの SetOption メソッドを使用して、コード内で MaxLocksPerFile の値を一時的に変更できます。

つまり、レジストリを手動で直接変更すると危険なので、このVBAプログラムで「DBEngine オブジェクトの SetOption メソッドを使用して、コード内で MaxLocksPerFile の値を一時的に変更」するという方法を採用すればいいというのです。さすがはマイクロソフトAccess公式サイトのソリューションですね。

こうなってくると、もはやSQLやAccessのクエリGUI操作という問題ではなく、「Windowsの仕様」「Accessの仕様」と「これらの仕様を一時的に変更するVBAプログラムを実行する」という問題になってくるように思います。
具体的には、レジストリの MaxLocksPerFile の値をデフォルト値「9500」から「200000」へと大幅に拡大するというVBAプログラミングになります。そのソースコードが上記のコードです。

このVBAプログラミングのおかげで、少なくとも1回は2個のテーブルの結合だけだったということもあってか、みごとに解決しました。
けれども、さらに5個以上の複数テーブルを結合しようとすると、また同じようなエラーが出て、失敗してしまいました。

1. DBEngine.SetOption dbMaxLocksPerFile, 200000 → DBEngine.SetOption dbMaxLocksPerFile, 8000000
2. SQL UNIONで結合するテーブル数を減らす。
3. メモリを物理的に増設する。
4. VBAプログラミングをもう少し工夫する。
5. その他

ここまで、問題の原因が明確になり、問題の解決方法の一つとして「レジストリの MaxLocksPerFile の値を変更する」VBAコードまであるわけですから、問題の解決へはあとちょっと、ひと工夫だけだと思うのですが……。

引き続きご教示をよろしくお願いいたします。

タイトルRe^3: SQLのUNIONで予めデータ型を指定する方法 OR 下記症状を改善する方法をお教えいただけませんか?
記事No16860
投稿日: 2017/04/03(Mon) 17:42
投稿者hatena
解決済: ON
> このVBAプログラミングのおかげで、少なくとも1回は2個のテーブルの結合だけだったということもあってか、みごとに解決しました。
> けれども、さらに5個以上の複数テーブルを結合しようとすると、また同じようなエラーが出て、失敗してしまいました。

原因が、複数テーブルを結合することによって一度に処理するレコード件数が多くなるということが分かっているので、
ユニオンクエリで結合して一度に処理するのではなく、
一つのテーブルのみの追加クエリをテーブル毎に実行すればいいのでは。


下記は、T_1 から T_5 までは追加する場合のコード例です。

For i = 1 to 5
    db.Execute "INSERT INTO T_UNION SELECT * FROM T_" & i, dbFailOnError
Next

タイトルRe^4: SQLのUNIONで予めデータ型を指定する方法 OR 下記症状を改善する方法をお教えいただけませんか?
記事No16861
投稿日: 2017/04/03(Mon) 21:50
投稿者Cosmos_18
解決済: ON
hatenaさん、ありがとうございます!

> For i = 1 to 5
> db.Execute "INSERT INTO T_UNION SELECT * FROM T_" & i, dbFailOnError
> Next

これで、問題解決しました。
また、よろしくお願い申し上げます。

めでたく解決したので、これをもって、少なくともこの件に関してはこれで締め切らせていただきます。
本当にありがとうございました!

- 以下のフォームから自分の投稿記事を修正・削除することができます -
処理 記事No パスワード

ページの先頭へ 前ページへ戻る