こんにちは！なるーらぼです！
週末に大変なことが起きましたね…

日ごろからの準備が必要だ、ということはわかっていても、うまくはいかないものです。
それでも、地震が起きる前にカセットコンロやガスを準備（できれば2セットあるとよいそうです）、クルマへはガソリンを空にしないようにしておくとか、地震がおきたらお風呂に水を張っておくとか…

Accessデータベース

きょうはAccessデータベースをつかいます。持っている方はそのままで結構ですが、どうやらわたしは持ってなかったようです！！（書き始めてから気づきました！！）

そういう方はランタイムが配布されているので、こちらからダウンロードするといいでしょう。

Microsoft Access 2013 Runtime

わたしもランタイムであります。

Access持ってなくてもあそべるのか？という質問があるとすると「Yes」であります。先ほどのランタイムさえ入れておけばたいていのことはできます。なにしろ、Accessそのものを利用せずにAccessを利用したソリューションを配布するためのものですから。

データベースファイルをつくる

まずは参照設定からです。データベースファイルをつくるには「Microsoft ADO Ext x.x for DDL and Security」を参照する必要があります。手元では「x.x」のところは「6.0」でした。

あとは変数として宣言して、ファイルをつくります。ユーザーフォルダへ「aaa.accdb」というデータベースファイルをつくるとしたら次のようになります。

Sub note_mu()
　Dim a As New ADOX.Catalog
　a.Create("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Environ("UserProfile") & "\aaa.accdb;")
End Sub

これで「aaa.accdb」というファイルが作成されたはずです。ただ、このコードは2度目は失敗します。というのも、すでに存在するファイルを上書きすることがないためです。そこで、Dir関数などで存在を確認して作成するようにすれば大丈夫です。

作成したら、今度はデータベースファイルを開いてみたいですよね。次は参照設定から「Microsoft ActiveX Data Objects x.x Library」を参照します。手元では「x.x」のところは「6.1」でした。

Sub note_mu()
　Dim a As New ADOX.Catalog
　Dim mdb As New ADODB.Connection
　Dim constr As String
　Dim dbpath As String
　'' データベース接続用
　dbpath = Environ("UserProfile") & "\aab.accdb"
　constr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbpath & ";"
　'' データベースファイルがなければつくり、
　'' あれば開きます
　If Dir(dbpath, vbNormal) = "" Then
　　Set mdb = a.Create(constr)
　Else
　　mdb.Open constr
　End If
　'' 接続を切っておきましょう
　mdb.Close
End Sub

あっという間に長くなってしまいました。これでデータベースを開いて閉じることができるようになりました。

次はテーブルをつくりたいですね。

mdb.Execute "create table MyTable (id number not null primary key, name text)"

これでIDと名前が項目であるテーブル「MyTable」が作成されます。しかし、これを書き加えると2度目の実行では失敗します。これまた同じ名前のテーブルをつくることができないからです。

ということでテーブルが存在するかどうかを調べることになるのですが、最初にデータベースを開いたときのオブジェクトを利用することができます。「カタログ」というオブジェクトなのですが、なんのカタログなのかというと「データベースの」です。

そこで、このカタログオブジェクトがもつ「Tablesプロパティ」が返すコレクションから、名前が「MyTable」になっているものを探して、あればTrueを返す関数をつくります。

Function hasTable(db As ADOX.Catalog, name As String) As Boolean
　Dim tbl As Table
　hasTable = False
　For Each tbl In db.Tables
　　If tbl.name = name Then
　　　hasTable = True
　　　Exit For
　　End If
　Next
End Function

この関数はカタログオブジェクトとテーブル名を引数にとります。与えられたカタログからTablesコレクションをみていって、引数で与えられた名前のテーブルがあったらTrueを返すようにしています。

これを先ほどのコードに加えてみます。

'' テーブルがなければテーブルをつくります
'' 項目が２つ、「ID,名前」という表になります。
If Not hasTable(a, "MyTable") Then
　mdb.Execute "create table MyTable (id number not null primary key, name text)"
End If

これで実行してみても、やはり同じエラーになります。でもファイルを新規作成したときはエラーになりません。なぜでしょうか？
これは、ファイルが既に存在していたときにのみ起こる問題で、新規作成していないのでどのデータベースの話をしているのか、カタログオブジェクトが知らないために起こります。ですから、どのデータベースなのか知らないときは先ほど追加した「hasTable」関数は常にFalseを返してしまうのです。

ということで、カタログオブジェクトにどのデータベースの話をしているのか知ってもらうことにします。データベースを開いたコードのすぐ後に次のように追記します。

Set a.ActiveConnection = mdb

これで開いたデータベース接続とカタログが紐づけされます。

次は初期データを放り込みたいと思います。

mdb.Execute "insert into MyTable values (1, 'nalulabo')"

こんな感じで登録したいのですが、2度目はやはり失敗します。

これはなぜかというと、テーブルを作成したときにIDという項目は重複しないものなんだという意味の指定（primary key）をしているからです。
このため、2度目は登録することができません。

ではデータをすべて選択して、件数がゼロなら初期データを登録するようにしてみます。まずデータを受け取るためのレコードセットオブジェクトを宣言しておきます。

Dim rs As New ADODB.Recordset

そして、すべてのデータを選択してみます。

Set rs = mdb.Execute("select * from MyTable")

レコードセットオブジェクトには「RecordCountプロパティ」があるので、このプロパティがゼロのときに初期データを登録するようにします。

If rs.RecordCount = 0 Then
　mdb.Execute "insert into MyTable values (1, 'nalulabo')"
　mdb.Execute "insert into MyTable values (2, 'm0t0k1')"
　mdb.Execute "insert into MyTable values (3, 'm0t0k1x2')"
End If

しかし今度は初期データの登録がされません。なぜでしょうか？
「ADODB.Connection」でつくった接続は、何も指定しなければサーバー用になっています。そうするとRecordCountプロパティは常に「-1」を返すというつくりになっているのです。そこで、クライアントですからねを明示してあげるようにすると件数を返してくれるようになります。

'' クライアントなのでクライアントカーソルにします
mdb.CursorLocation = adUseClient

これを追記してやれば件数が正しく返されるのでさきほどのコードで初期データが利用できます。

データの取り出し

では最後にデータの取り出しもしてみます。レコードセットオブジェクトは選択したデータがExcelシートのごとく行単位のコレクションになっています。コレクションを最後までみていくにはループでEOFプロパティがTrueになるまで繰り返すといいでしょう。

さらに、各データ項目はFieldsプロパティが返すコレクションにFieldオブジェクトとして詰め込まれています。テーブルの項目名が分かる場合はワークシートオブジェクトを指定するように

rs.Fields("id").Value

という感じでデータを取り出すことができます。
そして次の行へみているところを移動するには「MoveNextメソッド」を利用します。そうしないと、無限ループになってしまいますのでご注意ください。

なお、データベーステーブルの各項目はセットされていなくてもよいものもあり、そのときはNullという「何もない」を表す値がセットされています。トラブルの原因になりますので、IIf関数を使ってNullかどうかチェックして、Nullなら適切な値を埋めてあげるといいでしょう。

IIf(isNull(rs.Fields("name").Value, "null", rs.Fields("name").Value)

上記のコードは「name」という項目のデータがNullのときは「null」という文字列を、そうでなければデータを返します。

最後に

今回はAccessを持っていなくてもデータベースとしてあそべることをご紹介しました。カタログオブジェクトはかなり便利で、クエリやビューといったようなものも操作できるので相当あそぶことができます。

今回のコードも完全なものをアップしておきますので、ご参考まで。

https://gist.github.com/nalulabo/dc19bcd37124c3b747ae5a5f2113949f

ではまた、良い一日を！