Excel VBAよりAccessのエクスポート機能（DoCmd.TransferSpreadsheet)を使用したい

前提・実現したいこと

現在Excel VBA⇔Accessを連携させているマクロブック(xlsm)があります。
その中で、ある処理の前に前回処理実行日から10日以上たっている場合
Accessのテーブルのバックアップを取りたく
（Access内ではなく、任意のフォルダにxlsx形式かcsv形式で保存したい）
色々考えた結果Accessのエクスポート機能を利用しようと思ったのですが
DoCmd.TransferSpreadsheetの処理がうまくいきません。

発生している問題・エラーメッセージ

実行時エラー2046
コマンドまたはアクション'スプレッドシート変換'は無効です。

該当のソースコード

ExcelVBA
1'DB接続(ここは問題なくずっと使えてます）
2Sub ConnectDB()
3    Dim ConnectionString As String
4    Dim odbdDB As String
5    Dim read_folder As String
6
7    odbdDB = ThisWorkbook.Sheets("Top").Range("M2")
8
9    If odbdDB = "" Then
10        MsgBox "データベースの格納先が入力されていません。" & vbCrLf & "〔DB格納先〕に正しく入力してください。処理を終了します。", vbOKOnly + vbCritical
11        Exit Sub
12    End If
13
14    Set adoCn = CreateObject("ADODB.Connection") 'ADODBコネクションオブジェクトを作成
15    adoCn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & odbdDB & ";" & "JET OLEDB:Database"  'DBへの接続
16    adoCn.Open
17End Sub

この処理の後に

ExcelVBA
1Sub test()
2
3Dim strSQL As String
4Dim last_run As String
5Dim objACCESS As Object
6    
7    'Accessのアプリケーションオブジェクトを作成する
8    Set objACCESS = CreateObject("Access.Application")
9
10    adoCn.BeginTrans    'トランザクション開始
11    Set adoRs = CreateObject("ADODB.Recordset") 'ADOレコードセットオブジェクトを作成
12    strSQL = "SELECT Max(前回実行日.登録日) FROM [前回実行日テーブル];"
13    adoRs.Open strSQL, adoCn
14    last_run = adoRs.Fields(0) '前回実行日の取得
15    If DateDiff("d", last_run, Now) > 10 Then '前回の実行日が10日以上前なら
16**          objACCESS.DoCmd.TransferSpreadsheet acExport, , "BKUP取りたいテーブル名", _
17            "保存先パス+ファイル名" & last_run & ".xlsx"　←ここでエラーが発生します**
18    End If
19
20～～～（このあとは別の今まで動いている処理があります）～～～
21    'トランザクション終了
22    adoCn.CommitTrans
23End sub

お聞きしたいこと

①そもそもなんですが、ExcelVBAからAccessのエクスポート機能の利用は出来るのでしょうか？
②Access上でエクスポートのマクロを作成し、
ExcelVBAからAccessマクロを呼び出すという使い方が正解なんでしょうか？

ExcelとAccessの連携についてあまりよく分かっておらず、今までも見よう見まねで作ってきたので
お恥ずかしいのですがご教示いただけるとありがたいです。
よろしくお願いします。

補足情報（FW/ツールのバージョンなど）

Excel2016,Access2016です。

azzuro

2020/08/27 03:08

ヒントだけ。 Excel とAccessの連携で以前よく使ったのはCopyFromRecordsetです。 Excel側からテーブルをレコードセットで開いて、シート上に一括で貼りつけることができます。 CopyFromRecordsetをキーワードにしてググってみてください。

salty_

2020/08/27 03:13

azzuro様　ご回答ありがとうございます！！調べてみます！！m( _ _)m

tosi

2020/08/27 05:36

過去にこの様な質問ありました。ご参考まで。 https://teratail.com/questions/279333

salty_

2020/08/27 07:19

tosi様　ご回答ありがとうございます！まだうまくいっておらず・・・こちらの方法も参考にしてみます！

行動規範の内容に同意します

回答2件

自己解決

皆さま、色々な情報をありがとうございました。
OpenCurrentDatabaseメソッドではエラーが何度も出てしまい、よくよく考えたところ
エクスポートではなくExcelに書き出して保存でいいのでは？と思ったため
azzuro様の助言のCopyFromRecordsetに活用させていただきました！
以下、コードになります。

ExcelVBA
1Sub test()
2
3Dim strSQL As String
4Dim last_run As String   '最終実行日
5Dim bkup_name As String  'BKUP用ワークブックの名前
6Dim FolderName As String 'BKUP保存先
7Dim bkup_wb As Workbook  'BKUP用ワークブック
8
9    adoCn.BeginTrans    'トランザクション開始
10    Set adoRs = CreateObject("ADODB.Recordset") 'ADOレコードセットオブジェクトを作成
11  
12    strSQL = "SELECT Max(最終実行日テーブル.登録日) FROM [最終実行日テーブル];"
13    adoRs.Open strSQL, adoCn
14    last_run = adoRs.Fields(0)
15        
16    adoRs.Close
17
18    If DateDiff("d", last_run, Now) > 10 Then '前回実行日が10日以上前ならBKUPを取る
19            
20            bkup_name = "BKUPデータ_" & Format(last_run, "yyyymmdd") & ".xlsx"
21            
22            FolderName = ThisWorkbook.path & "過去実行データBKUP"  'BKUP保存先
23            
24                If Dir(FolderName, vbDirectory) = "" Then   '同名のフォルダがない場合フォルダを作成
25                    MkDir FolderName
26                End If
27            
28            '新しいブックをFolderName直下に格納
29            Set bkup_wb = Workbooks.Add '新しいファイルを作成する
30            bkup_wb.SaveAs FolderName & "\" & bkup_name  '新しいファイルを保存
31            
32            Workbooks.Open FolderName & "\" & bkup_name
33            
34            strSQL = "SELECT * FROM [バックアップを取るテーブル名]"
35            adoRs.Open strSQL, adoCn
36            
37            For i = 0 To adoRs.Fields.Count - 1
38                
39                bkup_wb.Sheets(1).Range("A1").Offset(0, i).Value = adoRs(i).Name
40            Next i
41            bkup_wb.Sheets(1).Range("A1").Offset(1, 0).CopyFromRecordset adoRs
42            bkup_wb.Save
43            bkup_wb.Close
44
45        End If
46～～～他の処理～～～
47    'トランザクション終了
48    adoCn.CommitTrans
49End Sub
50