カテゴリー: ExcelVBA

'テーブルを作成するSQL文を実行する
DBCone.Execute "CREATE TABLE テーブル(ID Long,日付 Date)"

データベースにSQL文を実行するには、Executeメソッドを使用します

このメソッドを使用する場合は、レコードの取得を行わないSQL文にする必要があります
UPDATE文やINSERT INTO文などになります

レコードを取得したり、関数の結果を取得したりする場合はADODB.Recordsetオブジェクトを利用します

ExcelVBAとしてはメソッドの実行を行うのみになりますが、引数に指定するSQL文は別の言語なので様々な指定を行うことが出来ます
今回の例コードではテーブルの作成を行っています
その際フィールドを2つ作成しています

実際、このコードをそのままコピペして使用することはほぼ出来ません
SQL文の内容が実際の使用場面と全く違うからです

ここではSQLの実行方法の解説なので例コードにあるSQL文についての詳しい解説は割愛させてもらいます

このメソッドの大きな利点は、データベースを丸ごと処理できることにあります
UPDATE文を使用すれば、データベース全体に編集を一括で行うことが出来ます
また、テーブルやビューの作成・削除などもこのメソッドから行います

ExcelVBAでデータベースを操作する場合は、テーブルの作成が必要です
テーブルの作成を行わずにレコード操作を行うことも出来ますが、それはまた別の話になってきます

ただ、問題があるとすればリアルタイムで処理の動きを確認できないところです
VBAのデバッグの様に、1行ずつ確認しながら作業を行えませんので、SQL文がどのような結果をもたらすかを最低限の範囲でイメージできていないと、データベース操作は難しく感じます

また、AccessSQLも有名で書籍もあふれているものではありません
ほかのSQLで使用できるものが使用できなかったりすることもありますので、実際に動きを確認しながら操作を進めるようにしてください

ちなみにINSERT INTO文はVBAから行うと非常に低速なので、ほぼ使うことはありません
ADODB.Recordsetオブジェクトを利用する方が比較対象にならない程に早いです

'操作用オブジェクトの作成
Dim zzDB_Rec As Object
Set zzDB_Rec = CreateObject("ADODB.Recordset")

'レコード操作処理

Set zzDB_Rec = Nothing

データベースの操作にはSQLを使用する方法と、VBAのオブジェクト利用（以下、ADO）を使用する方法の2通りがあります
どちらの操作方法を使用するにしてもレコードを取得する場合はオブジェクトの作成が必要になりますので例コードを使用してください

データベース操作なので、基本的にADOで出来ることはSQLで可能です
なので操作することだけを考慮すればSQLが優位ですが、実際のVBAでのコードの分かりやすさと単体レコードでの処理速度はADOが優位です
なので、どちらが完全に優位というものではありませんので、状況に合わせて使用を切り替えるようにしてください

切り替える基準の分かりやすい点として

• 単体のレコード操作を行う場合はADO
• 複数のレコード操作を行う場合はSQL

と認識して使用を切り分けてください

例えば、ワークシートにあるデータをデータベースに登録する場合に一括で登録することは出来ませんので1レコードずつ登録を行います
この場合にはADOを利用してください

また、データベース上の特定の文字列を置き換えしたい（システム名を正式名称に置き換えなど）の場合には一括で操作を行えるSQLを利用します

結果的にレコードが1つだったとしてもデータベースからのSELECT文はSQLを利用する方が便利です、厳密にはデータベース全てのレコードを操作することになります

例コードはレコードを代入させるためのオブジェクトになります
つまりレコードを取得する必要のない処理であればこのオブジェクトは不要です
例えば、UPDATE文やDELETE文などデータベースのレコードを編集するだけで、そのレコードを返さないような処理の場合です
この場合は、レコードのオブジェクトは不要で、接続用オブジェクトにSQLを実行してもらう形になります

'テーブルの作成
zzDB_Con.Execute "CREATE TABLE テーブル名(ID Long,日付 Date,コード String(20),金額 Currency)"

接続しているデータベースにテーブルを新規作成するには、SQL文のCREATE TABLE文を使用します

この構文を実行することで、レコードを収めるためのテーブルが作成されます

テーブルとはExcelでいうシートのようなもので、これが無いとデータベースにレコードを登録することが出来ません

また、1度作成してしまえばそこにレコードを保存していくので処理中に頻繁に出てくるような構文ではありません

また接続しているデータベースにSQLを実行するにはExecuteメソッドを利用します
引数にSQL文を文字列で指定します

Accessの無いPCであれば実際にデータベースの中身を確認できないので、このコードを実行しても何が起きているのか分かりませんが、再度実行してみてください
実行時エラーが発生し、すでに存在していることが分かります
とりあえずはそれで確認は十分です

コード解説

zzDB_Con.Execute "CREATE TABLE テーブル名(ID Long,日付 Date,コード String(20),金額 Currency)"

CREATE TABLE文にはまず、テーブル名を指定します
ここでは「テーブル名」という名称が指定されています
テーブル名という名前のテーブルを作成する、という意味になります

次に半角カッコで挟む形で作成するフィールドの指定を行います
フィールドとはExcelでいう列のことになります

AccessデータベースはExcelとは違い列は作成しなければいけません
またExcelと大きく違うのがフィールドには入力できるデータの型を指定する必要があることです
Excelはどんなものでも入力できるセルがありますが、どんなものでも入るのは便利な反面、何が入力されているかをいちいち判定する必要があり、ユーザーの入力ミスも往々にして発生します
そういったことを回避するためにデータベースでは事前に入力できる制限を設けることが出来ます

制約というものになりますが、今回は型の紹介だけ行っておきます

(ID Long,日付 Date,コード String(20),金額 Currency)"

まず、IDという名称のフィールドを作成します
このフィールドの型はLong型なので整数型になります
実際AccessデータベースにLong型は存在しません
ですが、自動的に同意義の型に変換してくれているのであまり気にしなくても良いです
さすが同じOfficeソフトです

つまり、このフィールドは整数値のみが利用可能で文字列は登録できないことになります

次に日付というフィールド名でDate型（日付型）を作成しています
日付型なので時間等も含めて登録することが可能です

次にコードという名称で文字列型のフィールドを作成しています
フィールドを複数作成する場合は、フィールドごとに半角カンマで区切ります

これは無くても構わないのですが、(20)という文字が追記されています
これは文字数制限になります
これ以上の文字列を登録しても切り捨てられて、この文字数で登録されます

最後にCurrency型（金額型）の金額というフィールドが作成されます
金額型なのでお金のデータを登録する際に利用します
ここら辺の型の細かいところは、変数に関する記事を確認してください

ExcelVBAで使用できる変数の型はほぼ利用できます
Double型、Boolean型なども利用可能です

ここまで来たらようやくレコードの登録が可能になります
その際にテーブル名とフィールド名を使用しますので、Accessの無い方は分からなくならないようにしてください

ExcelVBAではデータベース操作の基本であるレコード操作がオブジェクトを利用することで編集することが出来ます
また、このオブジェクトを利用せずにSQL文を利用してレコード操作を行うことも出来ます

どちらも同じ結果を導くので、どっちゃでもええのですが操作内容によっては処理速度に大きな差が出るので、そこには注意してください

基本的にコードの実行回数が同じ回数を実行する場合は、SQLよりオブジェクトを利用した方が早いです

例えば、レコードの追加が明確に差があります
SQL文ではレコードの追加はINSERT文を使用します、これはフィールド名と入力内容を指定することでデータベースに新規レコードとして追加する構文です
それに対して、オブジェクトを利用する場合はAddnewメソッドを利用します
このメソッドはデータの入力は行いません、新規レコードを追加するだけです
このメソッドを実行した後に各フィールド名を指定して値を代入していきます

この2つの全く同じ結果を求められる処理では、後者のオブジェクト利用が圧倒的に早いです

そもそもの想定の違いかもしれませんが、SQLは基本的に細かいコードを連続して実行するものではなく、なるべく1回のコード実行で結果を出すものだからです
対してExcelVBAは、細かいコードを大量に実行します

なのでどちらが優位というものでもないのでしょうが、ExcelVBAにおいてのレコード操作という点ではExcelVBAのオブジェクト利用が優位です

そして、ここまでオブジェクトと記載してきましたが、実際にはADOオブジェクトというものを利用します
これと似たような操作のできるDAOオブジェクトもありますが古いオブジェクトとなりますのでADOオブジェクトを利用できるようになれば十分です

このADOオブジェクトは、FileSystemObjectと同じようなインスタンスを作成して利用することのできるオブジェクトになります

Accessデータベースのファイルを作成し、接続を行い、テーブルの作成を行ったら
次に行うのがこのオブジェクトの作成です
それからようやくレコード操作が可能になります
実際のコードにすれば、ほんの数行の話なのであまり気負うものでもありませんが、初めは戸惑いたっぷりになるとは思いますが、基本はセルなどのオブジェクト操作と変わりませんので、この記事を読んでいる方のレベルなら特段難しくはありません

と、ここまでExcelVBAのオブジェクト利用の優位性を言い過ぎましたが、上記でも言いましたが実行回数が同じであれば確かにオブジェクト利用が優位です
ですが、オブジェクト利用ではレコードを1つずつ操作することになります

データベース全体に対するレコード操作を行いたい場合に1000レコードのデータベースであれば1000回コードを実行することになります
ですが、SQLのUPDATE文を利用すればたった1回の構文でデータベース全体の編集を行うことが出来ます

こうなるとコードの可読性においてもこちらが優位です

つまり言いたいのは、ADOオブジェクトだけではレコード操作を有効利用できない、ということです
おすすめ書籍でもあげているSQLの基礎本などを参考にSQLもすこしずつ理解を深めていってください

フィルタやワークシート関数を多用して、かつピボットテーブルまで使ってようやく出来るような集計がSQLでは驚くほど簡単に出来ることが多いです
データベースをデータの保管庫としてだけでなく、集計処理でも有効にデータベースを利用してください

'accdbファイルに接続する
zzDB_Con.Open "Provider=Microsoft.Ace.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "\作成DB.accdb"

'接続を解除（切断）する
zzDB_Con.Close

データベースの作成が完了したら、次にデータベースにOpenメソッドを使用して接続することで操作が可能となります
また、接続を行うとデータベースが接続状態となります、この状態を明示的に解除するのがCloseメソッドになります

データベースは性質上、排他的に開いたりすることが出来ます
データベースを操作する場合は、この切断処理は必ず行うようにしてください

コード解説

zzDB_Con.Open "Provider=Microsoft.Ace.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "\作成DB.accdb"

Openメソッドを使用して、データベースに接続を開始します
引数のProviderには既定の文字列を指定します、accdbファイルでは例コードの文字列を基本的に使用します
そのあとに続くSourceにAccessデータベースファイルの絶対パスを文字列で指定します
今回の場合では、同じフォルダ内のデータを参照しています

このOpenメソッド以降で、データベースに関する操作を行うことが出来るようになります

空白のファイルを作成して、最初に接続した場合はさらにテーブルの作成を行う必要があります
このテーブルの作成まで行うと、そこにレコードを登録していくことが出来るようになるためデータベースにデータを保存していくことが出来ます

zzDB_Con.Close

このコードを実行すると接続しているデータベースを切断します
データベースの利用が終了したら確実に行います
また、なるべくデータベースは無駄に接続することの無いようにしてください

データベースから受け取ったデータの加工等に時間のかかる処理であれば、処理後にまたデータベースを利用するとしても、いったん接続は切断するようにします

ただ、処理に時間が大きくかかる可能性が無いことや、データベースの利用自体がそもそも個人レベルのものである場合はそこまで気にしなくてもいいです

なんせ、接続したら終了時には切断する
ここをしっかり行うようにしてください

'接続用オブジェクトの作成
Dim zzDB_Con As Object
Set zzDB_Con = CreateObject("ADODB.Connection")

Set zzDB_Con = Nothing

Accessデータベースを操作するには、まずデータベースファイルの作成を行う必要がありました

次に必要なのが、そのデータベースと接続を行うことです
この接続という操作を行って初めてデータベースの操作が可能になります

その際に使用するのが記事コードのADODB.Connectionオブジェクトになります
これはインスタンスの作成を行う必要がありますので、最初にCreateObject関数でインスタンスの作成を行います

このオブジェクトは単にデータベースとの接続だけを行うものでは無く、接続したデータベースにSQL文を実行することが出来ます
基本的には接続とSQL文の実行の2つを実行するオブジェクトと認識してください

そしてSQL文の実行に関して、このオブジェクトではレコードを取得する必要が無い場合に主に利用します

例えば、UPDATE文はデータベースのデータを更新しますが、結果を取得しません
そういった構文に利用します
なのでSELECT文のようなレコードの取得を行う構文はこのオブジェクトでは利用できません

コード解説

Dim zzDB_Con As Object

ADODB.Connectionを代入させる変数です
事前バインディングの場合は専用の型を使用しますが、実行時バインディングを利用しますのでここではObject型で指定します

Set zzDB_Con = CreateObject("ADODB.Connection")

CreateObject関数でインスタンスの作成を行っています
Class名はADODB.Connectionと指定します

この作成後にデータベースの操作を行います

Set zzDB_Con = Nothing

操作が完了したらオブジェクト変数なので、最後に解放して初期化しておきます

実際の使用について

これで作成を実行した後に、使用するメソッドは主に3つになります
まずはデータベースとの接続を行うOpenメソッド、接続を解除するCloseメソッド、SQL文の実行を行うExecuteメソッドになります

OpenメソッドとCloseメソッドはセットで使用します
Openメソッド使用後はデータベースが使用中になります
この状態を解除するのがCloseメソッドなので必ず使用してください

Executeメソッドは、引数にSQL文を文字列で指定することで接続しているデータベースに対して実行します
上記にも解説したようにレコードの取得を必要としない構文を実行します

Accessデータベースを操作するうえで利用するのは、あとレコードを代入させるためのADODB.Recordsetオブジェクトを使用します

ちなみに、ここで使用しているADODB以外にDAOというオブジェクトもありますが、こちらはADODBより古いオブジェクトになります
こちらはこちらで利点もありますし、使用感も似ていますが両方を覚える必要はありません
せめてインスタンス作成が同じで利用できるなら良いのですが、別のインスタンスを作成する必要があるので両方の利用は面倒ですね