*

【Access】リンクテーブルの一括変換。ODBCを使わずにSQL Serverへ接続

公開日: : 最終更新日:2015/01/12 Microsoft Office , , , , ,

Accessのリンクテーブルの接続先を一括で変更する方法

この方法なら、ODBCを使用していないので、作業端末を変更した場合もわざわざODBCを作らなくてOK。

色々調べてみましたが、Accessだと、SQL Serverとのリンクテーブルを作成、変更するには、ODBCのDSNの設定が必要。
さらにODBCを作成するには、管理者権限も必要ということで、結構めんどくさい。

ということで、VBAで既存のリンクテーブルを削除し、再作成することで変更としました。

 

Sub LinkTableChange()

    '#########################################################
    ' リンク変換先の指定
    '#########################################################
    'データベースサーバーインスタンスを指定します。
    Const DBSrv As String = "【サーバー名を指定】"
    '接続するデータベースを指定します。
    Const DBName As String = "【データベース名を指定】"

    '#########################################################
    ' 変数定義
    '#########################################################
    Dim ConTest As ADODB.Connection
    Dim tableItem As TableDef
    Dim LinkConnection As String
    Dim NewTableItem As TableDef
    Dim TableName As String
    Dim SourceTableName As String

    'Windows認証用
    Const LinkConTemplate As String = "ODBC;DRIVER=SQL Server;SERVER=【サーバー名】;DATABASE=【データベース名】;Trusted_Connection=Yes"
    'SQLSqerver認証用
    'Const LinkConTemplate As String = "ODBC;DRIVER=SQL Server;SERVER=【サーバー名】;DATABASE=【データベース名】;UID=【ユーザ名】;PWD=【パスワード】"

    '#########################################################
    ' リンクテーブル作成用の接続文字列作成
    '#########################################################

    '接続文字列作成
    LinkConnection = Replace(LinkConTemplate, "【サーバー名】", DBSrv)
    LinkConnection = Replace(LinkConnection, "【データベース名】", DBName)

    '#########################################################
    ' リンクテーブルの接続先変更処理(削除、再作成)
    '#########################################################
    For Each tableItem In CurrentDb.TableDefs

        'Accessのローカルテーブルとデフォルト?で持ってるテーブルを除く
        If tableItem.Connect <> "" And tableItem.SourceTableName <> "" Then

            '変更するリンクテーブル名、ソース名を保存
            TableName = tableItem.Name
            SourceTableName = tableItem.SourceTableName

            '既存のリンクテーブルの削除
            CurrentDb.TableDefs.Delete tableItem.Name

            'リンクテーブルの再作成
            Set NewTableItem = CurrentDb.CreateTableDef(TableName)
            NewTableItem.Connect = LinkConnection
            NewTableItem.SourceTableName = SourceTableName
            CurrentDb.TableDefs.Append NewTableItem
            CurrentDb.TableDefs(TableName).RefreshLink
            Set NewTableItem = Nothing

        End If
    Next

    Set tableItem = Nothing

    '#########################################################
    ' 完了のポップアップ出力
    '#########################################################

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

End Sub

Adsense

関連記事

Microsoft Office

【PowerPoint】表や図形、画像オブジェクトの位置をピッタリ合わせる方法

パワーポイントで、図形や画像等のオブジェクトの細かい位置調整は、 完璧主義のこだわり派には非常にイ

記事を読む

データベース

【SQL Server】Excel VBAでSQLを実行し、レコードを更新(追加、更新、削除)する

VBAでSQL Serverのテーブルに SQL(Insert、Update、Delete)を発行

記事を読む

Microsoft Office

【Excel】エクセル。シートの名前を変更しようとしたらエラーポップアップ

エクセルでシート名を変更しようとしたときに… シートの名前をほかのシート、Visual B

記事を読む

Microsoft Office

【Excel】複数項目を指定した昇順、降順の並べ替え方法

Excel 2013でのお話。 なぜかわからないが、Excelのオートフィルターを使うと単一の

記事を読む

Microsoft Office

【Outlook】Gmailを使う時の設定(Gmailアカウント側)

OutlookでGmailを使用するには、Gmailのアカウント側でいくつか設定をしなければいけない

記事を読む

Microsoft Office

【Access】クエリで小数点の切り下げ、切り上げ

Access2007には、roundup、rounddown関数がない。 なので、純粋に関数だ

記事を読む

Microsoft Office

【Outlook】同期の失敗フォルダを消す方法

問い合わせで、Outlookの「同期の失敗」フォルダを消したいと受けた。 調べてみたけど、基本

記事を読む

Microsoft Office

【Access】削除クエリの「指定されたテーブルから削除できませんでした。」の対処法

削除クエリで「指定されたテーブルから削除できませんでした。」と ポップアップが表示され、クエリが実

記事を読む

Microsoft Office

【拡張子:accde】Access2007でaccdeの使い道と作成方法

うちの職場ではEUC(End User Computing)として Access2007 を多用して

記事を読む

Microsoft Office

【Excel】関数を一括でメモ帳などにコピーする方法

  セルに入力されている関数一つのみコピーしたい場合、対象セルを選択して、数式バーか

記事を読む

Adsense

Message

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

Adsense

Microsoft Office
【Access】削除クエリの「指定されたテーブルから削除できませんでした。」の対処法

削除クエリで「指定されたテーブルから削除できませんでした。」と ポッ

Microsoft Office
【PowerPoint】表や図形、画像オブジェクトの位置をピッタリ合わせる方法

パワーポイントで、図形や画像等のオブジェクトの細かい位置調整は、 完

データベース
【SQL Server】Excel VBAのレコードセットを使ってテーブルの一括更新

前々回は読み取り専用でレコードセットへ取得し、結果をエクセルへ書き出し

application
【Visual Studio】デバック、ブレークポイントで止まらない。

Visual Studio 2015でVB.netのプログラムをデバッ

データベース
【SQL Server】Excel VBAでSELECT文を実行してレコードセットへ取得

前回は、テーブルのレコードをVBAで直接更新(Insert/updat

→もっと見る

PAGE TOP ↑