*

【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

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

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

記事を読む

Microsoft Office

【Outlook】送信時の0x80040201エラーの対処

発生した環境 OSはWindows7。Outlookは2013。イーモバイルのPocket W

記事を読む

Microsoft Office

【Microsoft Office】Access 2007でMDBファイルが開けない

こんばんわ。今日、ハマッたことを書きたいと思います。 会社の内線がなり「共有フォルダ上のAcces

記事を読む

Microsoft Office

【Access】クエリ実行時に「引数が違います」と表示され実行できないときの対処法

Accessのクエリを実行すると 引数が違います とポップアップが表示され、クエリが実行できない

記事を読む

Microsoft Office

【Excel】複数のエクセル(ブック)のシートを一つのブックに結合するVBA

複数のエクセルのシートを一つのエクセルにまとめるVBAを作ってみた。 ソースコードは使い方の下

記事を読む

Microsoft Office

【Excel】エクセルを起動(実行)しても、何も表示されない。

仕事の共有で使用しているエクセルを開くと、Excelの枠だけ表示されて何も表示されない。 壊れ

記事を読む

Microsoft Office

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

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

記事を読む

Microsoft Office

【Access】循環参照エラーの回避方法

Accessでクエリを実行しようとすると クエリ定義の SELECT で指定されている別名

記事を読む

Microsoft Office

【Excel】「メモリまたはディスクの空き容量が~」のポップアップで開けない時の対処法

メモリまたはディスクの空き容量が不足しているため、ドキュメントを開いたり、保存したりできません。

記事を読む

Microsoft Office

【Outlook】プレビューウインドウ(閲覧ウインドウ)で表示したら、メール既読のオフ設定

前回、「Outlookの開封メッセージ送信を先送りにする方法」で、少し書きましたが、 私用のメ

記事を読む

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 ↑