#57
「アップサイジングウィザード」に代わるプログラムの２回目です。
今回は、アップサイズ対象となるAccessデータベース内のテーブル（ローカルテーブル）の情報を収集したうえで、それと同じ構造のテーブルをSQL Server上に生成するところまでトライしてみます。
なお、今回は主キーやインデックスなどの属性設定までは行わず、単純に同じ名前、同じデータ型のフィールドを作るところまでです。
テーブル一覧の取得
まずはAccessデータベース内にあるローカルテーブルの情報収集です。テーブルの一覧を取得するため、次のようなプログラムを作成します。
ここでは「TableDef」オブジェクトの「Attributes」プロパティが「0」であるものだけを対象とすることで、リンクテーブルや隠しテーブル、システムテーブルを除外しています。
Sub TrySample_2_1()
Dim dbs As Database
Dim tdf As TableDef
Set dbs = CurrentDb
For Each tdf In dbs.TableDefs
With tdf
If .Attributes = 0 Then
Debug.Print .Name
End If
End With
Next tdf
End Sub
- フィールド一覧の取得
ある特定のテーブルのフィールド一覧を取得するには、次のようなプログラムを作ります。これによってフィールド名とデータ型およびそのサイズを取得することができます。
Sub TrySample_2_2()
Dim dbs As Database
Dim tdf As TableDef
Dim fld As Field
Set dbs = CurrentDb
Set tdf = dbs.TableDefs("mtbl顧客マスタ")
With tdf
If .Attributes = 0 Then
For Each fld In .Fields
Debug.Print fld.Name, fld.Type, fld.Size
Next fld
End If
End With
End Sub
さらに、前述のテーブル一覧の取得で使ったコードと合わせることで、すべてのテーブルのフィールド情報を収集することができます。
またここでは、その結果を確認できるよう、イミディエイトウィンドウに出力して終わりではなく、「tblテーブル構造」というテーブルにその収集データを書き出すようアレンジを加えました。
Sub TrySample_2_3()
Dim dbs As Database
Dim tdf As TableDef
Dim fld As Field
Dim rst As Recordset
Dim strDataType As String
Dim varDataSize As Variant
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblテーブル構造")
For Each tdf In dbs.TableDefs
With tdf
If .Attributes = 0 And .Name <> "tblテーブル構造" Then
For Each fld In .Fields
varDataSize = Null
Select Case fld.Type
Case dbText
strDataType = "テキスト型"
varDataSize = fld.Size
Case dbMemo: strDataType = "メモ型"
Case dbByte: strDataType = "バイト型"
Case dbInteger: strDataType = "整数型"
Case dbLong
If fld.Attributes And dbAutoIncrField Then
strDataType = "オートナンバー型"
Else
strDataType = "長整数型"
End If
Case dbSingle: strDataType = "単精度浮動小数点型"
Case dbDouble: strDataType = "倍精度浮動小数点型"
Case dbDate: strDataType = "日付/時刻型"
Case dbCurrency: strDataType = "通貨型"
Case dbBoolean: strDataType = "Yes/No型"
Case Else: strDataType = "その他"
End Select
rst.AddNew
rst!テーブル名 = .Name
rst!フィールド名 = fld.Name
rst!データ型 = strDataType
rst!サイズ = varDataSize
rst.Update
Next fld
End If
End With
Next tdf
rst.Close
End Sub
実行結果の例：
- フィールド属性の取得
今回は、ローカルテーブルの構造に基づいてSQL Serverのデータベース上に「CREATE TABLE」文を使ってテーブルを生成します。
「CREATE TABLE」の”完全な構文”を使うと、主キーやインデックス、既定値、外部キー制約などもいっしょに指定できるのですが、今回は”もっともシンプルな構文”を使って必要最低限の指定でフィールドを生成するところまでとします（それ以外はあとからテーブル構造を変更するという形で行うこととします）。
その構文は次のようなものです。
この構文から、各フィールドについて[NULL|NOT NULL]、つまりNULL値を許容するかしないかをその属性として取得する必要があることが分かります。。NULL可否についてはAccessの場合「値要求」プロパティで取得できます。VBAでは「Required」で参照できます。そのプロパティ値が”True”ならNULL値を許可しない（NOT NULL）、”False”ならNULL値を許可する（NULL）です。
CREATE TABLE テーブル名 (
フィールド名1 データ型 [NULL|NOT NULL],
フィールド名2 データ型 [NULL|NOT NULL],
フィールド名3 データ型 [NULL|NOT NULL],
・・・・・・・・・・・・・・・・・・
)
それを踏まえて「TrySample_2_3」プロシージャのコードを変更すると次のようになります。
- ”ここから”〜”ここまで”の行範囲を追記しています。
- オートナンバー型の場合はRequiredプロパティに関係なく”値要求=はい”としています。Access上では自動的に番号が振られるのでデータ入力する必要がありませんが、かといってNULLで保存されることもありませんので、「CREATE TABLE」文では「NOT NULL」とする必要があるためです。
〜 前略 〜
rst.AddNew
rst!テーブル名 = .Name
rst!フィールド名 = fld.Name
rst!データ型 = strDataType
rst!サイズ = varDataSize
'------ ここから ------
If strDataType = "オートナンバー型" Then
rst!値要求 = True
Else
rst!値要求 = fld.Required
End If
'------ ここまで ------
rst.Update
〜 後略 〜
実行結果の例：
- CREATE TABLE文の生成と発行
Accessデータベースのテーブルやフィールドの情報が収集できたら、そこからSQL Severで有効な「CREATE TABLE」文を組み立てて、SQL Severに発行します。
その際、テーブル名やフィールド名は通常はそのまま使えますが、データ型に関しては置き換えが必要です。SQL Severで使用可能なすべてのデータ型ではありませんが、主だったものとしては下表のような関係になります。
Access SQLServer テキスト型 nvarchar型 メモ型 ntext型 バイト型 tinyint型 整数型 smallint型 長整数型 int型 単精度浮動小数点型 real型 倍精度浮動小数点型 float型 日付/時刻型 datetime型 通貨型 money型 Yes/No型 bit型 オートナンバー型 int型 ＋ IDENTITY
なお、AccessとSQL Serverは完全に一対一で対応しているわけではありません。たとえばテキスト型は上表では「nvarchar」としていますが、データに全角なしであれば「varchar」、すべてのデータの長さが同じであれば固定長の「char」を使った方が格納効率が高くなります。同様に、日付部分しか持たないデータであれば「DateTime」ではなく「Date」の方が効率的です。
したがって、上表はプログラムでシンプルに自動的に移行する場合、多少冗長であってもAccess側のデータ格納を優先した場合の置き換え方になります。
実際の「CREATE TABLE」文の生成と発行については、下記のようなプログラムを作成し実行します。
ここでは「TrySample_2_3」プロシージャで出力された「tblテーブル構造」テーブルのレコードセットを読み込んで処理するものとします。同じテーブル名が連続していますので、１レコードずつ読み込んでいき、その値が切り替わったらSQL文を発行するといったループ処理になります。
また、SQL Serverに１つのSQL文を発行する処理を「ExecCreateTable」プロシージャとして分けて作っています。
Sub TrySample_2_4()
Dim dbs As Database
Dim rst As Recordset
Dim strKeyTbl As String
Dim strDataType As String
Dim strSQL As String
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblテーブル構造")
With rst
Do Until .EOF
If strKeyTbl <> !テーブル名 Then
'テーブル名が切り替わったら１テーブル分を生成
If strKeyTbl <> "" Then
ExecCreateTable strSQL
End If
'テーブル名をキーに設定
strKeyTbl = !テーブル名
'SQL文を初期設定
strSQL = "CREATE TABLE " & strKeyTbl & "(" & vbCrLf
End If
'データ型を変換
Select Case !データ型
Case "テキスト型": strDataType = "nvarchar(" & !サイズ & ")"
Case "メモ型": strDataType = "ntext"
Case "バイト型": strDataType = "tinyint"
Case "整数型": strDataType = "smallint"
Case "長整数型": strDataType = "int"
Case "単精度浮動小数点型": strDataType = "real"
Case "倍精度浮動小数点型": strDataType = "float"
Case "日付/時刻型": strDataType = "datetime"
Case "通貨型": strDataType = "money"
Case "Yes/No型": strDataType = "bit"
Case "オートナンバー型": strDataType = "int IDENTITY(1,1)" '1から始まり1ずつ増分
Case Else: strDataType = "" 'その他は生成対象外とする
End Select
If strDataType <> "" Then
'１フィールド分をSQL文に追加
strSQL = strSQL & !フィールド名 & " " & strDataType & " " & _
IIf(!値要求, "NOT NULL", "NULL") & "," & vbCrLf
End If
.MoveNext
Loop
.Close
End With
'最後の１テーブル分を生成
If strKeyTbl <> "" Then
ExecCreateTable strSQL
End If
End Sub
Private Sub ExecCreateTable(strSQL As String)
'SQL Server上に１テーブル分を生成する
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文の末尾の余分な「,」を削除して「)」を追加
strSQL = Left(strSQL, InStrRev(strSQL, ",") - 1) & vbCrLf & ")"
'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 Severのテーブル構造
- Accessのテーブル構造
