【Sqlserver】複製したデータベースにAccessで接続しストアドが動くのかの実験（データベース複製実験３）

リンクテーブルODBC情報をVBAで一括変換

【Sqlserver】データベースを複製してAccessで動くようにODBC設定VBAの実験準備（データベース複製実験２）

AccessのSqlServerからのリンクテーブルが増えるといちいち修正するのが面倒になります。

そこで、一括でODBCの変換できるVBAを使って変換してみました。

 

リンクテーブル変更ボタンをクリックすると、下記のVBAコードが動いて一括でリンクテーブルが変更になります。

Private Sub link_t_btn_Click()

Dim db As DAO.DATABASE, tb As DAO.TableDef
Dim strCon As String
 
Dim idns As String
Dim idatabase As String
Dim iuid As String
Dim ipwd As String
'Dim iserver As String

idns = DNS
idatabase = DATABASE
iuid = UID
ipwd = PWD
'iserver = SERVER
 
'接続文字列(データソースレス接続形式)
'strCon = "Driver={SQL Server};DATABASE=" & idatabase & ";UID=" & iuid & ";PWD=" & ipwd & ";SERVER=" & iserver & ""

strCon = "ODBC;DSN=" & idns & ";DATABASE=" & idatabase & ";UID=" & iuid & ";PWD=" & ipwd & ""

    Set db = CurrentDb

    For Each tb In db.TableDefs
        If tb.Connect <> "" Then
            tb.Connect = strCon
            tb.RefreshLink
        End If
    Next tb

MsgBox "終了しました。"

End Sub

上のコードでコメントアウトしている、

サーバー名は必要ない

ようです。

ODBC;Driver={SQL Server}を使う場合は、サーバー名が必要になりますが、

ODBC;DSN=の場合は、サーバー名は省略できるようです。

パススルークエリのODBC情報をVBAで一括変換

パススルークエリのODBCの接続プロパティを見ると下記のようになっていました。

パススルークエリのODBC接続情報もリンクテーブルと同じようです。

Private Sub pass_query_btn_Click()
Dim O As Object
Dim db As DAO.DATABASE
Dim pass_q As DAO.QueryDef

Dim idns As String
Dim idatabase As String
Dim iuid As String
Dim ipwd As String
'Dim iserver As String

idns = DNS
idatabase = DATABASE
iuid = UID
ipwd = PWD
'iserver = SERVER
 

Set db = CurrentDb

For Each O In Application.CurrentData.AllQueries
Set pass_q = db.QueryDefs(O.name)

'pass_q.Connect = "ODBC;DSN=" & idns & ";UID=" & iuid & ";PWD=" & ipwd & ";DATABASE=" & idatabase & ";LANGUAGE=日本語"

pass_q.Connect = "ODBC;DSN=" & idns & ";DATABASE=" & idatabase & ";UID=" & iuid & ";PWD=" & ipwd & ""

Next

MsgBox "終了しました。"


End Sub

ここで大問題です。

パススルークエリだけを選んでODBC設定する方法を探しましたが見つかりませんでした。

自分のスキルではこの問題は乗り越えられそうもないので諦めます。

現時点の苦肉の対応策

全てのクエリがパススルークエリとみなされてODBC設定をされてしまうので、苦肉の策として、

手動ですが、そんなにデータベースを頻繁に増やして作業するということはないので、この部分だけは仕方ないですね。

AccessVBAのストアド呼び出しコードの変更

AccessのVBAでストアドを実行させていますが、そのODBC接続設定も変更しなければなりません。

検索で変更箇所はでてくるものでしょうか！？

変更箇所は、下記の部分になりますので、「ODBC1」とか、「接続文字列」とかで検索かけ見ようと思います。

通常のVBA以外に、標準モジュール（Functionで作成したもの）にも実験の為に、ストアド実行するコードを入れてみました。

検索の対象で４種類の検索方法があるようなので意味をしらべてみました。

今回は、全てのODBC設定を変更したいので、この場合は、「カレントプロジェクト」を選択すればいいらしい。

検索して手修正するのが面倒な場合は置換もできるので、この方が早いですね。

置換で、ODBC１からODBC2に変更し、user1からuser2に、DB1からDB2へ変更した。

この実験の結果

この実験の目的は、

という事でした。

 

特にデータベースを複製すると、ストアドがうまく動かないのではないか？という不信感があったので実験をすることにしましたが、結果はうまく動くことが確認できました。

ボタン一回でデータベースが複製できてODBC接続設定もできればいいのだが、そこまでシンプルには行きそうにもありません。

しかし、

とりあえず、複製して同じ仕組みを提供できることが分かった

ので良しとします。

 

ただ、注意点として今回の作業内容の他に、

 

 

ので注意してください。