More than 3 years have passed since last update.

VBAフォーム、DB接続クラス化入門①

Posted at 2020-01-12

###【はじめに】
医療事務から30代未経験でSIerへ転職して1年経過したため（保守運用）、この一年勉強して自分がググる際に、この情報もっとあったらいいなと思ったことを備忘録として登録します。
配属しいている現場はVB6を使用したレガシーな現場である為、書き方が古かったりするかもしれないが、その点はまだ無知な点が多い為、ご了承ください。
熟練者から見たらまだまだ、冗長化されている部分もあるかと思いますが、あくまで入門者向けの方に参考していただければと思います。

自宅でも勉強構築できる、なおかつ互換性のあるVBAにてCRUDができる機能を実装します。
そもそもプログラミング自体ほぼ皆無だった状態からのため、レガシーだろうが、何だろうが、まずはプログラミングについて理解を深める意味で初めてQiitaに投稿します。
今回はDB接続～テーブルに登録してあるデータをSELECTして取り出したデータをリストに表示するところまでを備忘録として記載します。
###【環境】

OS：Windows8.1
Excel 2013
エディタ：VBE
ローカル開発環境：xampp(過去にPHPのCRUDを勉強するためにxamppを用いてDB構築したため利用)

###【主な機能について】
DB登録されたID、UserName、Email、登録年月日をテキストボックスに入力して、データ表示ボタンをクリックすると、リストボックスに登録データを表示する。
※1.登録年月日の入力は必須
※2.各バリデーションチェックのソースコードを載せると長くなるため、記載しておりません。

フォームのイメージは以下
メイン画面（現状データ検索ボタンしかないが、データ作成、更新、削除を追記予定）
frmMain

データ検索ボタンをクリックすると以下のフォームを表示
テキストボックスのオブジェクト名は以下の通り

ID⇒ID_txt.Text
UserName⇒UserName_txt.Text
Email⇒Email_txt.Text
登録年月日（from）⇒InsDate_from_txt.Text
登録年月日（to）⇒InsDate_to_txt.Text

frmSearch

チェック項目

| |ID |UserName |Email |登録年月日(from) |登録年月日(to) |
|:-----|:-----|:-----|:-----|:-----|:-----|:-----|
|入力|任意|任意|任意|必須|必須|
|最大文字数|11|25|255|8|8|
※DB設計時、細かく設定しておらず、Emailの最大文字数は255のままになっている
改めて適切なchar数に設定したいと思う

###【Create Table】
DBへ登録するための、テーブル作成
データベース名：bbs
テーブル名：test_user_data_hed
※データは適当にINSERTして登録してもらえればと思います。

test_user_data_hed.sql

CREATE TABLE bbs.user_data_hed (
	ID        INT(11) AUTO_INCREMENT NOT NULL PRIMARY KEY,
	USER_NAME VARCHAR(25) NOT NULL,
	EMAIL     VARCHAR(35) NOT NULL,
	STATUS    INT(1) NOT NULL,
	INS_DATE  VARCHAR(14) NOT NULL,
	UPD_DATE  VARCHAR(14) NOT NULL  	
);

/*
STATUS = 1:通常、9:論理削除
*/

###【ソースコード】

frmMain


'--------------------------------------------------------------------
'データ検索フォーム表示
'--------------------------------------------------------------------
Private Sub frmTableSearch_Click()
    frmSearch.Show
End Sub

frmSearch

Option Explicit
'--------------------------------------------------------------------
'変数宣言
'--------------------------------------------------------------------
	Private lstrId             As String
	Private lstrUser_name      As String
	Private lstrEmail          As String
	Private lstrStatus         As String
	Private lstrInsDatefrom    As String
	Private lstrInsDateTo      As String

'--------------------------------------------------------------------
'機能：bbsへDB接続
'test_user_data_hedに登録されているテーブルデータをリストへ表示するためのトリガー
'--------------------------------------------------------------------
Public Sub select_cmd_Click()
    Dim dbconnect   As mysql_connect
    Dim ladoRs      As ADODB.Recordset   'ローカルレコードセット変数
    Dim ConnectFlg  As Boolean           '接続成功: True   接続失敗:False
    Dim lstrSQL1    As String            'ローカルsql格納変数
    Dim lintroop    As Integer           'ループ変数
    '例外処理⇒以下の記述でエラーが発生した場合にはエラーメメッセージ表示
On Error GoTo ErrHandler_End

    lstrId = ID_txt.Text
    lstrUser_name = UserName_txt.Text
    lstrEmail = Email_txt.Text
    lstrInsDatefrom = InsDate_from_txt.Text
    lstrInsDateTo = InsDate_to_txt.Text

    'mysqlクラスへ接続 Mysql_connectクラスのdbconnectをインスタンス化
    Set dbconnect = New mysql_connect
    'Mysqlクラスのmysql_connectメソッド内で接続処理を行い、接続成功の場合Trueを返す
    ConnectFlg = dbconnect.mysql_connect

    'selectすべきSQLを記述
    lstrSQL1 = ""
    lstrSQL1 = "SELECT ID"
    lstrSQL1 = lstrSQL1 & ",USER_NAME"                        
    lstrSQL1 = lstrSQL1 & ",EMAIL"
    lstrSQL1 = lstrSQL1 & ",STATUS"
    lstrSQL1 = lstrSQL1 & ",INS_DATE"
    lstrSQL1 = lstrSQL1 & ",UPD_DATE"
    lstrSQL1 = lstrSQL1 & " FROM bbs.test_user_data_hed"
    '論理削除は表示しない
    lstrSQL1 = lstrSQL1 & " WHERE STATUS <> '9'"
    
    'ID入力がある場合は、
    If Trim(lstrId) <> vbNullString Then
        lstrSQL1 = lstrSQL1 & " AND ID ='" & lstrId & "' "
    End If
    
    'USER_NAME入力がある場合は、
    If Trim(lstrUser_name) <> vbNullString Then
        lstrSQL1 = lstrSQL1 & "AND USER_NAME ='" & lstrUser_name & "' "
    End If
    
    'EMAIL入力がある場合は、
    If Trim(lstrEmail) <> vbNullString Then
        lstrSQL1 = lstrSQL1 & "AND EMAIL ='" & lstrEmail & "' "
    End If

    lstrSQL1 = lstrSQL1 & " AND INS_DATE >='" & lstrInsDatefrom & "000000" & "' "
    lstrSQL1 = lstrSQL1 & " AND INS_DATE <='" & lstrInsDateTo & "235959" & "' "

    'DB接続状態である場合に、Mysqlクラスのmysql_selectメソッドへアクセスし、上記で記述したSQLとリストオブジェクトを引数として渡す。
    If ConnectFlg = True Then
      Set ladoRs = dbconnect.mysql_select(lstrSQL1, select_data_List)      
    End If
    'インスタンス解放(確保していたメモリ領域を0にする)    
    Set dbconnect = Nothing
    Set ladoRs = Nothing
    
Exit Sub

ErrHandler_End:
    Set dbconnect = Nothing
    Set ladoRs = Nothing
    Call MsgBox ("DB接続エラー" & vbCrLf & _
                "ErrNo:" & Err.Number & vbCrLf & _
                "Err内容:" & Err.Description _
               , vbExclamation, "Error:Sub dbconnect")
End Sub

Private Sub Exit_cmd_Click()
    Unload frmSearch
    Load frmMain
End Sub

Private Sub UserForm_Initialize()
    ID_txt.Text = vbNullString
    UserName_txt.Text = vbNullString
    Email_txt.Text = vbNullString
    InsDate_from_txt.Text = vbNullString
    InsDate_to_txt.Text = vbNullString
End Sub

Mysql_connect.cls

Option Explicit
 Private adoCon     As ADODB.Connection      'データベースアクセスインターフェースオブジェクト(ActiveX Date Objects)
 Private adoRs      As ADODB.Recordset       'レコードセット変数　Executeで実行したSQLで取得したレコードをセット
 Private adoCmd     As ADODB.Command         'SQL実行コマンドオブジェクト
 Private Const DSN = "ExcelDB"               'DataSourceName
 Private Const Database = "bbs"              '接続先DB
'--------------------------------------------------------------------
'DB接続class
'mysqlへの接続
'【引数】なし
'mysql_connect          True:接続成功　False:接続失敗
'--------------------------------------------------------------------
Public Function mysql_connect() As Boolean
    'adoコネクション作成（データベース接続オブジェクト）
    Set adoCon = New ADODB.Connection
    
On Error GoTo ErrHandler
           
    'サーバから取得したカーソルをクライアント側に置いて作業する。
    adoCon.CursorLocation = adUseClient
    'DB接続、ODBCを使用してMysqlへ接続
    'Mysql接続(接続文字列 ⇒ DSN)
    adoCon.Open DSN
    '接続成功
    mysql_connect = True
    'エクセル画面の左下、ステータスバーにDB接続成功した場合は、以下の文字列を表示
    Application.StatusBar = "DB接続成功!! " & "接続先:" & Database
    Debug.Print "接続先:" & "bbs"
    

Exit Function

ErrHandler:
        '接続失敗
        mysql_connect = False
        Set adoCon = Nothing
        Application.StatusBar = "DB接続失敗!!　調査を依頼してください。"
        Call MsgBox("DB接続時エラー", vbExclamation, "Error:Function mysql_connect")
        
        
End Function


'--------------------------------------------------------------------
'【機能】sql文実行（SELECT）
'【引数】 lstrSQL
'        select_date_List
'【戻り値】adoRs 問い合わせしたSQLのレコードを返す
'--------------------------------------------------------------------
Public Function mysql_select(ByVal vstrSQL1 As String _
                           , ByVal vobjList As MSForms.ListBox) As ADODB.Recordset
    
    Dim lintloopRcdCnt   As Integer        'レコード取得ループ変数
    Dim lRecordCnt       As Long           'レコードカウント数取得変数
'On Error GoTo 以下で予期せぬエラーが発生した場合、ErrHandlerのラベルまで飛び、エラー発生時の処理およびメッセージを表示してFunctionを抜ける
On Error GoTo ErrHandler
    
    'DBアクセスのタイムアウト時間（時間切れ）を10分に設定
    adoCon.CommandTimeout = 60*10
    Set adoCmd = New ADODB.Command        'SQLコマンドオブジェクトインスタンス化 
    adoCmd.ActiveConnection = adoCon      'コマンドオブジェクトのActiveConnectionプロパティを使用して、現在開いているDBと関連付け
    adoCmd.CommandText = vstrSQL1         '実行するSQLを格納 コマンドテキストにSQLステートメントを定義する(文字列にストアドプロシージャの文字列を設定するとストアドを実行する)
    Set adoRs = New ADODB.Recordset       'レコードセットオブジェクトインスタンス化
    
    'クエリーの結果を格納するRecordsetオブジェクト変数
    'コマンドテキストに格納されたSQLをExecuteメソッドを実行し、adoRsへ格納する
    Set adoRs = adoCmd.Execute
    Debug.Print vstrSQL1
    
    'SQLで問い合わせて取得したデータをリストボックスへ表示
    With vobjList
            .Clear                       'リストの項目を初期化するためクリア
            .ColumnCount = 3             'リストに表示するカラム数
            .ColumnWidths = "30;60;100"  'リストに表示する幅
    
   'adoRsへ格納したSQLステートメントをループさせてリストへ取り出す処理   
    Do Until adoRs.EOF
         .AddItem ""
         'レコードセットの列数を取得して、ループ
         For lintloopRcdCnt = 0 To adoRs.Fields.Count - 1
         'レコードの値が存在する場合は、レコードの値をリストへ表示する
             If Not IsNull(adoRs(lintloopRcdCnt).Value) Then
                 'adoRsのレコードデータをリスト(行、列)をループして追加(Listの先頭インデックスは「0」スタートのため、-1をする⇒(List(0),レコード列の
                 'カウント数)に対して、レコードの値を代入していっている)
                 .List(.ListCount - 1, lintloopRcdCnt) = adoRs(lintloopRcdCnt).Value
             End If
         Next
         '1行目のレコード取り出しが完了したら、次のレコードへ
         adoRs.MoveNext
    Loop
    End With
    
   'レコードカウント数を取得し、0件だった場合はエラーメッセージ
    lRecordCnt = adoRs.RecordCount
    If lRecordCnt = 0 Then
        Call MsgBox("該当のデータは存在しません。", vbQuestion)
        Exit Function
    End If
    'レコードセットを返す
    Set mysql_select = adoRs

    Set adoCmd = Nothing
    Set adoRs = Nothing
    'Connectionオブジェクトを閉じて、それに関連するRecordsetオブジェクトも閉じる
    adoCon.Close
    '閉じただけでは確保しているメモリは解放されない為、Nothingで解放する。
    Set adoCon = Nothing
    
Exit Function
ErrHandler:
        'レコード取得時に予期せぬエラー
        Set adoCmd = Nothing
        Set adoRs = Nothing
        adoCon.Close
        Set adoCon = Nothing
        Call MsgBox("DBレコード取得時エラー" & vbCrLf & _
                          "ErrNo:" & Err.Number & vbCrLf & _
                          "Err内容:" & Err.Description _
                         , vbExclamation, "Error:Function mysql_select ")
    
    
End Function

以上がMysqlへ接続するためのソースになるが、ソースとその説明を別々に記載するとわかりづらいかと思い、すべてコメントに記述してみた。

###【参考資料】

ODBCによるMySQLの接続と操作

CRUD

【PHP超入門】クラス～例外処理～PDOの基礎
　

ODBCによるMysql接続は上記サイトを参考に、コントロールパネル⇒システムとセキュリティ⇒管理ツールの順で進み、ODBCデータソース(32ビット)にて、ユーザーDSNを追加し、

Data Source Name: xxxxxx
TCP/IP: localhost or 127.0.0.1
Port: 3306
User: xxxxxx
Password: xxxxxx
Database: xxxxxx

上記を設定後、テストボタンをクリックして、「Connection Successful」が出れば接続テストは完了です。
PHP超入門はクラスを学ぶ上で大変参考にさせていただいた記事です。
PHP言語に限らず他の言語でも参考になるものと思っています。

VBAクラスモジュール超入門

VBA中級者を悩ませるプロシージャ分割をマスターする極意

こちらの記事も大変参考にさせていただいた記事です。クラス、Propertyの使い方など（この点については使いどころも部分がまだまだ自分も未熟のため、本投稿のソースはPropertyを使用していませんが…）実際にデバック操作することで理解を深められる内容となっていました。
どこを分割するか、どうやって分割するか、についてのコツがわかりやすくまとまっています。
今でも何度も読み返すことの多いブログです。

[オブジェクト指向でなぜ作るのか] (https://www.amazon.co.jp/%E3%82%AA%E3%83%96%E3%82%B8%E3%82%A7%E3%82%AF%E3%83%88%E6%8C%87%E5%90%91%E3%81%A7%E3%81%AA%E3%81%9C%E3%81%A4%E3%81%8F%E3%82%8B%E3%81%AE%E3%81%8B%E2%80%95%E7%9F%A5%E3%81%A3%E3%81%A6%E3%81%8A%E3%81%8D%E3%81%9F%E3%81%84%E3%83%97%E3%83%AD%E3%82%B0%E3%83%A9%E3%83%9F%E3%83%B3%E3%82%B0%E3%80%81UML%E3%80%81%E8%A8%AD%E8%A8%88%E3%81%AE%E5%9F%BA%E7%A4%8E%E7%9F%A5%E8%AD%98%E2%80%95-%E5%B9%B3%E6%BE%A4-%E7%AB%A0/dp/4822281957/ref=sr_1_4?__mk_ja_JP=%E3%82%AB%E3%82%BF%E3%82%AB%E3%83%8A&keywords=%E3%81%AA%E3%81%9C%E3%82%AA%E3%83%96%E3%82%B8%E3%82%A7%E3%82%AF%E3%83%88%E6%8C%87%E5%90%91%E3%81%A7%E4%BD%9C%E3%82%8B%E3%81%AE%E3%81%8B&qid=1578812241&s=books&sr=1-4)

オブジェクト指向について未だに理解が不十分であるが、そもそもを理解するために、良書といわれている上記本を参考し、クラスをインスタンス化した場合のメモリの確保などについて学んだ本。
再度読み返し、理解を深めたい。

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up