VBAでExcelからAccessに接続 その３

前回はこちら

『VBAでExcelからAccessに接続　その２』

先週書いたコードはこんな感じでした。

'④-１．DB接続
'④-２．レコードセットオブジェクト生成
'④-３．クエリ実行＆データ取得
'④-４．レコードセットの配列化
'④-５．配列データをコンボボックスのリストに反映
'④-６．DB切断

これを、DBからデータ取るプロシージャごとに実行する作りになってました。

１～６の流れの中で処理ごとに異なる内容というのは、

• ２でRecordsetのOpenに使うクエリ内容
• ４と５のプロシージャ個別処理

ぐらいなんですよね。

同じコードがあちこちにいっぱい書いてあるな、と感じたので共通化しました。

まずは元プロシージャ。

２のクエリを変数に格納した後、クエリ結果のレコードセットを返す関数へ渡すように。

    strSQL = "SELECT catID,catName FROM category where parentID is null"
    Set adoRs = getCursor(strSQL)

で、呼び出し先の関数が新たに作成したというか纏めなおした処理。

Private Function getCursor(ByVal strSQL As String) As Object
    Dim objCon As Object
    Dim adoRs As Object
'①DB接続
    Set objCon = connectDB()

'②レコードセットオブジェクト生成
    Set adoRs = CreateObject("ADODB.Recordset")
    adoRs.CursorLocation = 3 ' adUseClient クライアントサイド
'③クエリ実行＆データ取得＆後処理
    adoRs.Open strSQL, objCon
    adoRs.activeconnection = Nothing '※今回のポイント
'④DB切断
    Call disconnectDB(objCon)
'⑤レコードセットを呼び出し元に返却
    Set getCursor = adoRs
End Function

①、②、④は元の処理と同じ事をやってます。

⑤は、関数なので戻り値を設定してます。

③の一行目も元の処理と同じですが、ポイントが二行目。

activeconnection = Nothing

これが、この関数内でDBを切断する為に必要となります。

なんで関数内で切断が必要かというと、接続オブジェクトを呼び出し元で持ち回ししないといけないから。

接続オブジェクトを持ちまわす理由は、普通に閉じちゃうとレコードセットも操作できなくなるから。

（DBに繋いで見てるデータを参照する変数なので、切断したら見えなくなってしまう）

という事で、接続を閉じてもレコードセットを操作できるように値渡しの変数みたいに、値のコピーとして変数を定義できないか調べてました。

それで見つけたのが、切断してもレコードセットのデータ内容を保持する為のオプション。

よくわからずおまじないとして記述してた下のコードとも関わるようです。

CursorLocation = 3 ' adUseClient クライアントサイド

これで、DBオープン→クエリ実行して結果取得→それを保持したままDBクローズ、を実装出来ました。

DBオープンクローズを完全に分離したので、各プロシージャ内ではDB接続オブジェクトの変数も必要なくなりました。

お陰で結構スッキリした感じに！

VBAでExcelからAccessに接続 その２

前回はこちら

『VBAでExcelからAccessに接続　その１』

ちなみに先週時点と比べてコードの中身がどんどん変わっていってる（設計してないから）ので、

先週時点のデータをゴミ箱から拾い上げて投稿してます。。。

■④データ取得

まずはコードから。

Public Sub getCategory()
    Dim objCon As Object
    Dim adoRs As Object
    Dim strSQL As String
    Dim DBdata() As String
    Dim iCnt As Integer: iCnt = 0

'④-１．DB接続
    Set objCon = connectDB()
'④-２．レコードセットオブジェクト生成
    Set adoRs = CreateObject("ADODB.Recordset")
    adoRs.CursorLocation = 3 ' クライアントサイド
'④-３．クエリ実行＆データ取得
    strSQL = "SELECT catID,catName FROM category where parentID is null"
    adoRs.Open strSQL, objCon '※3
'④-４．レコードセットの配列化
    iCnt = adoRs.RecordCount
    ReDim DBdata(iCnt - 1, 1)
    iCnt = 0
    Do Until adoRs.EOF
        DBdata(iCnt, 0) = adoRs.Fields.Item(0)
        DBdata(iCnt, 1) = adoRs.Fields.Item(1)
        iCnt = iCnt + 1
        adoRs.Movenext
    Loop
'④-５．配列データをコンボボックスのリストに反映
    frmMain.ComboBox1.List = DBdata
'④-６．DB切断
    Call disconnectDB(objCon)
End Sub

■④-１．DB接続
 

先週の記事に記載した接続プロシージャです。

目的のファイルと接続プロバイダを指定し、コネクションをオープンしています。

■④-２．レコードセットオブジェクト生成
 

NEWで設定するのと違いがあるのかは把握してません。

CursorLocationについては下記参照。クライアント側でカーソルを触る場合はこれ、という理解でとりあえずはOKそうです。

カーソル位置プロパティ (ADO) - ActiveX Data Objects (ADO)

■④-３．クエリ実行＆データ取得
 

クエリ文字列を設定した変数を渡して、クエリ実行→結果データの取得、を行います。

クエリ内容については、ここでは「IDと名前を取得している」事だけ気にしてもらえればOＫです。

Openメソッドについてはこちらを。

Open メソッド (ADO Recordset) - ActiveX Data Objects (ADO)

Open および Close メソッドの例 (VB) - ActiveX Data Objects (ADO)

■④-４．レコードセットの配列化
 

クエリの結果となるレコード数を元に配列を再定義し、レコードセットのデータを元に「ID、名前」の二次元配列を作成します。

Do～LoopをレコードセットのEOFまでmovenextで全件処理します。

int型変数は、配列の添え字を指定する為だけの物ですので他の方法でも全然ＯＫです。

■④-５．配列データをコンボボックスのリストに反映
 

前項で作成した配列のデータを丸ごとコンボボックスのリストとして定義します。

GUIからcomboboxオブジェクトのプロパティを見てもListという項目はなく、ネットで検索してもだいたいは「AddItem」もしくは「RowSource」で指定するような記載になっています。

MicorosoftのDOCサイトで探すのも分かりにくく、オブジェクトのプロパティを辿っても出てきません。

List プロパティ (Microsoft Forms)

DBから可変のデータを取ってくる場合、セルにいちいち書いて取ってくるのはセル範囲指定が悩ましいかな、と思いました。

AddItemなら単純に指定できるのですが、１行に２値を持たせる為にはどうすればいいのか分からず断念。

実はこのコンボボックス、一つの行に二つデータを持つ設定にしています。

ただしIDは見えてもあまり嬉しいデータではないので、表示されるのは名前だけです。

名前を選択した際にCombobox.Valueから得られる値がID、となるのが狙いです。

重要なプロパティは次の四つ。

★BoundColumn

　複数列があるコントロールで行を選択すると、プロパティに設定されている列の値がValueプロパティに格納されます。

★ColumnCount

　リスト ボックスまたはコンボ ボックスに表示する列の数を指定します。

★ColumnWidth

　複数列コンボ ボックスまたはリスト ボックスの各列の幅を指定します。

★TextColumn

　ユーザーが行を選択した場合に、Textプロパティに格納するComboBox列を識別します。

BoundColumn プロパティ

ColumnCount プロパティ (Microsoft フォーム)

ColumnWidths プロパティ

TextColumn プロパティ

まとめると、

1. IDと名前を１行に持たせるので、ColumnCount=2
2. コンボボックス選択時のValueはIDにしたいので、BoundColumn=1
3. コンボボックス上にIDは表示したくないので、ColumnWidth=0pt;30pt（１つ目を幅0にして疑似的に非表示）
4. コンボボックス選択時の表示値は名前にしたいので、TextColumn=2

といった感じです。こういう事出来るの全然知りませんでした。。。

■④-６．DB切断

先週のコードのままです。

今週かなり書き換えたので、来週は少しブラッシュアップした完成版を公開できればと思います。

VBAでExcelからAccessに接続 その１

■処理概要

①Excelブックを開いたらフォームを表示

②フォームには下記のオブジェクトを配置

• コンボボックス　：　抽出条件
• ボタン　：　抽出実行
• テキストボックス　：　データ

③コンボボックスの値はフォーム初期化時にDB（ACCESS）から取得

④コンボボックスを選択して抽出実行ボタンでデータをDBから取得

■①フォーム表示

とても簡単。

＜ThisWorkbookのコード＞

Private Sub Workbook_Open()
    frmMain.Show
End Sub

■②フォームのオブジェクト

画面表示は割愛します。オブジェクト名は以下の通り。

• フォーム自体　：　frmMain
• コンボボックス　：　ComboBox1
• ボタン　：　btnQuestion
• テキストボックス　：　txtQuestion

■③フォーム初期化

ボタンなどのCaptionプロパティをデザイン画面で入力すると、２バイト文字はどうも化ける模様でした。

＜frmMainのコード＞

Private Sub UserForm_Initialize()
    btnQuestion.Caption = "出題！"   
    getCategory
End Sub

カテゴリデータ取得以降は共通モジュールにて。

＜Module1のコード＞

Public Sub getCategory()
    Dim objCon As Object
    Dim ADOrecset As Object
    Dim strSQL As String
    Dim DBdata() As String
    Dim iCnt As Integer: iCnt = 0
 

    Set objCon = connectDB()     '←DBへの接続

    Set adoRs = CreateObject("ADODB.Recordset")    '←DBデータ取得の器を定義
    ADOrecset.CursorLocation = 3 ' クライアントサイド
    
    strSQL = "SELECT ID,Name FROM 抽出条件"
    ADOrecset.Open strSQL, objCon    '←DBデータを上記のSQL文で取得

'※ポイント※
    iCnt = ADOrecset.RecordCount
    ReDim DBdata(iCnt - 1, 1)
    iCnt = 0
    Do Until ADOrecset.EOF
        DBdata(iCnt, 0) = ADOrecset.Fields.Item(0)
        DBdata(iCnt, 1) = ADOrecset.Fields.Item(1)
        iCnt = iCnt + 1
        ADOrecset.Movenext
    Loop

    frmMain.ComboBox1.List = DBdata
    
    Call disconnectDB(objCon)    '←DBから切断
End Sub

DBへの接続は別関数にして次で説明。データ取る度使いますので。切断も同様。

※ポイント※ですが、ちょっとダサめに一行ずつ取って配列に入れてループしてます。

set Variant型変数 = adoRs.getRows　とすれば一発で取れるのですが、配列の中身が微妙で。

例えば２カラムのデータ４行をDBから取得した場合に、配列の中のデータ状態は

配列(0,0)　には　row1のcolumn1が、配列(0,1)　には　row2のcolumn1が、

中略で、配列(1,3)　には　row3のcolumn2が、配列(1,4)　には　row4のcolumn2。

といった具合で、添え字が（列,行）の形になるんですよね。

ツールの性質上あまり悩んでも仕方ないと思ったので、諦めてループにしました。

データベースへの接続処理です。

Private Function connectDB() As Object
    Dim DBpath As String 'Accessファイルのフルパス
    Dim adoCon As Object 'Accessへの接続用オブジェクト

    Set adoCon = CreateObject("ADODB.Connection")
    DBpath = ThisWorkbook.Path & "\DB.accdb"
 

'どっちでも接続可能

'    adoCon.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DBpath & ";"
    adoCon.Open "Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=" & DBpath & ";"
 

    Set connectDB = adoCon    
End Function

ACCESSに接続する場合の、ADOオブジェクトのConnectionStringプロパティに指定すべき値について、

Microsoftが公開している情報をさがしたのですが、見つかりませんでした。。。

一応網羅性の高そうなサイトの情報がありましたのでご紹介を。

Access 2013 connection strings - ConnectionStrings.com

※こちらの記事で見つけたサイトです

Microsoft.ACE.OLEDBについてまとめてみた - Qiita

最後に切断。よそで作ったオブジェクトの削除なので参照渡しで。

Private Sub disconnectDB(ByRef ADOcon AS Object)    
    ADOcon.Close            'Accessへの接続解除
    Set ADOcon = Nothing    '接続用オブジェクトの解放
End Sub

■④データ取得

長くなったので次回にします。

コンボボックスへの値の設定についてちょっと工夫がありました。

■あとがき

最初はこんな事やりたいと考えてました

Excelのワークシートにフォームコントロールを配置

コントロールに指定する値はACCESSに作ったテーブルからとってくる

コンボボックスに値を指定
コンボの選択を条件に、ボタンクリックで詳細データ取得

結果・・・失敗

ワークシート上のフォームコントロールのオブジェクトを指定する方法が分かりませんでした。

Shapeオブジェクトとして選択は出来るようですが、Shapeオブジェクトな訳ですからプロパティが全然違います。

結局諦めてフォームを追加する事にしたのでした。