#58
「アップサイジングウィザード」に代わるプログラムの３回目です。
前回SQL Server上にアップサイズしたテーブルについて、主キーやインデックス、および既定値の３つについて、追加設定してみます。
ここでも、Accessのテーブル構造からの情報収集 → それを使ったSQL文の生成 → SQL ServerへのSQL文の発行という流れになりますが、まずはSQL文の基本構文について確認しておきます。
- 主キー
ALTER TABLE テーブル名 ADD PRIMARY KEY (フィールド名)
- インデックス（重複なし）
CREATE UNIQUE INDEX インデックス名 ON テーブル名 (フィールド名)
- インデックス（重複あり）
CREATE INDEX インデックス名 ON テーブル名 (フィールド名)
- 既定値
ALTER TABLE テーブル名 ADD DEFAULT 既定値 FOR フィールド名
※今回は複数フィールドから成るインデックスないものとします。
※またインデックスの並べ替え順序はすべて”昇順”であるものとします。
※またインデックスの並べ替え順序はすべて”昇順”であるものとします。
これらを踏まえて、その組み立てに必要となる情報をAccess側のテーブルから取得します。なお、主キーやインデックスに関しては、テーブルのデザインビューのインデックスの画面の情報をプログラムから取得するものと考えると分かりやすいと思います。
主キーの取得
主キーの情報を取得するには、１つのテーブル定義を表す「TableDef」オブジェクトの、すべてのインデックスの集まりである「Indexes」コレクションから１つずつ「Index」オブジェクトを取り出していきます。そしてそのオブジェクトの「Primary」プロパティが”True”であれば、そのインデックスは主キーです。
Sub TrySample_3_1()
Dim dbs As Database
Dim tdf As TableDef
Dim idx As Index
Set dbs = CurrentDb
For Each tdf In dbs.TableDefs
With tdf
If .Attributes = 0 And .Name <> "tblテーブル構造" Then
'Indexesコレクションのループ
For Each idx In tdf.Indexes
If idx.Primary Then
'主キーのとき
Debug.Print .Name 'テーブル名
Debug.Print idx.Name 'インデックス名
Debug.Print idx.Fields(0).Name 'フィールド名（単一とする）
End If
Next idx
End If
End With
Next tdf
End Sub
- インデックスの取得
インデックスの情報を取得するには、主キーと同様に「Index」オブジェクトを取り出していきます。
ただしそこには主キーも”重複あり”のインデックスも”重複なし”のインデックスも含まれていますので、まずは「Primary」プロパティが”False”のものだけ取り出すことで主キーは除外します。
さらに、「固有」のプロパティを表す「Unique」プロパティの値を調べ、その値が”True”なら「重複なしインデックス」、”False”なら「重複ありインデックス」と判定します。
Sub TrySample_3_2()
Dim dbs As Database
Dim tdf As TableDef
Dim idx As Index
Set dbs = CurrentDb
For Each tdf In dbs.TableDefs
With tdf
If .Attributes = 0 And .Name <> "tblテーブル構造" Then
'Indexesコレクションのループ
For Each idx In tdf.Indexes
If Not idx.Primary Then
'主キー以外のとき
If idx.Unique Then
'固有インデックスのとき
Debug.Print "--重複なしインデックス--"
Else
'固有でないインデックスのとき
Debug.Print "--重複ありインデックス--"
End If
Debug.Print .Name 'テーブル名
Debug.Print idx.Name 'インデックス名
Debug.Print idx.Fields(0).Name 'フィールド名（単一とする）
End If
Next idx
End If
End With
Next tdf
End Sub
- 既定値の取得
フィールド定義の「既定値」プロパティの値を取得するのは比較的簡単です。「Field」オブジェクトの「DefaultValue」プロパティを調べます。
Sub TrySample_3_3()
Dim dbs As Database
Dim tdf As TableDef
Dim fld As Field
Set dbs = CurrentDb
For Each tdf In dbs.TableDefs
With tdf
If .Attributes = 0 And .Name <> "tblテーブル構造" Then
For Each fld In .Fields
If Len(fld.DefaultValue) > 0 Then
Debug.Print .Name 'テーブル名
Debug.Print fld.Name 'フィールド名
Debug.Print fld.DefaultValue '既定値
End If
Next fld
End If
End With
Next tdf
End Sub
- フィールド追加属性のSQL文の生成と発行
上記で作った主キー／インデックス／既定値の取得コードを統合し、それらの追加属性の設定を一気に行うプログラムが下記になります。
上記ではイミディエイトウィンドウに出力していましたが、その部分を「ALTER TABLE」や「CREATE INDEX」などのSQL文の生成処理に変更するとともに、SQL Serverに対してそれを発行しています。
Sub TrySample_3_4()
Dim dbs As Database
Dim tdf As TableDef
Dim idx As Index
Dim fld As Field
Dim strSQL As String
Dim strDefVal As String
Set dbs = CurrentDb
For Each tdf In dbs.TableDefs
With tdf
If .Attributes = 0 And .Name <> "tblテーブル構造" Then
'Indexesコレクションのループ
For Each idx In tdf.Indexes
If idx.Primary Then
'主キーのとき
strSQL = "ALTER TABLE " & .Name & " ADD PRIMARY KEY " & _
"(" & idx.Fields(0).Name & ")"
ElseIf idx.Unique Then
'固有インデックスのとき
strSQL = "CREATE UNIQUE INDEX " & idx.Name & " ON " & .Name & _
" (" & idx.Fields(0).Name & ")"
Else
'固有でないインデックスのとき
strSQL = "CREATE INDEX " & idx.Name & " ON " & .Name & _
" (" & idx.Fields(0).Name & ")"
End If
'SQL文を発行
ExecSQL strSQL
Next idx
'Fieldsコレクションのループ
For Each fld In .Fields
strDefVal = fld.DefaultValue
If Len(strDefVal) > 0 Then
'既定値があるとき
If strDefVal = "Yes" Then
strDefVal = "1"
ElseIf strDefVal = "No" Then
strDefVal = "0"
ElseIf strDefVal = "=Date()" Then
strDefVal = "CONVERT(date, GETDATE())"
End If
strSQL = "ALTER TABLE " & .Name & " ADD DEFAULT " & strDefVal & _
" FOR " & fld.Name
'SQL文を発行
ExecSQL strSQL
End If
Next fld
End If
End With
Next tdf
End Sub
Private Sub ExecSQL(strSQL As String)
'SQL Server上にSQL文を発行する
Dim dbs As Database
Dim strConStr As String
strConStr = "ODBC;DRIVER=SQL Server;" & _
"SERVER=NAFUREI;" & _
"DATABASE=UpSizeTest;" & _
"Trusted_Connection=Yes;"
On Error GoTo Err_Handler
'SQL Server上のUpSizeTestデータベースに接続
Set dbs = OpenDatabase("", False, False, strConStr)
'SQL文をSQL Serverに発行
dbs.Execute strSQL, dbSQLPassThrough
Exit_Here:
Exit Sub
Err_Handler:
MsgBox "エラー番号：" & Err.Number & vbCrLf & vbCrLf & _
"エラー内容：" & Err.Description, vbOKOnly + vbCritical
Resume Exit_Here:
End Sub
- ”既定値”に関しては、フィールドやインデックスとは違い、AccessとSQL Serverの違いを認識しておく必要があります。
たとえばYes/No型の既定値の「Yes/No」や「True/False」はSQL Serverでは「1/0」で指定する必要があります。また今日の日付を既定値にする場合の「Date」関数は「CONVERT(date, GETDATE())」というSQL Serverの関数式に変換する必要があります。
今回はあらかじめAccess側の既定値が分かっていますので上記のように強引に変換することができますが、汎用性はありません。特にAccess独自の関数式が設定してある場合には変換式も膨大になりますので、汎用性を求めたプログラムでの自動化するよりも手動での移行の方が確実かもしれません。
- 上記のプログラムで使っているSQL文は各設定を新規作成（CREATE）もしくは追加（ADD）するものです。すでにそれがSQL Server上にある場合は「dbs.Execute strSQL」の行でエラーとなります。
実行結果の例：
- ”既定値”に関しては、フィールドやインデックスとは違い、AccessとSQL Serverの違いを認識しておく必要があります。
