質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

90.48%

  • VBA

    1861questions

    VBAはオブジェクト指向プログラミング言語のひとつで、マクロを作成によりExcelなどのOffice業務を自動化することができます。

  • Access

    456questions

    Accessはマイクロソフトによるリレーショナルデータベース管理システムです。オブジェクト指向のアプリケーション作成に対応しており、テーブルや編集をはじめ、クエリ生成、入力フォーム作成、レポート作成など一通りの機能を備えています。

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

解決済

回答 1

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 4,872

nekomura

score 124

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

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

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

といったイメージです。

プロシージャ終了後は、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

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

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

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

回答 1

checkベストアンサー

+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

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2016/05/20 10:51 編集

    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様に対して矢継ぎ早の質問になり申し訳ありません。

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

    キャンセル

  • 2016/05/20 11:09 編集

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

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

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

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

    キャンセル

  • 2016/05/20 11:11 編集

    Suenaga様
    五月雨式の質問になり申し訳ありません。
    当該コードには、
    cat.ActiveConnection = CurrentProject.Connection
    と記述していたので
    Set tdl = CurrentProject.Tables("dbo_A2")

    Set tdl = cat.Tables("dbo_A2")
    と書き直したところ、予測変換に「Tables」が出ました。
    (Suenaga様のご指摘どおり、sがついていました)

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

    キャンセル

  • 2016/05/20 11:36

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

    キャンセル

  • 2016/05/20 11:39

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

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

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

    キャンセル

  • 2016/05/20 15:00

    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;")
    を使用するのが適切ですか?
    何度もお尋ねしてしまい申し訳ありません。
    よろしくお願いいたします。

    キャンセル

  • 2016/05/21 00:04 編集

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

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

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

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

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

    キャンセル

  • 2016/05/22 15:48

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

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 90.48%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問

同じタグがついた質問を見る

  • VBA

    1861questions

    VBAはオブジェクト指向プログラミング言語のひとつで、マクロを作成によりExcelなどのOffice業務を自動化することができます。

  • Access

    456questions

    Accessはマイクロソフトによるリレーショナルデータベース管理システムです。オブジェクト指向のアプリケーション作成に対応しており、テーブルや編集をはじめ、クエリ生成、入力フォーム作成、レポート作成など一通りの機能を備えています。