【はじめに】
医療事務から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 | 登録年月日(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接続は上記サイトを参考に、コントロールパネル⇒システムとセキュリティ⇒管理ツールの順で進み、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言語に限らず他の言語でも参考になるものと思っています。
こちらの記事も大変参考にさせていただいた記事です。クラス、Propertyの使い方など（この点については使いどころも部分がまだまだ自分も未熟のため、本投稿のソースはPropertyを使用していませんが…）実際にデバック操作することで理解を深められる内容となっていました。
どこを分割するか、どうやって分割するか、についてのコツがわかりやすくまとまっています。
今でも何度も読み返すことの多いブログです。
オブジェクト指向について未だに理解が不十分であるが、そもそもを理解するために、良書といわれている上記本を参考し、クラスをインスタンス化した場合のメモリの確保などについて学んだ本。
再度読み返し、理解を深めたい。