Access起動時にSQL serverとのODBC接続を自動更新させる

ODBC接続を行うVBAモジュールを作成し、これをAccess2010のマクロ "AutoExec" からcallする

AccessDB.accdb を開き、

作成 > 標準モジュール > VBA の Module1 編集画面で、

VBA のコード(ex. RefreshODBCLink) を記述する

作成 > マクロ > マクロ作成画面で 、

"プロシージャの実行" を選択し、"プロシージャ名" で RefreshODBClink() を入力 > [保存] >

マクロ名を "AutoExec" として保存する

VBAモジュールの例：

----------

Function RefreshODBClink()

    Dim Dbs As DAO.Database

    Dim Tdf As DAO.TableDef

    Dim strODBC As String

    Set Dbs = CurrentDb

    strODBC = "ODBC;DRIVER=SQL Server;UID=uid;PWD=password;LANGUAGE=日本語;DATABASE=dbname;SERVER=servername;"

    ' ODBCLink の再接続

    For Each Tdf In Dbs.TableDefs

        If Tdf.Connect Like "ODBC;*" Then

            Debug.Print "Set Tdf Connect:" & Tdf.Name & ":" & strODBC

            Tdf.Connect = strODBC

            Tdf.RefreshLink

            Tdf.Fields.Refresh

        End If

        DoEvents

    Next Tdf

    Dbs.TableDefs.Refresh

End Function

----------

 

ODBCリンクを作成するVBA

 

(Windows7, SQL Server 2008 R2, ODBC, Access 2010)

 

Function makeOdbcLink()

    Dim Dbs As DAO.Database

    Dim tdf As DAO.TableDef

    Dim strODBC As String

    strODBC = "ODBC;DRIVER=SQL Server;LANGUAGE=日本語;DATABASE=dbname;SERVER=servername;Integrated Security=SSPI;Auto Translate=True;"

    

    Set Dbs = CurrentDb

        

    ' 既存の ODBCリンクテーブル を削除

    '    On Error Resume Next

    '    CurrentDb.TableDefs.Delete "exist_table_name"     ' ここに削除したいテーブル名を記述

    '    On Error GoTo 0

        

    Set tdf = CurrentDb.CreateTableDef("link_table_name") ' ここに作成するテーブル名を記述

        tdf.Connect = strODBC

        tdf.SourceTableName = "surce_table_name"             ' ここにリンク元テーブル名を記述

        CurrentDb.TableDefs.Append tdf

        

        DoEvents

        

    Dbs.TableDefs.Refresh

    DoEvents

        

End Function

タグ：SQL ODBC access

コメント 2

初心者です。
毎回接続時　oracle ODBCドライバ接続というポップアップがでてサービス名（S)　EUCDB　ユーザー名（U)　E1410007　とでて、パスワード（P)XXXXXX　を入力するとつながります。
これを自動でしたいのですがモジュールのどこにどの名前をいれたらいいのかわかりません。
また、新規でリンク接続するときはデータソースの選択でDNS_EUCDBを選ぶようになっています。
by 藤田 (2017-07-27 10:40) 

※1 への回答
Oracle フォーラムのほうが回答率ありそうですが、取り急ぎ、ODBC 文字列に PWD パラメータがあります。この記事は SQLServer の例なので、リンクを参照して Oracle 向けの文字列にする必要があります。
by FJT (2018-02-19 11:44)