[[20230505220833]]　『refreshallで実行時エラー70が出る』（SY）　ページの最後に飛ぶ

『refreshallで実行時エラー70が出る』（SY）

VBAマクロでmdbデータ(コピー元)を別のフォルダにコピーしてから(コピー先)、コピー先のデータを別のブックにクエリで取得して使用しています

1回目は上手くいきますが、2回連続で下記マクロを動かすと、コピー先のmdbファイルのハンドルが残っており、コピーができないという状況です。

どうやらrefresh.all時の処理が残っているのが原因のようですが、何か解決策があればご教授頂きたいです
よろしくお願いいたします。

Sub データ更新()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Dim msg As Integer
Set FSO = CreateObject("Scripting.FileSystemObject")

  FSO.CopyFile "\\コピー元.mdb", "\\コピー先.mdb"

ActiveWorkbook.RefreshAll

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

If ThisWorkbook.ReadOnly Then

MsgBox "読み取り専用です"

Else

ThisWorkbook.Save

End If

MsgBox "更新しました"

End Sub

＜使用 Excel：Excel2016、使用 OS：Windows10 ＞

クエリの内容がわからないので推測になりますがExcelのクエリはデフォルトだとバックグラウンド処理が有効なはずです。
そのため「更新しました」と表示されてもRefreshAllによるクエリの走らせ直しが未完了でそれが悪さしていないでしょうか。

2回連続といっても1回目と2回目の間を十分に開けたり、クエリのバックグラウンド処理を無効化したら改善しないでしょうか。
（高橋） 2023/05/06(土) 06:11:00

早速の返信ありがとうございます。
クエリはエクセルの外部データの取り込みから、Accessデータベースを取り込を選んで、mdbファイル内の顧客リストのデータを参照している状態です。
クエリという書き方が間違っていたらすみません

バックグラウンド処理に関しては"データ"タブから
接続→プロパティ→バックグラウンドで更新する
のチェックは外しています

時間を開けても直らず、一度ブックを閉じるか、それでも直らない場合はリソースモニタからハンドルを解除しないと再度更新ができません

コピー元のmdbファイルを直接参照せずに内部のリストを取得できれば目的としては十分なので、
上記手法以外でも手段があれば、ご教授頂けると大変助かります。
（SY） 2023/05/06(土) 06:58:20

 接続のプロパティでmodeを変更してもダメだったので、いっそVBAで直接データ取り込んで、クエリは使わないでしまったほうがいいんじゃないですかね？

 modeについて
https://accessvba.blog.ss-blog.jp/2011-12-27

 そうすれば、いちいちコピーしなくても直接読取に行けそうだし、仮に今まで通りの
    Sub データ取込()
        Dim ws As Worksheet
        Dim cn As Object, rs As Object
        Dim fn As String
        Dim sql As String
        Dim fld As Variant, i As Long
        Set cn = CreateObject("ADODB.Connection")
        Set rs = CreateObject("ADODB.RecordSet")
        '
        '//設定//
        fn = "C:\test\データベース.accdb"                     '<-取得したいmdbのフルパス
        Set ws = Sheets("Sheet1")                             '<-出力したいシート名
        sql = "Select * From tbl1;"                           '<-実際のテーブル名に変更してください｡
        '
        '//コネクションを確立して、レコードセットを開く
        With cn
            .connectionstring = _
                "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                "Data Source= " & fn & ";"
            .Open
        End With
        rs.Open sql, cn
        '
        '//シートのリセット
        ws.Cells.ClearContents
        '
        '//フィールド名を1行目に入力
        i = 0
        For Each fld In rs.Fields
            i = i + 1
            ws.Cells(1, i).Value = fld.Name
        Next
        '
        '//レコードの一括出力
        ws.Range("A2").CopyFromRecordset Data:=rs
        Set rs = Nothing
        Set cn = Nothing
        MsgBox "出力しました"
    End Sub

 今まで通りの運用するなら、Callしてしまってもいいかも。
    Sub データ更新()
        Application.ScreenUpdating = False
        Application.Calculation = xlCalculationManual
        Dim msg As Integer
        Set FSO = CreateObject("Scripting.FileSystemObject")
        FSO.CopyFile "\\コピー元.mdb", "\\コピー先.mdb"
        If ThisWorkbook.ReadOnly Then
            MsgBox "読み取り専用です"
        Else
            Call データ取込
            ThisWorkbook.Save
        End If
        MsgBox "更新しました"
    End Sub

（稲葉） 2023/05/08(月) 12:09:06

ありがとうございます。
おかげさまで何とかなりそうです

ちなみに、データを一度コピーして参照しているのは、
データを直接参照した際に、データが壊れることを防ぐためだと管理者から言われていますが、
そのようなことはあり得るのでしょうか？

この度は大変お世話になりました。
失礼いたしました。
（SY） 2023/05/09(火) 15:43:24

 昔はアクセスよく壊れましたよ。
 なので、参照するだけなら、
 　メインのデータを持っているアクセスファイルに、リンクテーブルで参照させた別のアクセスファイル作る
 ってことやった覚えがあります。
 （1部署レベルの話です）

 会社の基幹システムも、データベース自体は別の堅牢なデータベースサーバーに置いておいて、
 参照するときだけアクセスでクエリ走らせて必要なデータだけ持ってくるという使い方だったような・・・

（稲葉） 2023/05/09(火) 16:09:52

重ね重ねご返答ありがとうございます。
データに関しては急に何があるかわからないですね…
今後もコピーしてからの作業にしようと思います

上記マクロを使ったファイルは現状エラーなしで更新も早くなり、かなり扱いやすくなりました
ありがとうございました。

（SY） 2023/05/09(火) 17:44:16

 結局、マルチポスト先は最後まで放置でしたね。
（マルチネス） 2023/05/09(火) 17:50:41

コメント返信：

[ 一覧(最新更新順) ]

[[20230505220833]] 『refreshallで実行時エラー70が出る』（SY） ページの最後に飛ぶ

[[20230505220833]]　『refreshallで実行時エラー70が出る』（SY）　ページの最後に飛ぶ