*

【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】表や図形、画像オブジェクトの位置をピッタリ合わせる方法

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

記事を読む

Microsoft Office

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

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

記事を読む

Microsoft Office

【Outlook】保存フォルダ(archive.pst)が開けない。

急に「保存フォルダ」が開けなくなった。 Outlookのバージョンは、2007のSP3。

記事を読む

Microsoft Office

【Access】「更新可能なクエリであることが必要です」のクエリのエラーポップアップ

Accessでクエリを作成し、実行すると 「更新可能なクエリであることが必要です。」

記事を読む

Microsoft Office

【Access】リンクテーブルが接続(表示)できない。

Accessを使用していて、リンクテーブルが表示(接続)できない。 あまりないけど、困った時に

記事を読む

Microsoft Office

【Access】エラーポップアップ。「少数を丸めたために、データが切り捨てられました。」

Accessのリンクテーブルでデータを確認していたら、急に・・・ 「少数を丸めたために

記事を読む

Microsoft Office

【Outlook】Gmailを使う時のOutlookの設定方法

Gmailアカウント側の設定を行った上で、Outlookの設定を行います。 Gmailアカウン

記事を読む

Microsoft Office

【Office】Access2007のピボットテーブルとExcel連携

仕事でAccess2007でEXCELみたいにピボットテーブルを使えますか? と質問を頂いた。

記事を読む

Microsoft Office

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

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

記事を読む

Microsoft Office

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

Excel 2013でのお話。 なぜかわからないが、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 ↑