EXCEL VBAを使ってAccessからデータを取得したい。

Question

コード的な質問というよりは、方法についての質問です。

まず、実現したいことですが、以下のようなものを想定しています。

以前質問：startとgoalのAセルの日付データを比較し、goalにないデータを start から行ごとコピペするプログラムを考えています。
=>startからAccessDBに変更。つまりstartとAccessDBのAセルの日付データを比較し、AccessDBにないデータを start から行ごとコピペするプログラムを考えています。そのためAccessからデータを取得する必要があります。

書籍を見ていると、比較するためのデータ取得のために、Accessの日付データを取得する方法とSQLによりデータを出力する方法があるかと思うのですが、どちらが良いのでしょうか？どちらでも取得できるデータは同じですか？一般的にはどちらで記載されることが多いでしょうか？よろしくお願いいたします。

こちら以前質問した続きです。
前回質問URLです。
質問１：https://teratail.com/questions/8x4hz58p7q8s21
質問２：https://teratail.com/questions/6w0221ourt8sda

したいことイメージ

元データ（start）

日付	数値データ	数値データ
11/26	1234	4567
11/27	1234	4567
11/28	1234	4567

コピペ先データ（AccessDB）

日付	数値データ	数値データ
11/26	1234	4567
11/27	1234	4567

コピペ先データ（コピペ後）

日付	数値データ	数値データ
11/26	1234	4567
11/27	1234	4567
11/28	1234	4567

以下、コード参照

Sub compare_and_copy_date()

    Dim L As Long, lRow As Long, M As Long, mRow As Long
    Dim wsStart As Worksheet: Set wsStart = ThisWorkbook.Sheets("start")
    Dim wsGoal As Worksheet: Set wsGoal = ThisWorkbook.Sheets("goal")
    Dim myConn As New ADODB.Connection
    
    myConn.Open ConnectionString:= _
        "Provider=Microsoft.ACE.OLEDB.12.0;" & _
        "Data Source=C:\Users\test.accdb"
        myRS.Open Source:="test", ActiveConnection:=myConn, _
            CursorType:=adOpenStatic
            
    '行すべてを表示
    wsStart.Rows.Hidden = False
    '行の最終行を取得
    lRow = wsStart.Cells(Rows.Count, "A").End(xlUp).Row
    mRow = wsGoal.Cells(Rows.Count, "A").End(xlUp).Row

    '日付に一致しないデータをコピペ（現状はエクセル別シートだがアクセスのテーブルに変更）
    For L = 2 To lRow
        For M = 2 To mRow
            If wsStart.Cells(L, 1).Value = wsGoal.Cells(M, 1).Value Then
                Exit For
            End If
        Next M
        If M > mRow Then
            wsStart.Rows(L).Copy Destination:=wsGoal.Cells(Rows.Count, "A").End(xlUp).Offset(1)
        End If
    Next L
End Sub

Comments

[hatena19]

Accessファイル内のテーブルにデータを追加するということですよね。
ならば、Access側で実行した方がはるかに楽ですが、それではだめですか。

[Kazuhiro-ch]

ご連絡ありがとうございます。

基本的にVBAでは追加される側にマクロを組んだ方が楽なのでしょうか？

[hatena19]

Accessの方が外部データとの連携機能が豊富ということと、SQLが簡単に使えるという点で楽ですね。

Answer 1

エクセルでするなら下記のようなコードでしょうか。

Sub compare_and_copy_date()

    Dim L As Long, lRow As Long, M As Long, mRow As Long
    Dim wsStart As Worksheet: Set wsStart = ThisWorkbook.Sheets("start")
    Dim myConn As New ADODB.Connection
    myConn.Open ConnectionString:= _
                "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                "Data Source=C:\Users\test.accdb"
    Dim myRS As ADODB.Recordset
    myRS.Open Source:="test", ActiveConnection:=myConn, _
              CursorType:=adOpenDynamic
            
    '行すべてを表示
    wsStart.Rows.Hidden = False
    '行の最終行を取得
    lRow = wsStart.Cells(Rows.Count, "A").End(xlUp).Row

     With myRS
        For L = 2 To lRow
            .MoveFirst
            .Find "日付=#" & Format(wsStart.Cells(L, 1).Value, "yyyy/mm/dd") & "#", 0, adSearchForward
            If .EOF Then '一致する日付がなければ、行データを追加
                .AddNew
                !日付.Value = wsStart.Cells(L, 1)
                !数値1.Value = wsStart.Cells(L, 2)
                !数値2.Value = wsStart.Cells(L, 3)
                .Update
            End If
        Next L
    End With
    myRS.Close: Set myRS = Nothing
    myConn.Close: Set myConn = Nothing

End Sub

Access側でするなら、
テーブルの日付フィールドを主キーにするか、インデックスを設定しておきます。
そのうえで、このテーブルにエクセルのstartシートをインポートするだけで、コード無しに希望のことが実現できます。
主キーまたはインデックスが設定してあると、重複データは自動ではじいて取り込んでくれます。

Comments

[Kazuhiro-ch]

ありがとうございます。一点質問なのですが、 .Find "日付=#" & Format(wsStart.Cells(L, 1).Value, "yyyy/mm/dd")の部分がアプリケーションの定義及びオブジェクト定義エラーになります。おそらくFormatのValueがおかしいのではと考えておりますが、いかがでしょうか？

また日付=#がどういう意味なのかもあわせて教えていただけると幸いです。

[hatena19]

startシートの１列目(日付)のセルの書式はどうなってますか。

.Find "日付=#" & Format(wsStart.Cells(L, 1).Value, "yyyy/mm/dd")
の前に下記のコードを挿入して実行したときに、
Debug.Print wsStart.Cells(L, 1).Value
イミディエイトウィンドウにどのような値が出力されますか。

[Kazuhiro-ch]

2022/11/26という最新の日付が出力されました。

[hatena19]

Debug.Print Format(wsStart.Cells(L, 1).Value, "yyyy/mm/dd")
としたときはどうなりますか。

あと、Accessのtestテーブルにには、「日付」という名前のフィールドはありますか。また、そのフィールドの名前は日付/時刻型ですか。

[Kazuhiro-ch]

同じく最新の日付が出力されます。

また日付：日付／時刻型というフィールドが存在します。

[hatena19]

いままでの中では問題点は見当たらないですね。
実物を見ることができないこちら側から原因を推測するのはてかなか難しいです。
ADOでレコードセットを開いてFindで検索して見つからなかったらAddNewで追加するというロジックでできるはずですので、ADOについての解説ページを研究していろいろトライしてください。

うまく行かないようなら、Access側でインポートするという方法も検討してみてください。

[Kazuhiro-ch]

承知しました。ひとまずそういたします。