#56
Access2010までは「アップサイジングウィザード」というツールが付属しており、テーブルなどを簡単にSQL Server上に移行することができました。しかしAccess2013以降その機能はなくなったため、Accessだけでアップサイズはできません。
その代替えとして別途「SQL Server Migration Assistant for Access」というツールを入手して使うこともできるのですが、今回はAccess VBAからSQL Server上のオブジェクトを操作するにはどのようなプログラムを組めばよいのか、その調査の意味で、アップサイズに関する簡単なプログラムを試作してみました。
まずは最初のステップとして、DAOを使ったプログラムでSQL Serverに接続してデータベースを新規作成するところまでやってみます。
SQL Serverへの接続
SQL Serverへ接続する場合の認証方法には、「Windows認証」と「SQL Server認証」とがあります。まず次のプログラムでWindows認証で接続してみます。
ここでは、SQL Server名は「NAFUREI」、また既存のデータベースに接続するわけではなく単にSQL Serverに接続するだけなので、データベース名は空とします（実際にはデフォルトのデータベースに接続しようとします）。「Trusted_Connection=Yes;」を接続文字列に付加することでWindows認証での接続となります。
Sub TrySample_1_1()
Dim dbs As Database
Dim strConStr As String
strConStr = "ODBC;DRIVER=SQL Server;" & _
"SERVER=NAFUREI;" & _
"DATABASE=;" & _
"Trusted_Connection=Yes;"
On Error GoTo Err_Handler
Set dbs = OpenDatabase("", False, False, strConStr)
MsgBox "接続に成功しました！", vbOKOnly + vbInformation
Exit_Here:
Exit Sub
Err_Handler:
MsgBox "エラー番号：" & Err.Number & vbCrLf & vbCrLf & _
"エラー内容：" & Err.Description, vbOKOnly + vbCritical
Resume Exit_Here:
End Sub
次に「SQL Server認証」で接続してみます。SQL Server認証の場合には、所定のユーザーID（UID）とパスワード（PWD）を接続文字列に付加します。
Sub TrySample_1_2()
Dim dbs As Database
Dim strConStr As String
strConStr = "ODBC;DRIVER=SQL Server;" & _
"SERVER=NAFUREI;" & _
"DATABASE=;" & _
"UID=hoshino;PWD=hoshino;"
On Error GoTo Err_Handler
Set dbs = OpenDatabase("", False, False, strConStr)
MsgBox "接続に成功しました！", vbOKOnly + vbInformation
Exit_Here:
Exit Sub
Err_Handler:
MsgBox "エラー番号：" & Err.Number & vbCrLf & vbCrLf & _
"エラー内容：" & Err.Description, vbOKOnly + vbCritical
Resume Exit_Here:
End Sub
上記のプログラムにおいて、SQL Server名が間違っているようなとき、あるいは接続できないとき、「OpenDatabase」のメソッドの行でエラーになりそうですが、実際にはそうはなりません。次のようなダイアログが表示されます。
ここで、SQL Server名や、[オプション＞＞]で表示されるデータベース名などを正しく入力して[OK]ボタンをクリックすれば、そのまま次のコードが続行されます。また[キャンセル]ボタンをクリックしたときにはじめてこのプログラムとしてのエラーが発生し、エラールーチンへ処理が飛びます。
一方、エラーが起こらなかっとしても、本当に正しく接続されているかはっきりとは分かりません。そこで、MsgBoxの行にブレークポイントを設定して、その時点のDatabaseオブジェクト変数である「dbs」の値をチェックしてみます。
プロシージャを実行してプログラムが一時停止したところで「ローカル」ウィンドウで「dbs」を見てみると、そこに含まれるテーブルの名前からデフォルトである「master」システムデータベースが開かれていることが分かります。
- データベースの新規作成
SQL Serverに接続できたところで、空の新規データベースを生成してみます。
それにはSQLとして「CREATE DATABASE」文を発行します。ここでは「UpSizeTest」というデータベースを作ってみます。
Sub TrySample_1_3()
Dim dbs As Database
Dim strConStr As String
Dim strSQL As String
strConStr = "ODBC;DRIVER=SQL Server;" & _
"SERVER=NAFUREI;" & _
"DATABASE=;" & _
"Trusted_Connection=Yes;"
On Error GoTo Err_Handler
Set dbs = OpenDatabase("", False, False, strConStr)
strSQL = "CREATE DATABASE UpSizeTest"
dbs.Execute strSQL
Exit_Here:
Exit Sub
Err_Handler:
MsgBox "エラー番号：" & Err.Number & vbCrLf & vbCrLf & _
"エラー内容：" & Err.Description, vbOKOnly + vbCritical
Resume Exit_Here:
End Sub
しかしこれを実行してみると、問題発生です。非常にシンプルなSQL文にも関わらず、「CREATE DATABASE ステートメントの構文エラー」が発生します。
「CREATE DATABASE」文の基本構文は「CREATE DATABASE データベース名」ですが、データベース名以外にもいろいろオプションがあります。その中の大事なものが未指定なのが原因かと思い、Management Studio上で実行してみても、それだけで正しくデータベースが生成されました。
そこで今度はエラーが発生している「dbs.Execute strSQL」のコードに目を向け、「Execute」メソッドのオプションを調べてみます。
このメソッドにもいろいろオプションがあるのですが、対SQL Serverとして見たときに関連しそうなものとして「dbSQLPassThrough」という組み込み定数があります。今回はDAOからのODBC経由の接続でありSQL発行でもあるので、SQL文をパススルークエリとして送出するこの指定は関係ありそうです。
そこでプログラムを次のように変更してみます。
Sub TrySample_1_4()
Dim dbs As Database
Dim strConStr As String
Dim strSQL As String
strConStr = "ODBC;DRIVER=SQL Server;" & _
"SERVER=NAFUREI;" & _
"DATABASE=;" & _
"Trusted_Connection=Yes;"
On Error GoTo Err_Handler
Set dbs = OpenDatabase("", False, False, strConStr)
strSQL = "CREATE DATABASE UpSizeTest"
dbs.Execute strSQL, dbSQLPassThrough
Exit_Here:
Exit Sub
Err_Handler:
MsgBox "エラー番号：" & Err.Number & vbCrLf & vbCrLf & _
"エラー内容：" & Err.Description, vbOKOnly + vbCritical
Resume Exit_Here:
End Sub
今度はエラーなしで処理できました、Management Studioで確認してみると、確かに「UpSizeTest」データベースが追加されています。
アップサイズの作業ではAccess側からODBC経由でSQL文を発行する機会は多々あるので、この「dbSQLPassThrough」定数のオプション指定は欠かせないことが分かります。
- 既存データベースを開く
Accessのテーブルのアップサイズ先となるデータベース「UpSizeTest」ができましたので、その確認も含めて、SQL Serverに接続するとともにそのデータベースを直接開くプログラムを作ってみます。
それには、TrySample_1_1では未指定だった「データベース名（DATABASE）」を接続文字列に明記するだけです。
Sub TrySample_1_5()
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
Set dbs = OpenDatabase("", False, False, strConStr)
MsgBox "接続に成功しました！", vbOKOnly + vbInformation
Exit_Here:
Exit Sub
Err_Handler:
MsgBox "エラー番号：" & Err.Number & vbCrLf & vbCrLf & _
"エラー内容：" & Err.Description, vbOKOnly + vbCritical
Resume Exit_Here:
End Sub
今後のアップサイズのトライアルではこのデータベース上にテーブルを作成したりしますが、上記コードのDatabaseオブジェクト「dbs」に対してそのSQL文を発行するケースが多々あるはずです。その際、上記のコードが処理の最初に行う基本形となります。
