Accessのリンクテーブル機能を使用してSqlServerへのリンクテーブルを作成することになったため、AccessからSqlServerへのリンクテーブルを作成する必要になったため、関数を作成しました。
まず最初にコードだけをかくとこのようになります。
'--DsnLessのSqlServerへのリンクテーブルを作成する
'--tableName:作成対象のテーブル名
'--linkTableNaMe:リンクテーブル名
Public Function setDsnLessTable(tableName As String, linkTableName As String) As Long
On Error GoTo catch
Dim td As tableDef
Dim databaseName As String
Dim userName As String
Dim userPassword As String
Dim serverName As String
Dim connectionString As String
setDsnLessTable = 0
databaseName = "SqlServerのデータベース名"
userName = "SqlServerのユーザー名"
userPassword = "SqlServerのユーザーのパスワード"
serverName = "localhost,1111"
'--同一の名前のテーブルがあれば削除する
For Each td In CurrentDb.TableDefs
If td.Name = tableName Then
CurrentDb.TableDefs.Delete tableName
End If
Next
connectionString = "ODBC;DRIVER=SQL Server;" _
& "SERVER=" & serverName & ";" _
& "DATABASE=" & databaseName & ";" _
& "UID=" & userName & ";" _
& "PWD=" & userPassword & ";"
Set td = CurrentDb.CreateTableDef(tableName, dbAttachSavePWD, linkTableName, connectionString)
CurrentDb.TableDefs.Append td
Exit Function
catch:
setDsnLessTable = -1
MsgBox "setDsnLessTable error: " & Err.Description
End Function基本的なコードは上記のようになります。
あくまでも接続文字列の部分は最低限だけを書いています。
必要であれば以下のコードも接続文字列に追加します。
& "Encrypt=True;" _
& "TrustServerCertificate=True;"通信の暗号化や、証明書の検証をスキップするかなどの設定となっています。
では次に実際の使い方を見ていきます。
DSN Lessリンクテーブルとは？
今回リンクテーブルを作成するときにDSN Lessテーブルを使用しています。
通常accessでSqlServerのリンクテーブルを作成するには「DSNファイル」が必要となります。
しかし、この方法だとDSNファイルを別で管理する必要があるので、複数の端末で使用されるクライアントを作成していると管理する部分が増えます。
個人的にはそれを避けたいので、DSN LESS接続を使っているのですが、具体的なメリットはこのようになります。
DSN Less接続のメリット
- ポータビリティ: DSN設定をデータベースやシステムに保存する必要がないため、アプリケーションを別のコンピューターに移動しても、追加の設定なしに接続が可能です。
- 簡易性: システムDSNやファイルDSNを事前に設定する手間が不要になります。
- 柔軟性: プログラム内で直接接続文字列を指定するため、実行時に接続先を動的に変更することができます。
シンプルに全てコードでかけるので、個人的にはDSN LESS接続しておくのが便利なのでこちらを使用しています。
SqlServerへのリンクテーブル作成する関数の使用例
先ほどのコードの実際の使用例として挙げるとまずこちらになります。
if setDsnLessTable("リンクテーブルにしたいテーブル名","accdb上で見えるテーブル名") = -1 then
exit sub
end ifシンプルに一つのテーブルをSqlServerのリンクテーブルにしたい場合は上記のように使用します。
指定テーブル数が少ないのであればこれでOKです。
私の場合はすでに存在しているリンクテーブルをSqlServerのリンクへ変更するため、以下の関数を作成ました。
全てのリンクテーブルをSqlServerに置き換えてくれるようになっています。
Public Function changeAllLinkTablesToSqlserver() As Long
On Error GoTo catch
Dim sqlConnection As String
Dim db As Dao.Database
Dim tb As Dao.tableDef
Dim tableName As String
Dim errorString As String
changeAllLinkTablesToSqlserver = 0
'--------------------------------------
Set db = CurrentDb
'テーブルオブジェクトを列挙
For Each tb In db.TableDefs
'リンクテーブルだけを処理
If tb.Connect <> "" Then
tableName = tb.Name
If setDsnLessTable(tableName, tableName) = -1 Then
GoTo catch
End If
End If
Next tb
db.Close
Set tb = Nothing
Set db = Nothing
Exit Function
catch:
errorString = Err.Description
On Error Resume Next
db.Close
Set tb = Nothing
Set db = Nothing
Call Utility.consoleLog(errorString)
changeAllLinkTablesToSqlserver = -1
End Function
tb.Connectはリンクテーブルの場合、文字列が入っているので、文字列の有無でリンクテーブルかどうかの判定をしています。
基本的にリンクテーブルの名前は変更しないので、setDnsLessTableの引数には同じ値を送る形となっています。
AccessでSqlServerにリンクテーブルが必要になった経緯
参画していた場所で昔からaccessを使用してデータベースを作成されていました。
VBAで書かれていて、DBとフロントは一応別れていました。
しかし、DBはリンクテーブルで作成されており、DAOを使用して書かれていました。
テーブル数は130以上、データ件数も30万件を超えるレコードがあるなどの状況でした。
リンクテーブルでの実装は処理が重くなることはよく言われており、
流石にAccessには荷が重いということで、SqlServerへの移行を考えることにしました。
一つづつ変えていくのでも良いかと思ったのですが、あっちはAccess、こっちはSqlserverで管理も大変かと思ったので、一気にSqlServerへの移行することにしました。
リンクテーブルそのまま移行する理由
本当であればSqlServerに変えた時にAdoでの実装もするのが良いのですが、流石に影響範囲が大きく断念することに。
リンクテーブルを使用して書かれていたコードだったため、SqlServerへのリンクテーブルであれば、基本的にはそのままのコードで動くため、あえてリンクテーブルのまま移行を行い、その後に徐々にAdoを使用した書き方に変更していくような流れでやろうと決まりました。
まとめ
ここまで読んでいただきありがとうございます。
Accessのリンクテーブルはパフォーマンスは良くないので多用したくないですが、徐々に移行していくのに使用するのはまだ使いやすいかなと思います。
もし現在のプロジェクトでリンクテーブルを使用するのであれば、今回のコードをベースに使ってもらえれば、比較的簡単にリンクテーブルを作成できると思いますので、よければ使ってみて下さい。
