Access vba ADOX 起動時イベント 毎回新規にリンクテーブルを再作成したい

Question

よろしくお願いいたします。

Access起動時に、毎回新規にリンクテーブルを再作成するといったような動作を期待したコードを、標準モジュールに書いています。
（事前準備として、マクロのアクションでプロシージャの実行を選択して、マクロ名を "AutoExec" として保存済み）
期待している動作（上司から言われている動作のヒント）は、実行時毎に、新しく再作成するという様な動作をさせる事です。

そのような動作に見せる為に、私なりに考えた動作は
・まずリンクテーブル"A"を削除し、
・その後リンクテーブル"A2"を作成、
・"A２"のデータは、リンク元から引っ張ってくる
といったイメージです。

プロシージャ終了後は、Accessのナビゲーションウィンドウからもとあった"A"テーブルが消え、
"A2"テーブルが再作成されたテーブルとして置かれる…というようにしたいです。

試行錯誤＆ネットでサンプルを拾ってきて、以下のようなコードを書いてみましたが、
エラーが出て、①の行が黄色く反転します。。
エラー内容：[実行時エラー438 オブジェクトは、このプロパティまたはメソッドをサポートしていません。]

サンプルコードはもともとDAOで書かれており、下記URLを参照にDAO→ADOになんとか書き換えています。
https://msdn.microsoft.com/ja-jp/library/cc948693.aspx(DAO から ADO への移植)
エラーを解決し期待通りの動作をさせる為にはどこを直せばよいでしょうか。

Function makeOdbcLink()

 Dim cat As New ADOX.Catalog
 Dim tbl As New ADOX.Table
 Dim strODBC As String
 Dim strSQL As String

    strODBC = "ODBC;DRIVER=SQL Server;SERVER=I*****4\SQLEXPRESS;Trusted_Connection=Yes;APP=Microsoft Office 2010;DATABASE=TestDB;"
    strSQL = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=TestDB;Data Source=I*******4\SQLEXPRESS"
    cat.ActiveConnection = CurrentProject.Connection
    
    
    ' 既存の ODBCリンクテーブル を削除
        On Error Resume Next  
        cat.Tables.Delete "dbo_qAction_add"   ' ここに削除したいテーブル名を記述 ここまでは実行された
            MsgBox "dbo_Aテーブルを削除しました"
        On Error GoTo 0
  
   
 ①Set tdl = CurrentProject.Table("dbo_A2")   ' ここに作成するテーブル名を記述
  'ここでエラー発生！！！
            MsgBox "dbo_qAction_add2テーブルを再作成しました"
  
  ② tdl.Connect = strSQL //実行時エラー424 オブジェクトが必要です（①をコメントアウトした場合）
  
  ③tdl.Table = "dbo_Action"         ' ここにリンク元テーブル名を記述    //実行時エラー424 オブジェクトが必要です（②をコメントアウトした場合）         
     cat.Tables.Append tbl
        
        DoEvents

    cat.Tables.Refresh
    DoEvents

MsgBox "テーブルのリンク変換完了"


End Function

また、当該コードの①をコメントアウトさせると、②，③に別のエラーが発生します。
可能であればこちらもご教示いただきたく存じますが、それは次回の課題としてまずは
本質問にたいして解決策をお願いいたします。

有職者の皆様方、どうかお知恵をお貸しください。
コーディングの仕方のサンプルもお教え頂ければ勉強になり幸いです。

Answer 1

とりあえず
Set tdl = CurrentProject.Table("dbo_A2")
↓
Set tdl = CurrentProject.Tables("dbo_A2") ← s つき

じゃないでしょうか。

---

2016.05.20. 11:00 追記

だいぶ前にリンクテーブルの「作成」ではなく、すでにあるリンクの接続先DBを「更新」する処理を書いたのを思い出したのでサンプルです。
開発用DBと本番用DBに全く同じ構成のテーブル構造がある状態で、接続先を開発DBと本番DBを切り替えるために使っていたものですので、DB接続部分だけ書き換えています。

' リンクテーブル更新
Sub update_link_table()
    Dim objADOXCat As ADOX.Catalog

    Dim strConnection as String     ' DBへの接続文字列
    Dim strTableName As String      ' リンク先を更新するテーブル名
    
    strConnection = "[接続文字列]"
    strTableName = "[テーブル名]"

    Set objADOXCat = New ADOX.Catalog
    With objADOXCat
        Set .ActiveConnection = CurrentProject.Connection
        With .Tables(strTableName)
            .Properties("Jet OLEDB:Link Provider String") = strConnection
            .Properties.Refresh
        End With
    End With

    Set objADOXCat = Nothing
End Sub

作成の場合も理屈上は With .Tables(strTableName) ~ End With の間を作成用に書けば使えるかと思います。

なお上記のソース、実際はテーブル名、接続文字列などを配列で用意してあり、For Each で書き換えていました。

---

2016.05.20. 11:30 追記

動作テストしてないですが、こんな感じじゃないでしょうか。
削除の方は問題ないと思いますので作成部分だけです。

Sub create_link_table()
    Dim cat As New ADOX.Catalog
    Dim tbl As ADOX.Table
    
    Set cat = New ADOX.Catalog
    Set cat.ActiveConnection = CurrentProject.Connection

    ' 複数ある場合はこの下の2行を繰り返す    
    Set tbl = get_table_def([SQLServerへの接続文字列], [SQLServer上のテーブル名], [Accessで作るテーブル名])
    cat.Tables.Append tbl

    Set tbl = Nothing
    Set cat = Nothing
End Sub

' 作成するリンクテーブルオブジェクト
Function get_table_def(strConnection as String, strRemoteTable As String, strLocalTable as string) As ADOX.Table
    Set get_table_def = New ADOX.Table

    With get_table_def
        .Name = strLocalTable
        .Properties("Jet OLEDB:Create Link") = True
        .Properties("Jet OLEDB:Link Provider String") = strConnection
        .Properties("Jet OLEDB:Remote Table Name") = strRemoteTable
    End With

End Function

Comments

[nekomura]

Suenaga様
早々のご回答に大感謝です。
早速試してみたのですが、同じエラーが出ててしまいました…。
サンプルコードから拾った元のDAOコードは以下だった為、
Set tdl = CurrentDb.CreateTableDef("dbo_A2")
私が
Set tdl = CurrentProject.Tables("dbo_A2")
と書き直しているのですが、
調べたところDAO→ADOでは、
CreateTableDef→Tableとあったのですが、
https://msdn.microsoft.com/ja-jp/library/dd148536.aspx
「CurrentDb」についてはわからなかったので、「CurrentProject」としてみたのです。

実は、CurrentProject．の後、入力候補に”Table”、"Tables"ともに存在しないことが今わかりましたorz（本質問に追記しておきます）
CurrentProject～から誤りなのでしょうか…。わからないことばかりで、Suenaga様に対して矢継ぎ早の質問になり申し訳ありません。

同かよろしくお願いたします。

[kaz.Suenaga]

ああ、Tables はADOXのTablesコレクションなので、DBではなくADOX.Catalogのものですね。

元のソースの本来の流れは、

- 接続先DBのカタログを取得（cat）
- 追加したい新テーブル用のオブジェクト（インスタンス）を生成、設定（tbl）
- カタログに追加 （ cat.Tables.Append tbl ）

という流れなので、ひとまず tbl を生成するところをよく見てみましょう。

[nekomura]

Suenaga様
五月雨式の質問になり申し訳ありません。
当該コードには、
cat.ActiveConnection = CurrentProject.Connection
と記述していたので
Set tdl = CurrentProject.Tables("dbo_A2")
↓
Set tdl = cat.Tables("dbo_A2")
と書き直したところ、予測変換に「Tables」が出ました。
（Suenaga様のご指摘どおり、ｓがついていました）

ただ、違う内容のエラーが出てしまいました…。
本質問を一旦書き直したいと思います。
>という流れなので、ひとまず tbl を生成するところをよく見てみましょう。
はい、ありがとうございます！
どうぞよろしくお願いいたします。

[nekomura]

Suenaga様
＞すでにあるリンクの接続先DBを「更新」する処理
上司の意図的に、これが本来やりたい動作のような気が…。
ひとまず試させていただきますのでまたご報告orご相談させてください。
サンプルコード、ありがとうございます！
わかりやすくてとてもためになります。

[kaz.Suenaga]

> 上司の意図的に、これが本来やりたい動作のような気が…。

0からリンクテーブルを作らないといけないケースって初回しかないと思いますので、多分更新できればいいんじゃないかなと。

まあ作成も更新もどっちもできておくに越したことはないですし、この手のコードは開発時にリンクテーブルを作成する際の作業に使いまわせるので習得しておくとよさそうですね。

[nekomura]

Suenaga様
回答でご教示頂きましたコードを元に、
更新→削除→作成
の順にモジュールを作っているのですが、
11:30 追記　サンプルコードの
With get_table_def
.Name = strLocalTable
.Properties("Jet OLEDB:Create Link") = True
.Properties("Jet OLEDB:Link Provider String") = strConnection
.Properties("Jet OLEDB:Remote Table Name") = strRemoteTable
End With
（）内赤字部分について質問です。
jetデータベースエンジンというと、Accessのデータベースという認識なのですが、
このソースを使って動作をさせるためには、接続文字列はこの場合、
SQLserverへ接続（私の場合"Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=TestDB;Data Source=I*******4\SQLEXPRESS"）
では無く、
Accessデータベースに接続する（ "ODBC;DRIVER=SQL Server;SERVER=I*****4\SQLEXPRESS;Trusted_Connection=Yes;APP=Microsoft Office 2010;DATABASE=TestDB;"）
を使用するのが適切ですか？
何度もお尋ねしてしまい申し訳ありません。
よろしくお願いいたします。

[kaz.Suenaga]

「リンクテーブル」はあくまでAccessのテーブルなので、Accessのテーブルをいじることになります。

Accessに新規テーブルを作る
その際、リンクテーブルとして作る
リンクテーブルの接続先は「接続文字列」
リンクテーブルの接続先のテーブル名は「リモートのテーブル名」

というように、Access内のテーブルのリンク先はSQLServerのこのテーブル、という指定をしているわけです。

そのため、リンクテーブルの接続先がSQLServerである以上、strConnection に対応する接続文字列はSQLServerに対するものです。

リンクテーブルはイメージで言うとショートカットみたいなものなので、置き場所はローカル、それが示す先はリモート、というものです。
データをコピーしてきてるわけではなく、行き先を指定しているだけです。

[nekomura]

Suenaga様
お世話になっております。
現在、ご教示頂いた内容を元に、標準モジュールに
・削除→新しく作成
・更新
という２パターンを作ろうとしています。
質問内容を整理して、改めて不明点を挙げようと思っておりますので、
どうぞよろしくお願いいたします。