Access (VBA)

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

 
(Windows 7 Professional : Access 2010)
CopyFromRecordsetメソッドに失敗しました
投稿日時: 19/04/03 17:15:07
投稿者: 万葉の湯

処理内容は、
  1.計算式が書かれた提出用資料用6シートと、Accessデータを貼り付ける為の作業用8シートのExcelを開く
 2.Accessからクエリの結果をExcelの作業用シート全件貼り付け、レコード件数、
     提出用資料のシートの方の行数を増やす
 3.同じような処理を8回繰り返す
 4.Excelを再計算をさせ、最後に作業用の列やシートを削除し保存する
 
Accessは起動すると自動でプログラムが実行されるようになっており、
そのまま実行する分には正常終了します。(データも正しくExcelにはりつきます)
ただ、デバッグをしようとAccessのステップイン(FCoolで途中まで実行して残りを一気に実行した場合(F5)などに
「CopyFromRecordsetメソッドに失敗しました。:Rangeオブジェクト」と表示され処理が中断してしまう場合があります。低い確率で処理がうまくいくときもあります。
 中断したときは大体CopyFromRecordsetの処理が5回目以降のようです。
4回目までの書き方と同じなので、何がいけないのかがまったくわからず困っています。
(以下のコードは、CopyRecordsetを2回分のみ記載しました)
 
どなたかご教授いただけると大変助かります。
よろしくお願いいたします。
 
 
Function Out_toExcel(ByRef mdb As Database)
    Dim RS As Recordset
    Dim StrPath As String, FileNM As String
    Dim xlApp As Excel.Application
    Dim xlBook As Excel.Workbook
    Dim wk_ws As Excel.Worksheet
    Dim ws(1 To 6) As Excel.Worksheet
 
    StrPath = CurrentProject.Path
    FileNM = "\Template\実績集計.xlsx"
 
    Set xlApp = CreateObject("Excel.Application")
    Set xlBook = xlApp.Workbooks.Open(StrPath & FileNM)
    xlApp.Application.Visible = True
    xlApp.ScreenUpdating = True
    xlApp.Application.DisplayAlerts = False
 
    Set ws(1) = xlBook.Sheets("明細")
    Set ws(2) = xlBook.Sheets("店別明細")
    Set ws(3) = xlBook.Sheets("【当週】サマリ")
    Set ws(4) = xlBook.Sheets("【月次】サマリ")
    Set ws(5) = xlBook.Sheets("【当週】商品別")
    Set ws(6) = xlBook.Sheets("【月次】商品別")
 
    '<<新商品実績>>
    Set wk_ws = xlBook.Sheets("DB?D")
    Set RS = mdb.OpenRecordset("SELECT Temp_DB?D.* FROM Temp_DB?D")
    If RS.EOF Then
        ws(2).Range("34:36").Delete
        ws(3).Range("35:38").Delete
        ws(4).Range("35:38").Delete
        ws(5).Range("36:39").Delete
        ws(6).Range("36:39").Delete
    Else
        wk_ws.Range("A2").CopyFromRecordset RS '集計したデータを「DB?D」シートへ貼り付け
        Maxrow = wk_ws.Cells(wk_ws.Rows.Count, 6).End(xlUp).Row - 1 'レコード件数を取得
        Select Case Maxrow 'レコード件数分の行調整
            Case 1
                ws(2).Range("35:36").Delete
                ws(3).Range("37:38").Delete
                ws(4).Range("37:38").Delete
                ws(5).Range("38:39").Delete
                ws(6).Range("38:39").Delete
            Case 2
                ws(2).Range("36:36").Delete
                ws(3).Range("38:38").Delete
                ws(4).Range("38:38").Delete
                ws(5).Range("39:39").Delete
                ws(6).Range("39:39").Delete
            Case Is >= 4
                Call Insert_Row(xlApp, ws(2), Maxrow, 34)
                Call Insert_Row(xlApp, ws(3), Maxrow, 36)
                Call Insert_Row(xlApp, ws(4), Maxrow, 36)
                Call Insert_Row(xlApp, ws(5), Maxrow, 37)
                Call Insert_Row(xlApp, ws(6), Maxrow, 37)
        End Select
    End If
    Set wk_ws = Nothing: RS.Close: Set RS = Nothing
 
    Set wk_ws = xlBook.Sheets("DB?C")
    Set RS = mdb.OpenRecordset("SELECT Temp_DB?C.* FROM Temp_DB?C")
    wk_ws.Range("A2").CopyFromRecordset RS '集計したデータを「DB?C」シートへ貼り付け
    Maxrow = wk_ws.Cells(wk_ws.Rows.Count, 6).End(xlUp).Row - 1 'レコード件数を取得
    Select Case Maxrow 'レコード件数分の行調整
        Case 1
            ws(2).Range("32:33").Delete
            ws(3).Range("33:34").Delete
            ws(4).Range("33:34").Delete
            ws(5).Range("34:35").Delete
            ws(6).Range("34:35").Delete
        Case 2
            ws(2).Range("33:33").Delete
            ws(3).Range("34:34").Delete
            ws(4).Range("34:34").Delete
            ws(5).Range("35:35").Delete
            ws(6).Range("35:35").Delete
        Case Is >= 4
            Call Insert_Row(xlApp, ws(2), Maxrow, 31)
            Call Insert_Row(xlApp, ws(3), Maxrow, 32)
            Call Insert_Row(xlApp, ws(4), Maxrow, 32)
            Call Insert_Row(xlApp, ws(5), Maxrow, 33)
            Call Insert_Row(xlApp, ws(6), Maxrow, 33)
    End Select
    Set wk_ws = Nothing: RS.Close: Set RS = Nothing
 
    xlApp.Calculate    'Excel再計算
 
   '----- 値貼り付け/作業フィールドの削除 -------------------------    
   Dim i As Long
        For i = 1 To 9
        With xlBook.Sheets(i)
            .Activate
            .Cells.Copy
            .Cells.PasteSpecial xlPasteValues
            .Columns("A:L").Delete
        End With
    Next i
 
    '----- 作業用シートの削除 ------------------------------
    For i = 20 To 10 Step -1
        xlBook.Sheets(i).Delete
    Next i
    xlBook.SaveAs "C:\test\"test.xlsx"
    xlBook.Close
    xlApp.Quit: Set xlApp = Nothing
 
End Function

回答
投稿日時: 19/04/04 00:37:52
投稿者: mayu.

パッと見で気になった所だけ指摘しておきます。
 

引用:

    Set wk_ws = xlBook.Sheets("DB?D")
    Set RS = mdb.OpenRecordset("SELECT Temp_DB?D.* FROM Temp_DB?D")

スタートアップ実行では正常動作する ということですが
オブジェクト名に使われているクエスチョンマークのような記号は
EXCELのワークシート名、Accessのテーブル/クエリ名ともに命名規則に則っていないですし
エスケープするためのクォートも見当たりませんから
通常は、ワークシート変数のインスタンス生成・SQLの発行ともにエラーになります。
 
Accessのオブジェクト名に使える( 安全な )記号は
半角のアンダースコアのみですから、共にリネームしたほうがいいでしょう。
 
あと、SQL文中でアスタリスクを使用して
全フィールド参照をなさっていますが、実運用ではやめたほうがいいでしょう。
 
理由としては、結果セットにおける列の並びが 参照元オブジェクトとは異なる可能性がある上、
もし、Temp_DB?D 内に OLEオブジェクト型や添付型、複数値フィールドが含まれていたり、
たとえ今は含まれていなかったとしても
こういった類の列が追加された瞬間に
CopyFromRecordsetメソッドがエラーになってしまいますので。
 
引用:
Function Out_toExcel(ByRef mdb As Database)
    Dim RS As Recordset

名前空間のバッティングなど、予期せぬエラーの原因になりますから
参照設定されているオブジェクト変数は、タイプライブラリを付与して宣言しましょう。
 
Function Out_toExcel(ByRef mdb As DAO.Database)
    Dim RS As DAO.Recordset

投稿日時: 19/04/04 09:03:41
投稿者: 万葉の湯

mayu.様
 
ご返信ありがとうございます。
 
ご指摘の部分早速修正して試してみます。
本当に途方にくれていましたので修正箇所をご指摘いただき、明るい兆しが見えてきました。
取り急ぎお礼申し上げます。
 
また、ご指摘いただきました点についてご返信です。

引用:
オブジェクト名にオブジェクト名に使われているクエスチョンマークのような記号は

文字化けしたままで投稿してしまい申し訳ございません。
クエスチョンマークになってしまった部分ですが、クエリ名、シート名を「DB1」「DB2」という名称にしており、数字部分が記号の「まるいち」「まるに」を使用していました。
より安全にしたいので、リネームいたします。
 
引用:
SQL文中でアスタリスクを使用して

 
こちらについてですが、テーブルをそのままRecordsetにセットしても問題ない箇所なのですが、
テーブルのレコード順が意図したものにならないことが過去によくあったため、
recordsetのタイミングで「orderby」をしたいがためにSQL分を書いていました。
(すみません。投稿した内容がなるべく短くなるようにOrderby部分を省略していました)
Excelへ貼り付けてから、Excel側でソートをするのがセオリーでしょうか?
 
また、OLEオブジェクト型が含まれるとCopyFromRecordsetメソッドのエラーになるとのことですが
こちらの掲示板に投稿前に同様の原因が考えられるという記事を発見し調べてみたのですが
OLEオブジェクトとは図形やグラフなど・・・
とありましたので、Accessのテーブルの場合は関係ないのかと思っていました。
Accessのテーブル内でOLEオブジェクト型が含まれる場合はどのようなデータを指すのでしょうか?
勉強不足で申し訳ございませんが、こちらも合わせてご教授いただけると大変勉強になります。
 
 
引用:

名前空間のバッティングなど、予期せぬエラーの原因になりますから
参照設定されているオブジェクト変数は、タイプライブラリを付与して宣言しましょう。
  
Function Out_toExcel(ByRef mdb As DAO.Database)
     Dim RS As DAO.Recordset
 

 
こちらもずばりご指摘いただきありがとうございます。
物足りなさを感じつつ、過去にこのようなコーディングで動いていたので問題ないかと思っていました。
修正いたします。
 

回答
投稿日時: 19/04/04 10:57:15
投稿者: mayu.

引用:
テーブルのレコード順が意図したものにならないことが過去によくあったため、

テーブルに並び順の概念はありませんので、一度ご覧になるといいでしょう。
http://www.naboki.net/access/achell/achell-03.html
 
引用:
recordsetのタイミングで「orderby」をしたいがためにSQL文を書いていました。
Excelへ貼り付けてから、Excel側でソートをするのがセオリーでしょうか?

結局は、好みの問題になるのでしょうけど
後から Excel上で並び替えを実施するよりは
先にSQL文中でソートしておくほうが、処理の記述漏れは防げるように思います。
 
引用:
Accessのテーブルの場合は関係ないのかと思っていました。
Accessのテーブル内でOLEオブジェクト型が含まれる場合はどのようなデータを指すのでしょうか?

例えば、
管理物件1つにつき、1レコードとして登録されている不動産のデータベースで
管理番号などの数値、住所などの文字列、竣工日などの時系列のほか、
 
映像写真 や 間取り といった情報を格納する必要が生じた場合に
図面、Wordドキュメント、画像、PDFファイルなどを登録できる属性として
OLEオブジェクト型のカラムが存在している、といったところでしょうか。
 
また、OLEオブジェクト型は Long Binary型として定義されているため
DAO や ADO では、データの読み取りに GetChunkメソッドを用いなければならず、
CopyFromRecordsetメソッドの守備範囲外になります。

投稿日時: 19/04/04 13:28:26
投稿者: 万葉の湯

mayu.様
 
重ねてご返信ありがとうございます。
ご指摘いただいたところを修正しながらテストを重ねに重ねたところ、表題のエラーが出なくなりました!
 
<テスト内容>
 1.テーブルやシート名に記号を使用していたのものを修正 → 現象変わらず
 2.SQL文のアスタリスクを使用しないように変更 → 現象変わらず
 3.Recordsetの宣言を修正 → 現象変わらず
 4.Dim wk_ws As Excel.Worksheet で宣言していた「wk_ws」の使いまわしをやめる → 現象変わらず
 
Set wk_ws = xlBook.Sheets("DB1")
Set RS = mdb.OpenRecordset("SELECT Temp_DB?C.* FROM Temp_DB1")
wk_ws.Range("A2").CopyFromRecordset RS '集計したデータを「DB1」シートへ貼り付け
   ↓↓↓
Set RS = mdb.OpenRecordset("SELECT Temp_DB?C.* FROM Temp_DB1")
xlBook.Sheets("DB1").Range("A2").CopyFromRecordset RS '集計したデータを「DB1」シートへ貼り付け
 
 5.処理順序の変更 → エラー発生せず
 
  1クエリの結果をExcelに貼り付け → 2Excelの行調整 → 1,2の処理を8シート分繰り返す
    ↓↓↓
  8つのクエリの結果を8つのExcelシートへ貼り付け → Excelの行調整を8シート行う
 
正常終了するとAccessを閉じるようにしているのですが、
デバッグのステップインで実行すると時々なぜだかlaccdbが残り、
タスクマネージャで強制終了させないとAccessが開けないという症状が発生してまだ完成とはいきませんが
質問させていただいたCopyFromRecordsetについては、一切エラーが出なくなりました。
処理順序の問題だったのか、ちょっと不安ですが。
 
mayu.様にご教授いただいた内容が直接的に解決したという結果にはなりませんでしたが
思いつく限りのテストをして万策尽きたところへ mayu.様にご回答いただいたことで、
試せることをひらめくとてもいいきっかけとなりました。
本当に本当にありがとうございました。
 
 
先ほどご回答いただいた内容について追記させていただきます。
 

引用:
先にSQL文中でソートしておくほうが、処理の記述漏れは防げるように思います。

安心いたしました。このままAccess側のSQL文でソートしておく方法にします。
 
引用:
OLEオブジェクト型のカラムが存在している

今回、CopyFromRecordsetの対象となるテーブルをすべてデザイン画面で確認し
データ型がOLEオブジェクト型ではないことを確認していました。
(すべてテキスト型か数値型で文字数も制限にかかるほどではありませんでした)
確認方法として認識が誤ってるでしょうか?