VBAにてリンクテーブルを作成する方法について

Question

実現したいこと

ACCESSにてODBC経由でORACLEに接続してリンクテーブルを作成しています。
ACCESSを開き直すたびにODBCのリンクが切れてしまうという事象が発生しております。

以下のいずれかの方法についてご存じの方がいらっしゃいましたら教えていただけないでしょうか。
・ACCESSを開き直すたびにODBCの接続が切れてしまうという事象自体を改善する方法
・VBAでリンクテーブルを作成する方法（できればソースもいただきたい。）

[使用環境]
Windows10
ACCESSバージョン14(32bit）
ORACLE Client 11.2.0(32bit）

発生している問題・分からないこと

ODBC--'ODBC名'への接続が失敗しました。

ACCESSファイルを開き直したあとにリンクテーブルを開こうとすると
上記のエラーが表示されます。

該当のソースコード

特になし

試したこと・調べたこと

• teratailやGoogle等で検索した
• ソースコードを自分なりに変更した
• 知人に聞いた
• その他

上記の詳細・結果

以下いずれかの方法を行うと改善します。
１．適当になにか１つのテーブルのリンクを生成する。
２．リンクテーブルマネージャーからリンクを更新する
すべてのテーブルで行う必要はなくどれか１つのテーブルのリンクを生成もしくは更新
すると改善されます。

補足

特になし

Answer 1

ACCESSを開き直すたびにODBCのリンクが切れてしまう

Access2021 他のPCでの実行時エラー'-2147467259(80004005)が解決できない

基本的には上記の件と同じ問題。

1. DAO.TableDef オブジェクトにより任意のリンクテーブルを参照し、その Connect プロパティに Oracle データベースへの接続に必要なパラメータを記述したODBC接続文字列を渡す。

2. その DAO.TableDef オブジェクトの RefreshLink メソッドを呼び出す。

以上の処理を実行する Function プロシージャを作成し、AutoExec マクロ、またはスタートアップフォームの Open イベントから呼び出すようになさればよろしいでしょう。

Comments

[safai]

試しに以下のようなソースでやってみたのですが、再接続時にデータソース選択画面が表示されて
そこからODBCに接続し直さないといけないようです。
データソース選択画面を表示させずに自動的に設定した接続方法で接続する方法はないでしょうか。
Dim dbs As Database
Dim tdf As TableDef
Dim dbPath As String

dbPath = "c:\test.accdb"

Set dbs = CurrentDb

'全テーブルの探索ループ
For Each tdf In dbs.TableDefs
With tdf

.Connect = ";DATABASE=" & dbPath
.RefreshLink

End With
Next tdf
End Sub

[sk.exe]

> dbPath = "c:\test.accdb"

> .Connect = ";DATABASE=" & dbPath

それは Access データベース（ .mdb / .accdb ファイル）をリンク先とする場合の接続文字列の書き方です。

設定すべき接続文字列は、リンク先となるデータソースの種類や接続に使用するドライバー/プロバイダーによって異なります。またユーザーIDとパスワードの指定も必要です。

既に Oracle データベースをリンク先とするリンクテーブルが存在する場合は、とりあえずイミディエイトウィンドウから次のようなコードを実行し、出力された文字列を参考に接続文字列を構成するようにして下さい。

-----------------------------------------------------------

? CurrentDb.TableDefs("リンクテーブル名").Connect

-----------------------------------------------------------

[safai]

やはり実行するとデータソース選択画面が表示されてしまうようです。
接続する際にVBAで接続先を指定するようにはできないでしょうか。

[sk.exe]

> やはり実行するとデータソース選択画面が表示されてしまうようです。

具体的にどのようなプロシージャを作成されたのか、具体的にどのような接続文字列を Connect プロパティに代入なさろうとされているのか、そのプロシージャをどのタイミングで呼び出そうとされているのかが不明ですので、現時点ではお答えのしようがありません。

仮に「『リンクテーブルのリンクの再更新を実行するプロシージャ』自体は正常に動作するが、それを呼び出す前にデータソース選択画面が表示されてしまう」ということであれば、呼び出すタイミングか呼び出す方法に問題がある可能性があります。

> ? CurrentDb.TableDefs("リンクテーブル名").Connect

「上記のコードをイミディエイトウィンドウから実行しようとした時点でデータソース選択画面が表示された」という意味でおっしゃっているのであれば、そのまま認証を行って Oracle データベースとの接続を確立した上で Connect プロパティの値を確認して下さい。

Answer 2

' iUser          ORACLE に接続するユーザ名(テーブルが存在するスキーマ名を兼ねます)
' iPassword      ORACLE に接続するパスワード
' iDSN           ORACLE に接続する DSN
' iTableName     テーブル名
Sub CreateLinkTable(ByVal iUser As String, ByVal iPassword As String, ByVal iDSN As String, ByVal iTableName As String)
Dim strConnect As String
Dim attatchName As String
Dim db As Database
Dim tdf As TableDef
     
    strConnect = "ODBC;DSN=" & iDSN & ";Uid=" & iUser & ";Pwd=" & iPassword
    attatchName = iUser & "_" & iTableName
    
    Set db = CurrentDb
    
    On Error Resume Next
    db.TableDefs.Delete attatchName
    On Error GoTo 0
    
    Set tdf = db.CreateTableDef(attatchName)
    tdf.Connect = strConnect
    tdf.SourceTableName = iUser & "." & iTableName
    tdf.Attributes = tdf.Attributes Or dbAttachSavePWD
    db.TableDefs.Append tdf
End Sub

DSN は ODBC アドミニストレータで定義しますが、32bit/64bit に注意してください。

Comments

[safai]

うまく動きませんでした。
Set pAttachTBL = iMyDb.CreateTableDef(pAttachName)
のところで実行エラー'424'　オブジェクトが必要です。
というエラーが表示されます。

設定する変数値はODBC設定に以下の項目であっていますか。

iDestDB=ODBC名
iMyDb=TNSサービス名
iUser =ユーザーID

またiTablenameはリンクを作成したいテーブル名であっていますか。

[KOZ6.0]

引数はソースのコメントにあるとおりです。
現在どのようにリンクテーブルを作成しているんでしょうか？

[safai]

コメントに書いてある言葉の意味がよくわからないのです。
WindowsにてODBC設定時にODBC名、TNSサービス名、ユーザーID、パスワードを設定しますが、
設定するのはここに設定している設定値ではないのですか？

ODBCデータソースからODBC経由でリンクを作成しています。
このような答えで的を射ていますか？

[KOZ6.0]

回答を書き換えます。