エクセルExcel大事典 VBAマクロ応用講座 ADO 接続 MDB アクセスAccess

GEN MUTO'S HOMEPAGE　＞　エクセル大事典　＞　エクセルVBAを極める

　■ エクセルVBAを極める－VBAスーパーテクニック

アクセスのテーブルに接続してみよう！

　
■ MDBファイルに接続してみよう！
　
前回はExcelのワークシートにADOを使用して接続しましたね。今回は、AccessのMDBファイルに同じくADOを使用して接続してみましょう。
コードはほとんど前回と同じです。MDBファイルによる簡易データベースはほとんど費用もかからず簡単にクライアントサーバーシステムを実現します。

データベーステーブル専用のMDBファイルをファイルサーバーの共有ディレクトリにおき、クライアントPCからアクセスさせます。クライアントPC数が5台以下の小規模なオフィスの場合、ほとんどこれだけで間に合ってしまうことが多いです。
ただし、MDBファイルはRDBMSとは違います。トランザクションログもなにもとっていないため、万一MDBファイルが破損した場合、バックアップした時点の状態にしか戻せません。
あくまで小規模オフィスの簡易的なデータベースと捉えるべきでしょう。

まず最初にアクセスのMDBファイルを用意する必要があります。
Accessから新規のMDBファイルを作成し、"test.mdb"という名前で"test.xls"と同じ場所においてください。

次にテーブルの作成です。オブジェクトタブからテーブルを選択し、新規作成をクリックします。
新しいテーブルウィンドウでデザインビューを選択します。

テーブルデザインの画面が表示されるのでフィールド名にSyainID、SyainNAME、Kinzoku、Shozoku、Yakusyokuを数値型、テキスト型、数値型、テキスト型、テキスト型でそれぞれ定義します。
し終わったらデザインビューを閉じてください。

おっとその前に大事なことを忘れてた。SyainIDのフィールドにカーソルを移動し、ツールバーから
主キー（鍵のアイコン）をクリックしてください。テーブルには基本的にひとつから複数のフィールドで構成される主キーが存在します。

主キーは通常、ユニークで重複を許可しません。同じSyainIDが入力されたときテーブルそのものが重複をチェックしてくれます。便利でしょ。
さてデザインビューを閉じると保存する名前を聞いてきますので"SyainMST"を入力してください。

これでテーブルが用意できました。サンプルレコードを数件入れておきましょう。
ウィンドウにSyainMSTテーブルが新規に作成されていると思いますので、ダブルクリックしてください。
エクセルのワークシートのようなものが出てきました。列の項目名にさきほど定義した項目名がでていますね。ここに直接データを入力しちゃいましょう。データは数件で十分です。

入力し終わったらこのウィンドウも閉じ、Accessも終了させましょう。
さあ下準備OKです。test.xlsからtest.mdbに接続してみましょう。

' フォームモジュールに記述ここから－－－－－－－－－－－－－－－－－－－－－－－－－
Dim CN As ADODB.Connection

Private Sub UserForm_Initialize()
　　On Error GoTo ErrGyo
　　Set CN = New ADODB.Connection
　　　　CN.Provider = "Microsoft.Jet.OLEDB.4.0"
　　　　CN.Open ThisWorkbook.Path & "\test.mdb"
　　Call ClearLabel
　　BtnOK.Caption = "OK"
Exit Sub
ErrGyo:
　　MsgBox "SyainMSTへの接続に失敗しました", vbCritical
　　BtnOK.Enabled = False
End Sub

Private Sub BtnOK_Click()
Dim RS As ADODB.Recordset
Dim SQL As String
　　Call ClearLabel
　　If ChkSyainID(TxtID) Then
　　　　SQL = "SELECT * FROM SyainMST WHERE SyainID = " & TxtID
　　　　Set RS = New ADODB.Recordset
　　　　　　RS.Open SQL, CN, adOpenStatic, adLockReadOnly
　　　　If Not RS.BOF Then
　　　　　　LblName = RS.Fields("SyainNAME")
　　　　　　LblKinzoku = RS.Fields("Kinzoku")
　　　　　　LblSyozoku = RS.Fields("Syozoku")
　　　　　　LblYakusyoku = RS.Fields("Yakusyoku")
　　　　Else
　　　　　　MsgBox "入力された社員IDは存在しません", vbCritical
　　　　End If
　　　　Set RS = Nothing
　　Else
　　　　MsgBox "社員IDが有効ではありません", vbCritical
　　End If
End Sub

Private Sub UserForm_Terminate()
　　Set CN = Nothing
End Sub
' フォームモジュールに記述ここまで－－－－－－－－－－－－－－－－－－－－－－－－－

前回とほとんど同じですね。ChkSyainID関数とClearLabel関数は割愛してあります。
標準モジュールにも変化はありません。

まず接続部ですが
CN.Properties("Extended Properties") = "Excel 8.0" の拡張プロパティが無くなっていますね。前回はExcelのワークシートに接続するためにこのプロパティを使っていました。今回はMDBへの接続なので必要ありません。
CN.Open ThisWorkbook.Path & "\test.mdb" では接続するMDBファイルのパスとファイル名を指定しています。
ThisWorkbook.Path でエクセルブックのあるフォルダを返します。test.mdbは同じフォルダ内にあるのであとはファイル名だけ教えてやればいいですね。もちろんフルパス名での指定も可能です。

ちなみにこの接続部のコードこのようにも書けます。
CN.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
　　　　　　　　　　　　　　　　　　"Data Source=" & ThisWorkbook.Path & "\test.mdb"
CN.Open

ConnectionString プロパティにあらかじめ接続文字列をセット。あとはオープンメソッドで接続します。
やっている内容に違いはありません。

あとSQL文字列が変わっていますね。
SQL = "SELECT * FROM SyainMST WHERE SyainID = " & TxtID 前回は [SyainMST$] でシート名を指定していましたが、テーブルを指定するときは直接テーブル名を記述すればＯＫです。

ちなみSELECTのあとの"*"はすべての項目を表します。セットする項目を指定するときは、
SyainID, SyainNAME, Kinzoku とカンマで区切って項目名を列挙すればＯＫです。

FROM句のあとに取得先のテーブル名を、WHERE句に条件を記述します。たとえば SyainID > 2000 とすればSyainIDが2000を超える社員IDのデータだけを取得してきます。比較演算子はIF文のときと同じものが使えます。
他の条件も紹介しておきましょう。

BETWEEN条件
（例）SELECT * FROM SyainMST WHERE SyainID BETWEEN 1000 AND 2000
指定した2つの値の間にあるデータを取得します。
（例）では社員IDが1000から2000の間にあるデータを取得します。

IN条件
（例）SELECT * FROM SyainMST WHERE Syozoku IN ('経理部')
指定した値のリストの中にあるデータを取得します。
（例）では所属が経理部のデータのみ取得します。

LIKE条件
（例）SELECT * FROM SyainMST WHERE SyainNAME LIKE '鈴木%'
文字の検索条件を指定します。ワイルドカードで"%"と"_"が使用できます。"%"は「任意の文字数の任意の文字」を"_"は「1文字分の任意の文字」を表し、組み合わせて使用することが可能です。
（例）では名前の先頭に鈴木を含む全てのデータを取得します。

SQL文にはまだまだ沢山の記述法が存在します。すべてを一度に紹介するのは難しいので徐々に紹介していきたいと思います。
次回はSQLサーバーへの接続編です。

　

＞＞次の話にすすむ

サイト内検索ができます

Microsoft Most Valuable Professional

Microsoft MVP Excel
武藤玄プロフィール
　

■■■ 執筆した書籍 ■■■
　

EXCEL VBA 業務自動化
　

 Excel VBA を５日でマスターする本
　

 仕事を速くする Excel VBA 入門
　

 Excel VBAと業務改善のポイントがわかる本
　

 Access VBA ベーシックスタンダード
　

 Excel VBA アクションゲーム作成入門
　

■■■ 連載中の記事 ■■■
　

Excel VBAで業務改善！
　

実務で使うExcel VBA
　

■ ＴＯＰページ

■ エクセルVBA超入門

　・９ステップで"Hello World!"
　　からオブジェクト指向まで！
　　1."Hello World!"を表示しよう
　　2.基本はデータの入出力
　　3.繰り返し処理
　　4.シートで簡易データベース
　　5.名前をつけよう
　　6.構造化プログラムに挑戦
　　7.構造体を使ってみよう
　　8.フォームを使ってみよう
　　9.オブジェクト指向に挑戦

エクセルExcel大事典ははリンクフリーです
お問い合わせはこちら gengengen@yahoo.co.jp