Excel (VBA)

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

 
(Windows 10 Pro : Excel 2016)
ADOでレコードセットのオープン時に「メモリが不足」エラー
投稿日時: 18/02/28 19:05:33
投稿者: kakka

お世話になります。
投稿先をAccessの方にするべきか迷ったのですが、こちらへ投稿させていただきます。
 
下記のようなコードでaccdbファイルにADOで接続し、ワークブックとしてデータを取得するマクロを作成しました。
 

Option Explicit
Public Const AccessPath As String = "\\NAS\Shared Folder\sample.accdb"
'Public Const AccessPath As String = "C:\Users\user\sample.accdb"
Sub GetWorkbook_FromAccess()
Dim OutputWS As Worksheet, OutputWB As Workbook
Dim adoCn As Object 'ADOコネクションオブジェクト
Dim adoRs As Object 'ADOレコードセットオブジェクト
Dim bufSQL As String

bufSQL = "SELECT * FROM hoge WHERE Fail=True"

Set OutputWB = Workbooks.Add()
Set OutputWS = OutputWB.Worksheets(1)

Set adoCn = CreateObject("ADODB.Connection")
adoCn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & AccessPath & ";" 'Accessファイルに接続
Set adoRs = CreateObject("ADODB.Recordset")

adoRs.Open bufSQL, adoCn  'ここでエラー発生
OutputWS.Range("A1").CopyFromRecordset adoRs

adoRs.Close 'レコードセットのクローズ
adoCn.Close 'コネクションのクローズ

Set adoRs = Nothing
Set adoCn = Nothing  'オブジェクトの破棄

MsgBox "完了しました。"
End Sub

 
ローカル上では問題なく動いていたのですが、社内で共有するためaccdbファイルをNAS上へ置きパスを変更したところ、エラーが出るようになってしまいました。
エラー内容は「実行時エラー '2147467259 (80004005)':) メモリ不足です」という内容です。
PCはWindows10 64bit、Office2016 32bitですが同じ構成の別PCではエラーが発生せず正常に終了し、かと思えば違う構成(Windows7 64bit Office2013 32bit等)でエラーが発生したりしなかったりと発生条件がよくわからない状況です。
 
NASが原因かと思い、他のPC上へ共有フォルダを作成し、そこへaccdbファイルをコピーしても症状は変わりませんでした。
 
さらに不可解なのは、エラー発生時に「デバッグ」を押しVBEを表示させ、F5キーで続行すると問題なく実行できてしまうことです。ではとDoevents関数やSleep関数などをレコードセットのオープン前においても症状は変わらずです。
 
原因や解決策に心当たりのある方はご教授ください。
よろしくお願いいたします。[/code]

回答
投稿日時: 18/02/28 20:47:11
投稿者: WinArrow
投稿者のウェブサイトに移動

これだけの説明だけでは、皆目、見当もつきませんが、
 
取り敢えず、カーソルタイプ、ロックタイプを指定してみましょう。
ダメ元かもしれませんが・・・

投稿日時: 18/03/01 09:24:34
投稿者: kakka

> WinArrowさん
ありがとうございます。
ロックタイプとカーソルタイプは一通り試してみました。が、症状は変わらずでした・・・。

投稿日時: 18/03/01 09:54:24
投稿者: kakka

解決したかもしれません。
 
こちらのサイト
http://www.geocities.jp/cbc_vbnet/ADO/recordset.html
を参考にカーソルタイプとロックタイプを設定して試していたのですが
改めてマイクロソフトのページ
https://msdn.microsoft.com/ja-jp/library/cc389787.aspx
を確認したところ、カーソルタイプにadOpenUnspecifiedという値があることに気づき
これを試したところ動くようになりました。
 
いろいろ腑に落ちない感は残りますが・・・。
 

投稿日時: 18/03/01 11:17:24
投稿者: kakka

解決しませんでした。
 
カーソルタイプにadOpenUnspecifiedを設定して
引き続きテストしていたら同様の症状が発生するようになってしまいました。
 
設定した直後は何回かうまく動いたのですが。

回答
投稿日時: 18/03/02 09:43:57
投稿者: Suzu

レスつきませんね。。。
 
メモリ関係なので、何が問題なのか特定するのが難しいのは難しいです。
 
WEB検索をすると
【Access2010 の選択クエリを実行すると「メモリ不足です」というエラーが発生する】
https://answers.microsoft.com/ja-jp/msoffice/forum/msoffice_access-mso_winother-mso_2010/access2010/c3a25645-5c2c-4448-aeb6-9725a86f1fbe
 
なんてのは、出てきますが特定には至っていません。
 
【メモリ】と出ていますのでセオリーで言うなら
・マルチで他に実行しているアプリがあるなら閉じてみる
・再起動直後に実行してみる
・accdbファイルを、ローカルに戻し、各PCで実行してみる
 
  「ファイル」「ネットワーク」「ドライバ」何が問題なのか特定する」が第一段階でしょう。
 
有りがちなのは、先のリンク先でも出ていますが
余計な物がファイル内に残っている事があります。
(mdbファイルの時はそうでした。accdbになってどうなったかは判りません。)
mdbの頃は、最適化をしてもエラーになったり、動作が遅かったりする事があり
新規mdbファイルを作成し、そこに、一個づつオブジェクトをインポートし
不必要なゴミを取り除く事で快適に動作する事がありました。
 
特に、今回のaccdbがmdbから変換した物であるなら行ってみるべき事でしょう。
新規にて作成したaccdbであっても、やってみる価値はありますよね?
 
以下気になる部分を羅列します。
・SQL これがそのものズバリのSQLでは無いでしょう?
  面倒なSQLなのであればSQLを見直す。
・SQL SELECT * FROM〜 フィールド全選択が必要ですか?
  本当に必要なフィールドに限定してください。
・このSQLの当該レコード数はどのくらいになるのでしょうか。 少なくはできない?
・ファイルをmdbに変更し、DAOにて接続してみる。
 
 

引用:
不可解なのは、エラー発生時に「デバッグ」を押しVBEを表示させ、F5キーで続行すると問題なく実行できてしまうことです。

メモリ関係では、経験的によくありました。
 その時には。。そういうもんなんだと諦めてました。そうならない様、コードを組む様にしてました。

回答
投稿日時: 18/03/02 10:29:34
投稿者: WinArrow
投稿者のウェブサイトに移動

私が気になっている点は
>WHERE Fail=True
このフィールドは、YesNo型のデータタイプですよね・・・
私は、YesNo型のフィールドは使ったことはありませんが、
Jetエンジン(mdb)では、1ビットのフィールドサイズでしたが
Aceエンジン(accdb)では、1バイトのフィールドサイズに変わったらしいです。
このあたりは、大丈夫なんでしょうか?
または、余計なごみ???が入っていないでしょうか?

投稿日時: 18/03/02 11:37:31
投稿者: kakka

Suzu さん WinArrow さん
 
返信ありがとうございます。
 
メモリが8GBのPCで発生して4GBのPCで発生しない、なんて事もありましたので
物理的に本当にメモリが足りないとは考えにくいとは思いつつ、
他のアプリを閉じる、再起動直後、などは試してみましたが改善しませんでした。
 
https://blogs.technet.microsoft.com/officesupportjp/2017/02/16/ace_memory_error/
このようなページも見つけて、そもそもAccessがインストールされていないPCでも症状が発生するので違うかとは思いつつダメ元で試しましたが改善されずでした。
 
 
>SQLについて
おっしゃるように、サンプルのコードは投稿するにあたって簡素化したものですが
SQL文の長短やフィールドのデータ型、テーブルのレコード数の多少、検索結果の多少にかかわらず発生します。まったく同じSQLでもPCによって発生したりしなかったり、です。
レコード数250000、フィールド数30の中からから100レコード10フィールドを取得するのも、レコード数もフィールド数も一桁のようなテーブルから一つのレコードを取得するのも同じ症状です。
 
 
今回のaccdbファイルは新規に作成したものですが、ご提案頂いたように
新しく作成しなおす、mdbで作成してDAOで接続する、あたりを試してみようと思います。

回答
投稿日時: 18/03/02 13:20:12
投稿者: Suzu

引用:
今回のaccdbファイルは新規に作成したものですが、ご提案頂いたように
新しく作成しなおす、mdbで作成してDAOで接続する、あたりを試してみようと思います。

 
その前に、ドライバ や プロバイダを変えてみましょう。
 
https://qiita.com/yaju/items/86314412741deb806366
 
ドライバは、
【Microsoft Access データベース エンジン 2010 再頒布可能コンポーネント】
https://www.microsoft.com/ja-jp/download/details.aspx?id=13255
 
【Microsoft Access 2016 Runtime】
https://www.microsoft.com/ja-jp/download/details.aspx?id=50040
 
をインストールすれば、使えるハズです。
すみません。そこまでは試せていないです。
 
 
2016RunTimeは 64bit OS、 32bit Officeの場合
AccessDatabaseEngine.exe / quiet オプションが必要みたいです。
 
それでも問題が続く様であれば、私なら、DAO を使います。

回答
投稿日時: 18/03/02 13:24:29
投稿者: Suzu

プロバイダを 変えるのは、ACE.OLEDB.12.0 の問題がどこまで関わっているのかが判らないからです。
 
なので、プロバイダに、
Microsoft.ACE.OLEDB.15.0
Microsoft.ACE.OLEDB.16.0
を使う為に、全部のPCに新しいドライバをインストールしておくために
再頒布可能コンポーネント や RunTime を インストールする という事です。

投稿日時: 18/03/02 14:52:08
投稿者: kakka

Suzu さん
 
ありがとうございます。プロバイダとドライバの変更、試してみます。

回答
投稿日時: 18/03/02 20:47:27
投稿者: ニャッチュ

15年ほど昔の話なので記憶が曖昧なところもありますが似たような感じの経験がります。
 
Access2000ADP型式でVBAからADOでグリグリ作り込んでた時です。
たしか同じくテーブルのOPEN時だったと思います。
接続先はSQLサーバかNASか(どっちもあった案件だった)忘れましたけど、
普通にVBAコードを流して実行するとエラー、
その手前あたりの行からステップ実行するとそのエラー地点は通過するいう現象です。
 
結局原因は不明だったのですが、それを回避した方法は
Connectionのオブジェクト変数を閉じないで、ずっと開きっぱなしにする
という方法でした。
 
具体的には、変数を
Public Dim adoCn As Object
として、ADPファイルを開いた時のしょっぱなにその変数にコネクションを代入し、以降はその変数をそのまま使いまわすという方法です。
 
今回の質問の状況でしたら、adoCnの値がNothingのときだけコネクションを取得するという感じになると思います。
そして、コード後方でのクローズ処理はしません。
 
以上、環境が異なっているので的が外れてるかもしれませんが、何かヒントになればと思い投稿しました。
(たぶんどっか設定をミスってたんだろうなぁと想像できます;汗)

回答
投稿日時: 18/03/04 09:31:54
投稿者: MMYS

すでに下記の情報は実施済みですか。
 
Access でメモリ不足エラーが発生する現象の対処策について
https://blogs.technet.microsoft.com/officesupportjp/2017/02/16/ace_memory_error/
 
プログラム起動時のシステムリソース不足を解消する
http://trendy.nikkeibp.co.jp/article/tech/20070809/1002010/?rt=nocnt
 
 
ここで言うメモリ不足は、DB Engineに割り当てられたメモリです。
上限ギリギリで動作している場合、「メモリ不足の時だけ」問題が表面化します。
 
 
本番環境では同時アクセスの可能性はありませんか。
「デバッグ」画面で、F5キー続行で正常動作は、、同時アクセスが解除されたから、かもしれません。
ユーザー操作待ちになったことで、メモリ開放がされた可能性もあります。
 
また、SQLの書き方でメモリ使用量が変わると思います。
私ならSQLやDB構造を見直します。
 
なお、
Accsessはスタンドアロン用に設計されています。
同時アクセスが頻繁に発生する場合、おすすめしません。
 
あと、例外処理は入ってますよね。
エラー時は再試行って
 

投稿日時: 18/03/05 09:40:21
投稿者: kakka

> ニャッチュさん
情報ありがとうございます。
現状では、Suzuさんにご提案頂いたプロバイダの変更で上手く動きそうに感じているのですが、Connectionオブジェクトを閉じない方法も試してみようと思います。
 
 
> MMYSさん
ありがとうございます。Accessのメモリ不足についての記事は既に試していたのですが、デスクトップヒートについては初耳な用語でした。試してみます。
本番環境での同時アクセスは可能性がありますが、私がテストして症状が発生していた段階では同時接続は行われていません。
 
SQLの書き方でメモリ使用量が変わるというのは、データの取得量が同じでもSQL文の違いでメモリ使用量が異なる、という事でしょうか?それともSELECT文で不要なフィールドを選択しない、という事でしょうか?
 
例外処理はデバッグ作業のためにいまは外していますが、実際に使い始める時には入れる予定です。

投稿日時: 18/03/06 10:11:07
投稿者: kakka

ひとまずエラーは発生しなくなったのですが、ちょっとすっきりしない感じです。
Suzuさんにご提案頂いたプロバイダの変更を試しました。
 
Provider=Microsoft.ACE.OLEDB.12.0

Provider=Microsoft.ACE.OLEDB.16.0
 
この変更でエラーは発生しなくなったのですが、12.0に戻してもエラーが発生しなくなりました。
一度16.0を使用したことによって何かしらの設定などが変更された結果、
12.0を指定してもエラーが発生しなくなる、みたいな想像をしたのですが
そんな事は起こりえるのでしょうか?

回答
投稿日時: 18/03/06 10:52:06
投稿者: Suzu

引用:
SQLの書き方でメモリ使用量が変わるというのは、データの取得量が同じでもSQL文の違いでメモリ使用量が異なる、という事でしょうか?

 
データの取得量。。
どこのマシンに於いてのデータの取得でしょうか。
 
Accessは簡易データベース + GUI 作成ツール + レポート のアプリケーションです。
データベースのクエリの部分ですが、Access単体では
   全データを取得 → クエリのSQLに沿って 必要なレコードを取捨選択 → 画面表示
  となります。
ネットワーク経由でも基本同じです。
 
なので、mdb/accdb をNAS等で共有してても、
必要なテーブルの全データを取得し、クライアント側で抽出し表示します。
なのでAccessをネットワーク経由で開いた場合、取得している量は基本変わりません。
 
あくまでもファイル共有の延長ですから、同時接続やら、HDDの速度やネットワーク速度に大きく左右されます。
 
本格的なデータベースでは、
サーバー側に、SQLを渡し、そのSQLに沿って必要なデータのみ をクライアントに返します。
 
なので、ネットワークの環境には影響を受けにくいし、同時接続もどんと来いとなります。
 
 
例えば ある名簿から、携帯電話番号の判っている 20代 の方のデータを抽出します。
 
・20代 → 携帯電話番号
・携帯電話番号 → 20代
 
どっちが効率的でしょう。
 
後者ですよね。逆に言うと、「20代」と言う条件要らないです。
 
得られるデータは同じでも、条件を間違えると余計な計算をしている訳です。
 
どの様な順番で処理するのかをクエリプランと言います。興味があれば検索してみてください。
 
 
 
引用:
それともSELECT文で不要なフィールドを選択しない、という事でしょうか?

それは当然の事です。

回答
投稿日時: 18/03/08 06:54:41
投稿者: i-brown

kakka さんの引用:
お世話になります。
ローカル上では問題なく動いていたのですが、社内で共有するためaccdbファイルをNAS上へ置きパスを変更したところ、エラーが出るようになってしまいました。
エラー内容は「実行時エラー '2147467259 (80004005)':) メモリ不足です」という内容です。
PCはWindows10 64bit、Office2016 32bitですが同じ構成の別PCではエラーが発生せず正常に終了し、かと思えば違う構成(Windows7 64bit Office2013 32bit等)でエラーが発生したりしなかったりと発生条件がよくわからない状況です。
 
NASが原因かと思い、他のPC上へ共有フォルダを作成し、そこへaccdbファイルをコピーしても症状は変わりませんでした。
 
さらに不可解なのは、エラー発生時に「デバッグ」を押しVBEを表示させ、F5キーで続行すると問題なく実行できてしまうことです。ではとDoevents関数やSleep関数などをレコードセットのオープン前においても症状は変わらずです。
 
原因や解決策に心当たりのある方はご教授ください。
よろしくお願いいたします。

 
1. .accdbファイルの容量は何GBですか?
2. タスクマネージャーでExcelのメモリ使用量を確認しましたか?(32bitアプリなら2GBに制限されます)
  Ctrl + Shift + Escを押し、下の「詳細」ボタンを押すと、アプリケーション毎のメモリ使用量が出ます。
 
余計なお世話で済みませんが、参照設定と最低限の例外処理(コネクションクローズのみ)を追加してコードを書き換えてみました。
' VBEのツール->参照設定で Microsoft ActiveX Data Objects 6.1 Library にチェック追加
Option Explicit
#Const Network = True   ' コンパイル時の挙動を切り替える宣言。Networkの所は好きな名前を付けてよい。

' 上の#Constに従ってコンパイル時に解釈される。
#If Network Then
    Public Const AccessPath As String = "\\NAS\Shared Folder\sample.accdb"
#Else
    Public Const AccessPath As String = "C:\Users\user\sample.accdb"
#End If

Sub GetWorkbook_FromAccess()
    Dim OutputWS As Worksheet
    Set OutputWB = Workbooks.Add()
    
    Dim OutputWB As Workbook
    Set OutputWS = OutputWB.Worksheets(1)
    
    Dim bufSQL As String
    bufSQL = "SELECT * FROM hoge WHERE Fail=True"
    
    On Error GoTo Err   ' 例外発生時にコネクションを閉じさせる
    Dim adoCn As New ADODB.Connection
    adoCn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & AccessPath & ";" 
    
    Dim adoRs As New ADODB.Recordset
    adoRs.Open bufSQL, adoCn  'ここでエラー発生
    
    OutputWS.Range("A1").CopyFromRecordset adoRs
    
    adoRs.Close 'レコードセットのクローズ
    
Err:
    adoCn.Close 'コネクションのクローズ
       
    MsgBox "完了しました。"
End Sub

投稿日時: 18/03/09 15:22:39
投稿者: kakka

> i-brown さん
 
accdbファイルはおよそ500MBです。エクセルのメモリ使用量も見たところ200MB以下でした。
 
 
今回はプロバイダを16.0に変更することでエラーは発生しなくなりましたので
ひとまずこれで解決とさせていただきます。
(12.0に戻してもエラーが出なくなってしまったのが腑に落ちませんが・・・)
 
色々と教えてくださった皆様ありがとうございました。
Accessのことについてあまり詳しくなかったのでとても勉強になりました。
また何かありましたらよろしくお願いいたします。