データベース管理ソフトウェア、Accessを実務で使うときに「知っているとちょっと便利なコツ」について紹介するこのコラム。今回は、Excelと連携させることの最大のメリットをご紹介します。あわせて知っておきたい、悪意のある第三者にどう対応するかのセキュリティ面についても解説。最終回となる今回のお話しも、あなたのAccess力アップに役立ちますように。
前回のコラムを見逃した方はこちらからご覧ください。
第5話 VBA内でSQLを扱うコツ
ExcelとAccessを連携させると…？
Excelの次のステップアップとしてAccessにチャレンジしてみよう、という気持ちで始める方も多いことと思います。データベースの構造を知ることで効率の良いデータの管理方法がわかり、結果的にExcelを活用する力も向上するはずです。
しかし、AccessはExcelの上位互換というわけではありません。Excelはグラフ化や視覚的な分析が得意ですが、入力の自由度が高すぎるためにデータを整然と保つのが苦手です。対してAccessはデータ管理のスペシャリストですが、Excelほどに自由度の高いUIやグラフ化は苦手なのです。
最終回である今回は、そんなお互いの苦手な部分を補完し合うと便利な例をご紹介いたします。
Excelにデータを持たなくていい
連携させることの最大のメリットは、Excelでデータの管理をしなくてもよくなることです。管理はAccessにまかせて、Excel側では必要なときに必要なデータだけ取り出して表やグラフに活用すれば、お互いのソフトの「いいとこ取り」でスマートに使うことができます。
Accessが苦手なユーザーにも受け入れられやすい
Microsoft Officeシリーズの仲間とはいえ、一般ユーザーからするとAccessの認知度は高くありません。慣れ親しんだExcelのシートだと心理的な抵抗感が少ないユーザーも多くいらっしゃるので、UI部分をExcelで作るのも、ユーザーの「使いやすい」と感じる1つの要素になり得ます。
読み込み即グラフ化が可能
たとえば以下のようなExcelシートがあるとします。上部のグラフは下部の表に紐付いており、表に数値が入ると棒グラフが描写される設定になっています。
このシートのB2セル（年）を変更してマクロを実行すれば、その年に応じたデータが読み込まれてグラフが描写される、なんて素敵だと思いませんか？そんなことが可能なんです。
読み書きのテンプレート
それでは、Accessのテーブルから読み書きするためのテンプレートを任意のExcelファイル（.xlsm）のVBEに書いていきます。
今回は参照設定の「Microsoft ActiveX Data Objects X.X Library」にチェックを入れずにADOを利用するコードでご紹介します。詳しくは第4回の記事も合わせてご覧ください。
以下はExcelからAccessのファイルを指定して接続したり、接続解除したりする部分を切り出してプロシージャ化したものです。何度も使われる部分になるので、こうして部品化しておくと取り回しが楽になります。
上の3行がモジュールの先頭（宣言セクション）に書かれるように、新しい標準モジュールにコピー&ペーストし、Accessのパスは任意のものへ変更してください。
Private cn As Object 'ADOコネクション用オブジェクトの宣言
Private Const adStateOpen = 1 'レコードセットが開いている場合の設定値
Private Const adOpenKeyset = 1 'レコードセットカーソルタイプの設定値
Private Sub connectDB()
'## 接続
Set cn = CreateObject("ADODB.Connection") 'ADOコネクションを作成
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=C:\data.accdb;" 'Accessファイルを指定してコネクションを開く
End Sub
Private Sub disconnectDB()
'## 接続解除
If Not cn Is Nothing Then
cn.Close
Set cn = Nothing
End If
End Sub
以下はレコードの抽出で使用するテンプレートです。引数のSQLによってAccessから抽出されたレコードを、Excelの指定シート・セル起点で展開します。上の接続/接続解除のプロシージャと同じモジュールに書いてください。
Public Sub putRsOnSheet(ByVal sql As String, ByVal ws As Worksheet, ByVal rng As Range)
'## レコードセットをシートへ展開
On Error GoTo Err_Handler 'エラーが起きたら「ErrorHandler」にジャンプする指示
Call connectDB '接続
'レコードセットのオープン
Dim rs As Object 'レコードセット用変数宣言
Set rs = CreateObject("ADODB.RecordSet") 'ADOレコードセットオブジェクトを作成
rs.Open sql, cn 'レコードセットを開く
'空だったら最終処理へ
If rs Is Nothing Or (rs.BOF And rs.EOF) Then 'レコードセットやレコードが存在しなかった場合
MsgBox "対象レコードがありません", vbInformation, "確認" 'メッセージ出力
GoTo Finally '最終処理へジャンプ
End If
'シートに展開
Dim tgtRow As Long
tgtRow = rng.Row 'スタートの行
Dim tgtCol As Long
tgtCol = rng.Column 'スタートの列
Application.ScreenUpdating = False '画面更新OFF
Do Until rs.EOF 'レコードセットが終了するまで処理を繰り返す
Dim fldNum As Long 'フィールドの繰り返し用変数
For fldNum = 0 To rs.Fields.Count - 1 'フィールドの数だけ繰り返す
ws.Cells(tgtRow, tgtCol + fldNum).Value = rs(fldNum) 'フィールドの並び順でセルに書き込む
Next fldNum '次のフィールドへ
tgtRow = tgtRow + 1 '行をカウントアップする
rs.MoveNext '次のレコードに移動する
Loop
Application.ScreenUpdating = True '画面更新ON
GoTo Finally '正常に終了したら最終処理へジャンプ
Err_Handler: '例外処理
Dim msgTxt As String
msgTxt = "Error #: " & Err.Number & vbNewLine & vbNewLine & Err.Description 'エラーメッセージが入る
MsgBox msgTxt, vbOKOnly + vbCritical, "エラー" 'メッセージ出力
Finally: '最終処理
If Not rs Is Nothing Then 'レコードセットオブジェクトが存在している場合のみ
If rs.State = adStateOpen Then rs.Close 'レコードセットが開いていたら閉じる
Set rs = Nothing
End If
Call disconnectDB '接続解除
End Sub
以下はレコードの挿入・更新・削除で使用するテンプレートです。引数のコレクション内に格納されているすべてのSQLを実行します。上の接続/接続解除のプロシージャと同じモジュールに書いてください。
Public Function tryExecute(ByVal sqlList As Collection) As Boolean
'## SQLの実行
On Error GoTo ErrorHandler 'エラーが起きたら「ErrorHandler」にジャンプする指示
Call connectDB '接続
cn.BeginTrans 'トランザクション開始
'実行
Dim sql As Variant
For Each sql In sqlList 'SQL文リストをループ
cn.Execute sql '1行ずつ実行
Next sql
cn.CommitTrans '確定
tryExecute = True '成功だった場合、関数の結果にTrueを入れる
GoTo Finally '正常に終了したら最終処理へジャンプ
ErrorHandler: '例外処理
cn.RollbackTrans '元の状態へ戻す
Dim msgTxt As String
msgTxt = "Error #: " & Err.Number & vbNewLine & vbNewLine & Err.Description 'エラーメッセージが入る
MsgBox msgTxt, vbOKOnly + vbCritical, "エラー" 'メッセージ出力
Finally: '最終処理
Call disconnectDB '接続解除
End Function
テンプレートの利用（レコードをExcelシートに展開）
SQLのSELECT構文を作成し、起点とするシート・セルと共に引数としてテンプレートへ渡すコードがこちらです。
Public Sub loadRecord()
'## レコードをシートへ
'対象シートを設定
Dim ws As Worksheet
Set ws = Sheets("シート名")
'SQL文の作成
Dim sql As String
sql = "任意のSELECT文"
'レコードセットをシートへ展開
Call putRsOnSheet(sql, ws, ws.Range("A1"))
End Sub
実行すると、指定のセルを起点にレコードセットが展開されます。
さきほど例に出した、グラフが設定されているシートに展開するコードだったら、以下のように書きます。SQLではPIVOTを使ったクロス集計を行っています。
（接続するAccessファイルに「販売」テーブルが存在し、その中に「商品ID（文字列型）」「売上日（日付型）」「数量（数値型）」フィールドがある想定です）
Public Sub loadRecord()
'## レコードをシートへ
'対象シートを設定
Dim ws As Worksheet
Set ws = Sheets("集計")
'指定年取得
Dim tgtYear As Long
tgtYear = ws.Range("B2").Value
'SQL文の作成
Dim sql As String
sql = _
"TRANSFORM Sum(数量) " & _
"SELECT 商品ID " & _
"FROM 販売 " & _
"WHERE Format(売上日,'yyyy') In ('" & tgtYear & "')" & _
"GROUP BY 商品ID " & _
"PIVOT Format(売上日,'m') In ('1','2','3','4','5','6','7','8','9','10','11','12');"
'レコードセットをシートへ展開
Call putRsOnSheet(sql, ws, ws.Range("B16"))
End Sub
実行すると以下のようにデータが展開され、自動的にグラフが描写されます。
B2セルの値を変更してプログラムを実行し直すことで、その年のデータを月別に読み込むことができます。SQLを工夫すれば、年月を指定して日別のグラフにすることも可能です。
テンプレートの利用（レコードの挿入・更新・削除）
SQLのINSERT/UPDATE/DELETE構文をコレクションに格納し、引数としてテンプレートへ渡して結果をTrue/Falseの形で受け取るコードがこちらです。
Public Sub executeSQL()
'## レコードの挿入・更新・削除
'コレクションの作成
Dim sqlList As Collection
Set sqlList = New Collection
'SQL作成
Dim sql As String 'SQL文用変数の宣言
sql = "任意のINSERT/UPDATE/DELETE構文" '1つ目のSQL文
sqlList.Add sql 'コレクションへ追加
sql = "任意のINSERT/UPDATE/DELETE構文" '2つ目のSQL文
sqlList.Add sql 'コレクションへ追加
'実行
If tryExecute(sqlList) Then '処理が成功した場合
MsgBox "正常に終了しました", vbOKOnly + vbInformation, "終了" '終了メッセージ
End If
End Sub
実行すると、問題が起きた場合は処理されずにエラー内容が、問題なく処理が終了した場合は終了メッセージが表示されます。
SQLにExcel上のセルの値を組み込めば、自由度の高いシステムを作ることができます。
VBAでプレースホルダーを使う
ユーザーが入力した値をそのままSQLに組み込んでデータベースを操作する場合、さらにその入力する人間にSQL知識と悪意がある場合は、SQLインジェクション（不正なSQL文を実行させる攻撃）の恐れがあることに注意をしなければなりません。
Accessは個人～少人数向けの非公開が前提のデータベースソフトなので悪意のある第三者が介入する可能性は低いと思われますが、知識として知っておくことは大切です。
この問題に対しては、入力値を一旦別の場所に確保（プレースホルダー）しておいて、実行のときに無害化して使うのが一般的な手段で、VBAでも実装が可能です。
それでは最後に、VBAでプレースホルダーを使った書き方も見ておきましょう。
以下はレコードの抽出で使用するテンプレートです。レコードセットを取得してイミディエイトウィンドウへ出力する動きを、1つのプロシージャで完結する形で書いてあります。SQL条件の値に「？」を置き、プレースホルダーの値を配列型でパラメーターとして持たせます。
Public Sub loadRecord ()
'接続
Dim cn As Object 'ADOコネクション用オブジェクト
Set cn = CreateObject("ADODB.Connection")
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=C:\Data.accdb;" 'Accessファイルを指定してコネクションを開く
'ADOコマンドオブジェクトの設定
Dim cmd As Object
Set cmd = CreateObject("ADODB.Command")
cmd.ActiveConnection = cn
'ADOレコードセットオブジェクトの設定
Dim rs As Object
Set rs = CreateObject("ADODB.RecordSet")
'実行
cmd.CommandText = "SELECT * FROM table1 WHERE f1 = ? ;"
Set rs = cmd.Execute(Parameters:=Array("a"))
Do Until rs.EOF 'レコードセットが終了するまで処理を繰り返す
Dim fldNum As Long 'フィールドの繰り返し用変数
For fldNum = 0 To rs.Fields.count - 1 'フィールドの数だけ繰り返す
Debug.Print rs(fldNum), ; 'フィールドを改行せずイミディエイトウィンドウに出力
Next fldNum '次のフィールドへ
Debug.Print "" 'レコード切り替えでイミディエイトウィンドウを改行
rs.MoveNext '次のレコードに移動する
Loop
'レコードセットオブジェクトの処理
rs.Close 'レコードセットを閉じる
Set rs = Nothing
'コマンドオブジェクトの後処理
Set cmd = Nothing
'接続解除
cn.Close
Set cn = Nothing
End Sub
以下はレコードの挿入・更新・削除で使用するテンプレートです。1つのプロシージャで完結する形で書いてあります。「？」を複数使用する場合は、パラメーターの配列に順番に配置します。
Public Sub executeSQL()
'接続
Dim cn As Object 'ADOコネクション用オブジェクト
Set cn = CreateObject("ADODB.Connection")
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=C:\Data.accdb;" 'Accessファイルを指定してコネクションを開く
'ADOコマンドオブジェクトの設定
Dim cmd As Object
Set cmd = CreateObject("ADODB.Command")
cmd.ActiveConnection = cn
'実行
cmd.CommandText = "INSERT INTO table1(f1, f2, f3, f4) VALUES(?, ?, ?, ?);"
cmd.Execute Parameters:=Array(1, Now, "a", False)
'コマンドオブジェクトの後処理
Set cmd = Nothing
'接続解除
cn.Close
Set cn = Nothing
End Sub
Excelからデータベースを操作できると、仕事の可能性がぐんと広がります。対象のデータベースの規模が大きくなれば気をつけなくてはならないことも増えるので、セキュリティ面も勉強しながらステップアップを目指してくださいね。
以上で、連載「実務で使えるAccessのコツ」は終了です。この連載が、あなたのお仕事に役に立てたらとっても嬉しいです。ここまで読んでいただき、ありがとうございました！
第1話「イベント」を使いこなそう
第2話 レイアウトで躓きやすいポイント
第3話 マクロとVBAの違い
第4話 DAOとADOの違い
第5話 VBA内でSQLを扱うコツ
