VBA:Excel :Range
Range設定
Sub Range設定() '====================================== '// セル、範囲 '====================================== '// 単一セル Range("G2").Select Cells(3, 2).Select Cells(4, "B").Select '// 範囲 Range("G2", "I2").Select '// G2~I2 Range("G2:I2").Select '// G2~I2 Range(Cells(2, 7), Cells(2, 9)).Select '// 離れた Range("G2, I2").Select '// G2,I2 Dim rng As Range Set rng = Cells(2, 7) Set rng = Union(rng, Cells(2, 9)) Set rng = Union(rng, Cells(4, 9)) rng.Select '====================================== '// 行、列 '====================================== '==================================== '[Rows / Columns] ' 単一行:Rows(2) ' 単一列:Columns("G") ' 単一列:Columns(2) ' 連続列:Columns("F:E") '------------------------------------- '[Range] ' 単一行:Range("8:8").Select ' 離れた行:Range("2:2,4:4,6:8").Select ' 連続列:Range("E:F").Select ' 離れた列:Range("E:E,G:H").Select ' 指定Rangeを含む行:Range("E1,G1:H1").EntireRow.Select ' 指定Rangeを含む行:Range("E1,G1:H1").EntireColumn.Select '==================================== Range("E:F").EntireColumn.Select '連続 Range("E:E,G:H").Select '離れた Range("E1,G1:H1").EntireColumn.Select '離れた '// 単一 Columns("G").Select Columns(5).Select '// 離れた Set rng = Columns(5) Set rng = Union(rng, Columns(7), Columns(10)) rng.Select End Sub
範囲取得
'********************************************** ' ' '********************************************** Sub 範囲取得() Dim ws As Worksheet Set ws = ThisWorkbook.Worksheets("TEST") ws.Range("B2").CurrentRegion.Select Set ws = Nothing End Sub
最終行/列
'********************************************** ' ' '********************************************** Sub 最終行_列() Dim ws As Worksheet Set ws = ThisWorkbook.Worksheets("TEST") Dim rng As Range Set rng = ws.Range("B2").CurrentRegion '// Row Debug.Print rng.Rows(rng.Rows.Count).Address '// レンジ内 行アドレス Debug.Print rng.Rows(rng.Rows.Count).Row '// 行番号 Debug.Print rng.Rows(rng.Rows.Count).EntireRow.Address '// 行全体(Entire:全体) rng.Rows(rng.Rows.Count).EntireRow.Select '// Col Debug.Print rng.Columns(rng.Columns.Count).Address '// レンジ内 列アドレス Debug.Print rng.Columns(rng.Columns.Count).Column '// 列番号 Debug.Print rng.Columns(rng.Columns.Count).EntireColumn.Address '// 列全体(Entire:全体) rng.Columns(rng.Columns.Count).EntireColumn.Select Set ws = Nothing End Sub
開始行/列
'********************************************** ' ' '********************************************** Sub 先頭行_列() Dim ws As Worksheet Set ws = ThisWorkbook.Worksheets("TEST") Dim rng As Range Set rng = ws.Range("B2").CurrentRegion '====================== '// Row '====================== Debug.Print rng.Rows(1).Address '// レンジ内 行アドレス Debug.Print rng.Rows(1).Row '// 行番号 Debug.Print rng.Rows(1).EntireRow.Address '// 行全体(Entire:全体) rng.Rows(1).EntireRow.Select '====================== '// Col '====================== Debug.Print rng.Columns(1).Address '// レンジ内 列アドレス Debug.Print rng.Columns(1).Column '// 列番号 Debug.Print rng.Columns(1).EntireColumn.Address '// 列全体(Entire:全体) rng.Columns(1).EntireColumn.Select Set ws = Nothing End Sub
セル(行頭先頭/行頭最後/行末先頭/行末最後
'********************************************** ' ' '********************************************** Sub セル() Dim ws As Worksheet Set ws = ThisWorkbook.Worksheets("TEST") Dim rng As Range Set rng = ws.Range("B2").CurrentRegion '-------------------------------------------------------- Dim rng_Cell As Range '====================== '左上 '====================== Set rng_Cell = rng(1, 1) rng_Cell.Select Debug.Print rng_Cell.Address '====================== '右上 '====================== Set rng_Cell = rng(1, rng.Columns.Count) rng_Cell.Select Debug.Print rng_Cell.Address '====================== '左下 '====================== Set rng_Cell = rng(rng.Rows.Count, 1) rng_Cell.Select Debug.Print rng_Cell.Address '====================== '右下 '====================== Set rng_Cell = rng(rng.Rows.Count, rng.Columns.Count) rng_Cell.Select Debug.Print rng_Cell.Address '-------------------------------------------------------- Set rng = Nothing Set ws = Nothing End Sub
VBA:Excel :TOP
- カテゴリ
- App
- ブック
- シート
- Range, Cell
- 配列/コレクション/辞書
- 検索
- ステートメント
- ■文字列
- ■日付/時刻
- ■フォーム
- ■FSO
- ■Excel関数(Application.WorksheetFunction)
- ■Tips
- 資格
- その他
カテゴリ
・セルの書式設定
・テーブル
・Tips
・Excel マクロ・VBA:入門
・オブジェクト モデルの概要
・リファレンス
・資格
App
ブック
・開く(フルパス/ダイアログ/Open済確認/非表示で開く)
・閉じる(上書き保存する/しない)
・保存(ダイアログ/自動(警告メッセージ出さない))
シート
・ワークシート名取得(WSコレクション)
・ワークシート名取得(ADO)
Range, Cell
・範囲 Range("A1","C3") / Range("A1:C3")
・離れたセル Range("A1,A3")
配列/コレクション/辞書
検索
ステートメント
■文字列
■日付/時刻
■フォーム
フォーム/ユーザーフォーム
フォーム/コンボボックス
フォーム/マルチページ(MultiPage)
■FSO
■Excel関数(Application.WorksheetFunction)
・関数一覧
■Tips
いろいろな 非表示/表示
'** '* いろいろな 非表示/表示 '* '* parg {} '* @arg_ref {} '* @arg_opt {} '* @ret {} '* '* @note '* Public Sub mthVisibleHidden() Set app = Application Set wb = Workbooks("Book1") Set ws = Worksheets("Sheet1") '// Excel 非表示/表示 app.Visible = False app.Visible = True '// ブック 非表示/表示 ' Workbooks("Book1").Visible 'NG app.Windows(wb.Name).Visible = False app.Windows(wb.Name).Visible = True '// ワークシート 非表示/表示 ws.Visible = xlSheetHidden ws.Visible = xlSheetVisible '// 行、列 非表示/表示 Set rng = ws.Rows(2) rng.Hidden = True rng.Hidden = False End Sub
資格
その他
・コントロール ー Caption改行
デザインモード [Ctrl]+[Ent]
・ダブルコーテーション:chr(34)
・シングルコーテーション:chr(39)
・2次元配列 行列変換
・Access UNION BETWEEN
・
VBA:Excel :ブック
ブックを開く
'************************************* '// ブックを開く '// '************************************* Sub ブックを開く_パス指定() Dim wb As Workbook '戻りを使わないときには、引数をカッコで括らないというのがVBAの文法です。 '戻りを使うのならカッコで引数を括らなければならない。 Set wb = Workbooks.Open(Filename:="D:\_Prog\Excel_VBA\■01_ブック\Sample.xlsx") MsgBox wb.Name MsgBox wb.Path wb.Close End Sub
ブックを閉じる
'************************************* '// ブックを閉じる '// 上書き保存/保存しない '************************************* Sub mth_CloseBook() Call ブックを開く_パス指定 Dim wb As Workbook Set wb = Workbooks("Sample.xlsx") Dim ws As Worksheet Set ws = wb.Worksheets(1) ws.Range("A1").Value = "dd" Dim bSaveFlg As Boolean Dim res As VbMsgBoxResult res = MsgBox("上書き保存?", vbYesNo) If res = vbYes Then bSaveFlg = True Else bSaveFlg = False End If If bSaveFlg Then wb.Close SaveChanges:=True Else wb.Close SaveChanges:=False End If End Sub
ブック OPEN確認
'******************************* '// Check book Opened '// ファイルOPEN確認 '******************************* Function mth_ChkBookOpen(ByVal sFile_fp As String) As Boolean On Error Resume Next Open sFile_fp For Append As #1 Close #1 If Err.Number > 0 Then mth_ChkBookOpen = True Else mth_ChkBookOpen = False End If End Function
ブック開く ダイアログ
'******************************* '// Open FileDialog '// ダイアログでファイルを開く '******************************* Sub mth_OpenFileDialog() Dim vFileName_fp As Variant '// GetOpenFilename : 「キャンセル」の戻り値は、bool型 vFileName_fp = Application.GetOpenFilename("Excel Book,*.xsl?") If VarType(vFileName_fp) = vbBoolean Then MsgBox "Cancel" Else Workbooks.Open vFileName_fp End If End Sub
他のブックのプロシージャ呼び出し
'**************************************** '// 他のブックのプロシージャ呼び出し '**************************************** Sub mth_他のブックのプロシージャ呼び出し() Dim sFile_fp As String sFile_fp = "D:\_Prog\Excel_VBA\■01_ブック\Sample_VBA.xlsm" Debug.Print Application.Run("'D:\_Prog\Excel_VBA\■01_ブック\Sample_VBA.xlsm'!Test", 2) Debug.Print Application.Run("'" & sFile_fp & "'" & "!Test", 2) End Sub
VBA:Excel :ブック
ブックを開く
'************************************* '// ブックを開く '// '************************************* Sub ブックを開く_パス指定() Dim wb As Workbook '戻りを使わないときには、引数をカッコで括らないというのがVBAの文法です。 '戻りを使うのならカッコで引数を括らなければならない。 Set wb = Workbooks.Open(Filename:="D:\_Prog\Excel_VBA\■01_ブック\Sample.xlsx") MsgBox wb.Name MsgBox wb.Path wb.Close End Sub
ブックを閉じる
'************************************* '// ブックを閉じる '// 上書き保存/保存しない '************************************* Sub mth_CloseBook() Call ブックを開く_パス指定 Dim wb As Workbook Set wb = Workbooks("Sample.xlsx") Dim ws As Worksheet Set ws = wb.Worksheets(1) ws.Range("A1").Value = "dd" Dim bSaveFlg As Boolean Dim res As VbMsgBoxResult res = MsgBox("上書き保存?", vbYesNo) If res = vbYes Then bSaveFlg = True Else bSaveFlg = False End If If bSaveFlg Then wb.Close SaveChanges:=True Else wb.Close SaveChanges:=False End If End Sub
ブック OPEN確認
[Excel作業をVBAで効率化]ブックが開かれているかチェックする
[Office TANAKA]ブックが開かれているかどうか調べる
[Office TANAKA]共有ブックを開いているユーザーを調べる
'******************************* '// Check book Opened '// ファイルOPEN確認 '******************************* Function mth_ChkBookOpen(ByVal sFile_fp As String) As Boolean On Error Resume Next Open sFile_fp For Append As #1 Close #1 If Err.Number > 0 Then mth_ChkBookOpen = True Else mth_ChkBookOpen = False End If End Function
ブック開く ダイアログ
'******************************* '// Open FileDialog '// ダイアログでファイルを開く '******************************* Sub mth_OpenFileDialog() Dim vFileName_fp As Variant '// GetOpenFilename : 「キャンセル」の戻り値は、bool型 vFileName_fp = Application.GetOpenFilename("Excel Book,*.xsl?") If VarType(vFileName_fp) = vbBoolean Then MsgBox "Cancel" Else Workbooks.Open vFileName_fp End If End Sub
他のブックのプロシージャ呼び出し
'**************************************** '// 他のブックのプロシージャ呼び出し '**************************************** Sub mth_他のブックのプロシージャ呼び出し() Dim sFile_fp As String sFile_fp = "D:\_Prog\Excel_VBA\■01_ブック\Sample_VBA.xlsm" Debug.Print Application.Run("'D:\_Prog\Excel_VBA\■01_ブック\Sample_VBA.xlsm'!Test", 2) Debug.Print Application.Run("'" & sFile_fp & "'" & "!Test", 2) End Sub
名前を付けて保存 (xlsm --> xlsx)
'**************************************** '// 名前を付けて保存(ダイアログ) '// xlsm --> xlsx '**************************************** Sub mth_SaveAs_xlsx() Dim strFileName As String strFileName = ThisWorkbook.Path & "\" _ & "SAMPLE_" & Format(Now, "yyyymmddhhmmssms") & ".xlsx" Dim FileName As Variant FileName = Application.GetSaveAsFilename( _ InitialFileName:=strFileName, _ FileFilter:="Excelファイル,*.xlsx" _ ) If FileName = False Then Exit Sub End If Application.DisplayAlerts = False '警告メッセージ非表示 ActiveWorkbook.SaveAs _ FileName:=FileName, _ FileFormat:=xlWorkbookDefault Application.DisplayAlerts = True '警告メッセージ非表示解除 End Sub '**************************************** '// 名前を付けて保存(自動) '// xlsm --> xlsx '**************************************** Sub mth_SaveAs_xlsx2() Dim strFileName As String strFileName = ThisWorkbook.Path & "\" _ & "SAMPLE_" & Format(Now, "yyyymmddhhmmssms") & ".xlsx" '名前を付けて保存(.xlsx) Application.DisplayAlerts = False '警告メッセージ非表示 ThisWorkbook.SaveAs FileName:=strFileName, FileFormat:=xlOpenXMLWorkbook Application.DisplayAlerts = True '警告メッセージ非表示解除 End Sub
Sub Sample() Dim strFileName As String strFileName = "D:\_Prog\Excel_VBA\■01_ブック\■01_ブック - コピー.xlsm" Dim strFileName_2 As String strFileName_2 = "D:\_Prog\Excel_VBA\■01_ブック\SAMPLE_20201110215605115_SSS.xlsx" If strFileName = "False" Then MsgBox "ファイル選択をキャンセルしました" Exit Sub End If Dim wb As Workbook Application.DisplayAlerts = False '警告メッセージ非表示 Application.EnableEvents = False '★マクロ起動 無効 Set wb = Workbooks.Open(strFileName) ActiveWindow.Visible = False wb.SaveAs FileName:=strFileName_2, FileFormat:=xlOpenXMLWorkbook wb.Close Application.EnableEvents = True '★ Application.DisplayAlerts = True '警告メッセージ非表示解除 End Sub
リファレンス
VBA:Excel :ADO:Access
第4話 DAOとADOの違い【連載】実務で使えるAccessのコツ - itstaffing エンジニアスタイル
配列?構造体?いやレコードセットでしょ!エクセルVBA - ぼくLog
https://sowel.co.jp/PDF_file/VB6/HowToUseRecordSet.pdf
DB Class化
プロパティ
■clsAccDB
Option Explicit Private Const sPROVIDER_ As String = "Microsoft.ACE.OLEDB.12.0" Private sDataSrc_ As String Private sTbName_ As String Private dmy_ ' // データソース Property Get pDataSrc() As String pDataSrc = sDataSrc_ End Property Property Let pDataSrc(sDataSrc As String) sDataSrc_ = sDataSrc End Property ' // テーブル名 Property Get pTbName() As String pTbName = sTbName_ End Property Property Let pTbName(sTbName As String) sTbName_ = sTbName End Property
DB作成、削除
■clsAccDB
'*************************************************** '* DB作成 '* '* '* @Arg01 {} '* @Arg02 {} '* @Ret {} '* @Note '* '*************************************************** Sub mthDB_Create() '参照設定:Microsoft ADO Ext.6.0 for DDL and Security Dim cat As ADOX.Catalog Dim ConStr As String On Error GoTo ErrHandler '新規作成するデータベースのパスと名前 'Access 2007以降(accdb ファイル) ConStr = "Provider=" & sPROVIDER_ & ";" _ & " Data Source=" & sDataSrc_ 'Access 2003以前(mdb ファイル) 'DBFile = ActiveWorkbook.Path & "\mydb2.mdb" 'ConStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sDataSrc_ Set cat = New ADOX.Catalog cat.Create ConStr ErrHandler: If Err.Number <> 0 Then MsgBox Err.Number & vbCrLf & Err.Description End If Set cat = Nothing End Sub
'*************************************************** '* DB削除 '* '* '* @Arg01 {} '* @Arg02 {} '* @Ret {} '* @Note '* '*************************************************** Sub mthDB_Delete() Dim FSO As FileSystemObject Set FSO = New FileSystemObject FSO.DeleteFile (sDataSrc_) Set FSO = Nothing End Sub
'*************************************************** '* DB存在確認 '* '* '* @Arg01 {} '* @Arg02 {} '* @Ret {} '* @Note '* '*************************************************** Function mthDB_IsExists() As Boolean Dim FSO As FileSystemObject Set FSO = New FileSystemObject mthDB_IsExists = FSO.FileExists(sDataSrc_) Set FSO = Nothing End Function
■modAccDB
'*************************************************** '* CraateDB '* '* @Arg01 {} '* @Arg02 {} '* @Ret {} '* @Note '* '*************************************************** Public Sub mthDB_Create() Dim c As clsAccDB Set c = New clsAccDB c.pDataSrc = ThisWorkbook.Path & "\test.accdb" If c.mthDB_IsExists Then Call c.mthDB_Delete End If Call c.mthDB_Create End Sub
'*************************************************** '* DeleteDB '* '* @Arg01 {} '* @Arg02 {} '* @Ret {} '* @Note '* '*************************************************** Public Sub mthDB_Delete() Dim c As clsAccDB Set c = New clsAccDB c.pDataSrc = ThisWorkbook.Path & "\test.accdb" If c.mthDB_IsExists Then Call c.mthDB_Delete End If End Sub
TB 存在確認、作成、削除
TB存在確認
'*************************************************** '* [cls]TB存在確認 '* '* @Arg01 {} '* @Arg02 {} '* @Ret {} '* @Note '* '*************************************************** Public Function mthTB_IsExist() '参照設定:Microsoft ADO Ext.6.0 for DDL and Security mthTB_IsExist = False Dim cat As ADOX.Catalog Dim tbl As ADOX.Table Dim ConStr As String On Error GoTo ErrHandler 'データベースのパスと名前 'Access 2007以降(accdb ファイル) ConStr = "Provider=" & sPROVIDER_ & ";" _ & " Data Source=" & sDataSrc_ 'Access 2003以前(mdb ファイル) 'DBFile = ActiveWorkbook.Path & "\mydb2.mdb" 'ConStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sDataSrc_ 'データベース接続 Set cat = New ADOX.Catalog cat.ActiveConnection = ConStr 'テーブルの確認 For Each tbl In cat.Tables Select Case tbl.Type Case "TABLE" If tbl.Name = sTbName_ Then mthTB_IsExist = True Exit Function End If End Select Next tbl ErrHandler: If Err.Number <> 0 Then MsgBox Err.Number & vbCrLf & Err.Description End If Set cat = Nothing Set tbl = Nothing End Function
TB作成
'*************************************************** '* [cls]TB作成 '* '* @Arg01 {} '* @Arg02 {} '* @Ret {} '* @Note '* '*************************************************** Public Sub mthTB_Create( _ ByVal dic_Fld As Dictionary _ ) '参照設定:Microsoft ADO Ext.6.0 for DDL and Security Dim cat As ADOX.Catalog Dim tbl As ADOX.Table Dim ConStr As String On Error GoTo ErrHandler 'データベースのパスと名前 'Access 2007以降(accdb ファイル) ConStr = "Provider=" & sPROVIDER_ & ";" _ & " Data Source=" & sDataSrc_ 'データベース接続 Set cat = New ADOX.Catalog cat.ActiveConnection = ConStr 'テーブルの作成 Set tbl = New ADOX.Table tbl.Name = sTbName_ Set tbl.ParentCatalog = cat 'フィールド(列)の設定 Dim v For Each v In dic_Fld tbl.Columns.Append v, dic_Fld(v) Next v 'テータベースへ登録 cat.Tables.Append tbl ErrHandler: If Err.Number <> 0 Then MsgBox Err.Number & vbCrLf & Err.Description End If Set cat = Nothing Set tbl = Nothing End Sub
'*************************************************** '* [mod]TB作成 '* '* @Arg01 {} '* @Arg02 {} '* @Ret {} '* @Note '* '*************************************************** Public Sub mthTB_Create() '参照設定:Microsoft ADO Ext.6.0 for DDL and Security '------------------------------------------------------------------------- Dim dic_Fld As Dictionary Set dic_Fld = New Dictionary 'フィールド(列)の設定 dic_Fld.Add "登録ID", adInteger dic_Fld.Add "氏名", adVarWChar dic_Fld.Add "生年月日", adDate dic_Fld.Add "備考", adLongVarWChar '------------------------------------------------------------------------- Dim c As clsAccDB Set c = New clsAccDB c.pDataSrc = ThisWorkbook.Path & "\test.accdb" c.pTbName = "TB_TEST" If Not c.mthTB_IsExist Then Call c.mthTB_Create(dic_Fld) End If End Sub
TB削除
'*************************************************** '* [cls]TB削除 '* '* @Arg01 {} '* @Arg02 {} '* @Ret {} '* @Note '* '*************************************************** Public Sub mthTB_Delete() Dim cat As ADOX.Catalog Dim tbl As ADOX.Table Dim ConStr As String On Error GoTo ErrHandler 'データベースのパスと名前 'Access 2007以降(accdb ファイル) ConStr = "Provider=" & sPROVIDER_ & ";" _ & " Data Source=" & sDataSrc_ 'データベース接続 Set cat = New ADOX.Catalog cat.ActiveConnection = ConStr 'テーブルの削除 cat.Tables.Delete sTbName_ ErrHandler: If Err.Number <> 0 Then MsgBox Err.Number & vbCrLf & Err.Description End If Set cat = Nothing Set tbl = Nothing End Sub
'*************************************************** '* [mod]TB削除 '* '* @Arg01 {} '* @Arg02 {} '* @Ret {} '* @Note '* '*************************************************** Public Sub mthTB_Delete() Dim c As clsAccDB Set c = New clsAccDB c.pDataSrc = ThisWorkbook.Path & "\test.accdb" c.pTbName = "TB_TEST" If c.mthTB_IsExist Then Call c.mthTB_Delete End If End Sub
INSERT
Dim colc_mrg As Collection Set colc_mrg = New Collection Dim colc As Collection Set colc = New Collection colc.Add (Array("登録ID", 1)) colc.Add (Array("氏名", "a")) colc.Add (Array("生年月日", "1971/1/1")) colc.Add (Array("備考", "note")) Call colc_mrg.Add(colc) Set colc = New Collection colc.Add (Array("登録ID", 2)) colc.Add (Array("氏名", "b")) colc.Add (Array("生年月日", "1971/1/2")) colc.Add (Array("備考", "note2")) Call colc_mrg.Add(colc) Call c.mthInsert(colc_mrg)
'** '* INSERT '* '* parg {} '* @arg_ref {} '* @arg_opt {} '* @ret {} '* '* @note '* Sub mthInsert(colc As Collection) '================== ' 接続文字列 '================== Dim cnstr As String cnstr = "Provider = " & sPROVIDER_ & ";" & _ "Data Source = " & sDataSrc_ & ";" '================== ' 接続(cn) '================== Dim cn As ADODB.Connection Set cn = New ADODB.Connection cn.ConnectionString = cnstr cn.Open '================================ ' レコードセット(rs SQL,DB) '================================ Dim rs As ADODB.Recordset Set rs = New ADODB.Recordset rs.Open sTbName_, cn, adOpenDynamic, adLockOptimistic Dim r As Long Dim c As Long For r = 1 To colc.Count rs.AddNew For c = 1 To colc.Item(r).Count ' Debug.Print colc.Item(r)(c)(0) ' Debug.Print colc.Item(r)(c)(1) rs.Fields(colc.Item(r)(c)(0)) = colc.Item(r)(c)(1) Next c rs.Update Next r '================================ ' CLOSE '================================ rs.Close: Set rs = Nothing cn.Close: Set cn = Nothing End Sub
Collection(Collection((Array))
Sub dmy() ' Dim arrFld(), arrVal() ' ReDim arrFld(colc.Item(r).Count - 1) ' ReDim arrVal(colc.Item(r).Count - 1) For c = 1 To colc.Item(r).Count ' Debug.Print colc.Item(r)(c)(0) ' Debug.Print colc.Item(r)(c)(1) ' arrFld(c - 1) = colc.Item(r)(c)(0) ' arrVal(c - 1) = colc.Item(r)(c)(1) rs.Fields(colc.Item(r)(c)(0)) = colc.Item(r)(c)(1) Next c ' Dim sFld As String ' Dim sVal As String ' sFld = "([" & Join(arrFld, "],[") & "])" ' sVal = "('" & Join(arrVal, "','") & "')" ' ' 'INSERT INTO syain(id,name,romaji) VALUES (1,'鈴木','suzuki'); ' Dim sSQL As String ' sSQL = "" ' sSQL = sSQL & "INSERT INTO " & sTbName_ & sFld ' sSQL = sSQL & " VALUES" & sVal ' sSQL = sSQL & ";" End Sub
JOIN(FULL)
'** '* FULL JOIN '* '* parg {} '* @arg_ref {} '* @arg_opt {} '* @ret {} '* '* @note '* Sub mth_JoinFull() '================== ' 接続文字列 '================== Dim cnstr As String cnstr = "Provider = " & sPROVIDER_ & ";" & _ "Data Source = " & sDataSrc_ & ";" '================== ' 接続(cn) '================== Dim cn As ADODB.Connection Set cn = New ADODB.Connection cn.ConnectionString = cnstr cn.Open '================================ ' レコードセット(rs SQL,DB) '================================ Dim rs As ADODB.Recordset Set rs = New ADODB.Recordset '================================ ' SQL '================================ Dim sSQL As String sSQL = "" sSQL = sSQL & "SELECT" sSQL = sSQL & " [T1.File],[T2.File],[T1.Sheet1],[T2.Sheet2],[T1.Code],[T2.Code]," sSQL = sSQL & " [T1.Data01],[T1.Data02],[T2.Data11],[T2.Data12]" sSQL = sSQL & " FROM TB_01 AS T1" sSQL = sSQL & " LEFT JOIN TB_02 AS T2" sSQL = sSQL & " ON T1.File = T2.File" sSQL = sSQL & " AND T1.Code = T2.Code" sSQL = sSQL & " Union " sSQL = sSQL & "SELECT" sSQL = sSQL & " [T1.File],[T2.File],[T1.Sheet1],[T2.Sheet2],[T1.Code],[T2.Code]," sSQL = sSQL & " [T1.Data01],[T1.Data02],[T2.Data11],[T2.Data12]" sSQL = sSQL & " FROM TB_01 AS T1" sSQL = sSQL & " RIGHT JOIN TB_02 AS T2" sSQL = sSQL & " ON T1.File = T2.File" sSQL = sSQL & " AND T1.Code = T2.Code" rs.Open sSQL, cn, adOpenStatic #If Win64 Then Dim lRecord_Cnt As LongLong ' 64ビット版のとき #Else Dim lRecord_Cnt As Long ' 32ビット版のとき #End If Dim lField_Cnt As Long lRecord_Cnt = rs.RecordCount lField_Cnt = rs.Fields.Count ThisWorkbook.Worksheets("AccDB").Cells.ClearContents ThisWorkbook.Worksheets("AccDB").Range("A1").CopyFromRecordset rs 'RecordSetをシートに貼り付け rs.MoveFirst '================================ ' RESULT '================================ #If Win64 Then Dim r As LongLong ' 64ビット版のとき #Else Dim r As Long ' 32ビット版のとき #End If Dim c As Long lRecord_Cnt = rs.RecordCount lField_Cnt = rs.Fields.Count Dim colcRecordAll As Collection Set colcRecordAll = New Collection For r = 0 To lRecord_Cnt - 1 Dim colcBuf As Collection Set colcBuf = New Collection For c = 0 To lField_Cnt - 1 Dim vDatPair() Dim sFldName As String sFldName = rs.Fields.Item(c).Name Dim sData As String If IsNull(rs.Fields(sFldName)) Then sData = "" Else sData = rs.Fields(sFldName) End If vDatPair = Array(sFldName, sData) Call colcBuf.Add(vDatPair) Next c Call colcRecordAll.Add(colcBuf) rs.MoveNext Next r '================================ ' CLOSE '================================ rs.Close: Set rs = Nothing cn.Close: Set cn = Nothing End Sub
接続文字列
Const sDataSorce As String = "D:\Northwind.accdb" Const sPASS As String = "pass"
DB接続
'■■■■■■■■■■■■■■■■■■■■■■■■■ '// DB接続 '// '■■■■■■■■■■■■■■■■■■■■■■■■■ Private Sub psub_DB接続() Call mth_OpenDB End Sub '************************************************************************* '機能 : DB接続 '戻り値 : 'ARG1 : '説明 : '************************************************************************* Public Sub mth_OpenDB() '================== ' 接続文字列 '================== Dim cnstr As String cnstr = "Provider = Microsoft.ACE.OLEDB.12.0;" & _ "Data Source = " & sDataSorce & ";" & _ "Jet OLEDB:Database Password = " & sPASS & ";" '================== ' 接続(cn) '================== Dim cn As ADODB.Connection Set cn = New ADODB.Connection cn.ConnectionString = cnstr cn.Open '================================ ' CLOSE '================================ cn.Close: Set cn = Nothing End Sub
選択
SELECT [First Name],[Job Title] FROM Employees WHERE City = 'Seattle'
「Sales」を含む
SELECT [First Name],[Job Title] FROM Employees WHERE [Job Title] LIKE '%Sales%'
0文字以上:%
1文字:_
『SELECT』ByRef collect(FLD_NAME, Value)
'■■■■■■■■■■■■■■■■■■■■■■■■■ '// 『SELECT』テーブルの全データをシートに出力 '// '■■■■■■■■■■■■■■■■■■■■■■■■■ '********************************************************* '* SELECT '* '* parg {} '* @arg_ref {} '* @arg_opt {} '* @ret {} '* '* @note '* '********************************************************* Sub mth_Select( _ ByVal sSQL As String, _ ByRef Ref_colcDatas As Collection _ ) Dim i As Long 'Ref_collection Delete For i = 1 To Ref_colcDatas.Count Ref_colcDatas.Remove 1 Next i '## 接続文字列 ##################### Dim cnstr As String cnstr = "Provider = " & sPROVIDER_ & ";" & _ "Data Source = " & sDataSrc_ & ";" '## 接続(cn) ##################### Dim cn As ADODB.Connection Set cn = New ADODB.Connection cn.ConnectionString = cnstr cn.Open '## レコードセット(rs SQL,DB) ##################### Dim rs As ADODB.Recordset Set rs = New ADODB.Recordset rs.CursorLocation = adUseClient rs.Open sSQL, cn, adOpenDynamic, adLockReadOnly '## Result ################## If rs.RecordCount < 1 Then GoTo END_PROC Do Until rs.EOF Dim colcFldDats As Collection Set colcFldDats = New Collection For i = 0 To rs.Fields.Count - 1 colcFldDats.Add Array(rs.Fields(i).Name, rs.Fields(rs.Fields(i).Name).Value) Next i Ref_colcDatas.Add colcFldDats Set colcFldDats = Nothing rs.MoveNext Loop '## CLOSE ####################################### rs.Close cn.Close END_PROC: Set rs = Nothing Set cn = Nothing End Sub
『SELECT』テーブルの全データをシートに出力
'■■■■■■■■■■■■■■■■■■■■■■■■■ '// 『SELECT』テーブルの全データをシートに出力 '// '■■■■■■■■■■■■■■■■■■■■■■■■■ Private Sub psub_テーブルデータをシートに出力() Dim ws As Worksheet Set ws = ThisWorkbook.Sheets(1) ws.Cells.Clear ws.Activate Dim rngStartCell As Range Set rngStartCell = ws.Range("B2") '// 処理 Call mth_SelectAll_CopyFormRs(rngStartCell) ws.Cells.EntireColumn.AutoFit Set rngStartCell = Nothing Set ws = Nothing End Sub '************************************************************************* '機能 : 『SELECT』テーブルの全データをシートに出力 '戻り値 : 'ARG1 : '説明 : '************************************************************************* Public Sub mth_SelectAll_CopyFormRs(rngStartCell As Range) '================== ' 接続文字列 '================== Dim cnstr As String cnstr = "Provider = Microsoft.ACE.OLEDB.12.0;" & _ "Data Source = " & sDataSorce & ";" & _ "Jet OLEDB:Database Password = " & sPASS & ";" '================== ' 接続(cn) '================== Dim cn As ADODB.Connection Set cn = New ADODB.Connection cn.ConnectionString = cnstr cn.Open '================================ ' レコードセット(rs SQL,DB) '================================ Dim rs As ADODB.Recordset Set rs = New ADODB.Recordset Dim sSQL As String sSQL = "SELECT * FROM Employees" rs.Open sSQL, cn, adOpenStatic, adLockReadOnly '================================ ' RESULT '================================ rngStartCell.CopyFromRecordset rs '================================ ' CLOSE '================================ cn.Close: Set cn = Nothing End Sub
『SELECT』フィールド指定:データをシートに出力
'■■■■■■■■■■■■■■■■■■■■■■■■■ '// 『SELECT』フィールド指定:データをシートに出力 '// '■■■■■■■■■■■■■■■■■■■■■■■■■ Private Sub psub_フィールド指定_データをシートに出力() Dim ws As Worksheet Set ws = ThisWorkbook.Sheets(1) ws.Cells.Clear ws.Activate Dim rngStartCell As Range Set rngStartCell = ws.Range("B2") '// 処理 Call mth_SelectFld_CopyFormRs(rngStartCell) ws.Cells.EntireColumn.AutoFit Set rngStartCell = Nothing Set ws = Nothing End Sub '************************************************************************* '機能 : 『SELECT』フィールド指定:データをシートに出力 '戻り値 : 'ARG1 : '説明 : '************************************************************************* Public Sub mth_SelectFld_CopyFormRs(rngStartCell As Range) '================== ' 接続文字列 '================== Dim cnstr As String cnstr = "Provider = Microsoft.ACE.OLEDB.12.0;" & _ "Data Source = " & sDataSorce & ";" & _ "Jet OLEDB:Database Password = " & sPASS & ";" '================== ' 接続(cn) '================== Dim cn As ADODB.Connection Set cn = New ADODB.Connection cn.ConnectionString = cnstr cn.Open '================================ ' レコードセット(rs SQL,DB) '================================ Dim rs As ADODB.Recordset Set rs = New ADODB.Recordset Dim sSQL As String sSQL = "SELECT [Last Name],[Job Title] FROM Employees" rs.Open sSQL, cn, adOpenStatic, adLockReadOnly '================================ ' RESULT '================================ rngStartCell.CopyFromRecordset rs '================================ ' CLOSE '================================ cn.Close: Set cn = Nothing End Sub
『WHERE』条件_データをシートに出力
'■■■■■■■■■■■■■■■■■■■■■■■■■ '// 『WHERE』条件_データをシートに出力 '// '■■■■■■■■■■■■■■■■■■■■■■■■■ Private Sub psub_条件_データをシートに出力() Dim ws As Worksheet Set ws = ThisWorkbook.Sheets(1) ws.Cells.Clear ws.Activate Dim rngStartCell As Range Set rngStartCell = ws.Range("B2") '// 処理 Call mth_SelectWhere_CopyFormRs(rngStartCell) ws.Cells.EntireColumn.AutoFit Set rngStartCell = Nothing Set ws = Nothing End Sub '************************************************************************* '機能 : 『WHERE』条件_データをシートに出力 '戻り値 : 'ARG1 : '説明 : '************************************************************************* Public Sub mth_SelectWhere_CopyFormRs(rngStartCell As Range) '================== ' 接続文字列 '================== Dim cnstr As String cnstr = "Provider = Microsoft.ACE.OLEDB.12.0;" & _ "Data Source = " & sDataSorce & ";" & _ "Jet OLEDB:Database Password = " & sPASS & ";" '================== ' 接続(cn) '================== Dim cn As ADODB.Connection Set cn = New ADODB.Connection cn.ConnectionString = cnstr cn.Open '================================ ' レコードセット(rs SQL,DB) '================================ Dim rs As ADODB.Recordset Set rs = New ADODB.Recordset Dim sSQL As String sSQL = "" sSQL = sSQL & "SELECT * FROM [Order Details]" 'sSQL = sSQL & " WHERE [Quantity] >= 100" rs.Open sSQL, cn, adOpenStatic, adLockReadOnly '================================ ' RESULT '================================ rngStartCell.CopyFromRecordset rs '================================ ' CLOSE '================================ cn.Close: Set cn = Nothing End Sub
『LIKE』パタンマッチ_データをシートに出力
'■■■■■■■■■■■■■■■■■■■■■■■■■ '// 『LIKE』パタンマッチ_データをシートに出力 '// '■■■■■■■■■■■■■■■■■■■■■■■■■ Private Sub psub_パタンマッチ_データをシートに出力() Dim ws As Worksheet Set ws = ThisWorkbook.Sheets(1) ws.Cells.Clear ws.Activate Dim rngStartCell As Range Set rngStartCell = ws.Range("B2") '// 処理 Call mth_SelectLike_CopyFormRs(rngStartCell) ws.Cells.EntireColumn.AutoFit Set rngStartCell = Nothing Set ws = Nothing End Sub '************************************************************************* '機能 : 『LIKE』パタンマッチ_データをシートに出力 '戻り値 : 'ARG1 : '説明 : [%:0以上][_:1文字] '************************************************************************* Public Sub mth_SelectLike_CopyFormRs(rngStartCell As Range) '================== ' 接続文字列 '================== Dim cnstr As String cnstr = "Provider = Microsoft.ACE.OLEDB.12.0;" & _ "Data Source = " & sDataSorce & ";" & _ "Jet OLEDB:Database Password = " & sPASS & ";" '================== ' 接続(cn) '================== Dim cn As ADODB.Connection Set cn = New ADODB.Connection cn.ConnectionString = cnstr cn.Open '================================ ' レコードセット(rs SQL,DB) '================================ Dim rs As ADODB.Recordset Set rs = New ADODB.Recordset Dim sSQL As String sSQL = "" sSQL = sSQL & "SELECT [Last Name],[Job Title] FROM Employees" sSQL = sSQL & " WHERE [Job Title] Like 'Sales%'" rs.Open sSQL, cn, adOpenStatic, adLockReadOnly '================================ ' RESULT '================================ rngStartCell.CopyFromRecordset rs '================================ ' CLOSE '================================ cn.Close: Set cn = Nothing End Sub
SELECT * FROM TB
'************************************* ' SELECT * FROM TB ' '************************************* Private Sub btn_04_05_DB接続_Click() '=========================== '// 出力先 開始セル '=========================== Dim ws As Worksheet Set ws = ThisWorkbook.Worksheets("RES") Dim ws_Range As Range Set ws_Range = ws.Range("A2") '=========================== '// 実行 '=========================== Dim varrTB() As Variant varrTB = mth_0405_DB_Select(ws_Range) '=========================== '// TB 2次元配列データ '=========================== Dim lRow_Last As Long lRow_Last = ws.Cells(Rows.Count, 1).End(xlUp).Row Dim lRow As Long: lRow = UBound(varrTB, 1) - LBound(varrTB, 1) + 1 Dim lCol As Long: lCol = UBound(varrTB, 2) - LBound(varrTB, 2) + 1 ' Out 1 ws.Range("A13", Cells(lRow - 1 + 13, lCol - 1)).Value = varrTB ' Out 2 lRow_Last = ws.Cells(Rows.Count, 1).End(xlUp).Row ws.Range( _ "A" & lRow_Last + 2, _ ws.Range("A" & lRow_Last + 2).Offset(lRow - 1, lCol - 1)).Value = varrTB End Sub '************************************* ' SELECT * FROM TB ' '************************************* Function mth_0405_DB_Select(ByVal ws_Range As Range) As Variant() Dim lRecord As Long Dim lField As Long Dim varrTB() As Variant '================== ' 接続文字列 '================== Dim cnstr As String cnstr = "Provider = Microsoft.ACE.OLEDB.12.0;" & _ "Data Source = " & "D:\Northwind.accdb;" & _ "Jet OLEDB:Database Password = " & ps_PASS & ";" '================== ' 接続(cn) '================== Dim cn As ADODB.Connection Set cn = New ADODB.Connection cn.ConnectionString = cnstr cn.Open '================================ ' レコードセット(rs SQL,DB) '================================ Dim rs As ADODB.Recordset Set rs = New ADODB.Recordset Dim sSQL As String sSQL = "SELECT * FROM Employees" rs.Open sSQL, cn, adOpenStatic, adLockReadOnly '================================ ' RESULT '================================ Dim lRecord_Cnt As Long Dim lField_Cnt As Long lRecord_Cnt = rs.RecordCount lField_Cnt = rs.Fields.Count Dim vArr2D() As Variant '---------------------------------- ' 配列格納1 '---------------------------------- ReDim vArr2D(lRecord_Cnt - 1, lField_Cnt - 1) varrTB = rs.GetRows '※配列(列,行) 一括読込み ' Dim vArr2D As Variant ' '// Transpose : 配列に Null 値を含めることはできません。 ' vArr2D = WorksheetFunction.Transpose(varrTB) Dim iRec As Long Dim iCol As Long For iRec = LBound(varrTB, 2) To UBound(varrTB, 2) For iCol = LBound(varrTB, 1) To UBound(varrTB, 1) vArr2D(iRec, iCol) = varrTB(iCol, iRec) Next Next '---------------------------------- ' 配列格納2 '---------------------------------- ReDim vArr2D(lRecord_Cnt - 1, lField_Cnt - 1) rs.MoveFirst For iRec = 0 To lRecord_Cnt - 1 For iCol = 0 To lField_Cnt - 1 vArr2D(iRec, iCol) = rs.Fields.Item(iCol).Value Next rs.MoveNext Next '---------------------------------- ' 一括セル出力 '---------------------------------- ' rs.MoveFirst ' ThisWorkbook.Sheets("RES").Range("A2").CopyFromRecordset rs rs.MoveFirst ws_Range.CopyFromRecordset rs '---------------------------------- ' フィールド名 '---------------------------------- Dim sFldName() As String ReDim sFldName(lField_Cnt - 1) rs.MoveFirst For iCol = 0 To lField_Cnt - 1 sFldName(iCol) = rs.Fields.Item(iCol).Name Next '---------------------------------- ' 指定FLD レコード 配列格納 '---------------------------------- Dim vRecDat_1() As Variant 'nullを想定 Dim vRecDat_2() As Variant ReDim vRecDat_1(lRecord_Cnt - 1) ReDim vRecDat_2(lRecord_Cnt - 1) rs.MoveFirst For iRec = 0 To lRecord_Cnt - 1 vRecDat_1(iRec) = rs.Fields("Last Name") vRecDat_2(iRec) = rs.Fields("First Name") rs.MoveNext Next '================================ ' CLOSE '================================ rs.Close: Set rs = Nothing cn.Close: Set cn = Nothing ' mth_0405_DB_Select = vArr2D End Function
C#:ADO(接続型)
DB接続
DB接続/状態確認/CLOSE
cnstr :
cn(cnstr)
cn → cn.Open
cn → cn.State
cn → cn.Close
private void DB接続_状態確認() { OleDbConnectionStringBuilder cnstr = new OleDbConnectionStringBuilder(); cnstr["Provider"] = _sProvider; cnstr["Data Source"] = _sDataSorce; cnstr["Jet OLEDB:Database Password"] = _sPass; OleDbConnection cn = new OleDbConnection(cnstr.ToString()); try { cn.Open(); System.Windows.Forms.MessageBox.Show($"接続状態 : {cn.State}", "通知"); cn.Close(); System.Windows.Forms.MessageBox.Show($"接続状態 : {cn.State}", "通知"); } catch (Exception ex) { System.Windows.Forms.MessageBox.Show(ex.Message, "通知"); } }
スキーマ
スキーマ(schema):データベースの構造
テーブルリスト (cn.GetOleDbSchemaTable)
cnstr :
cn(cnstr)
cn → cn.Open
■DataTable dt = cn.GetOleDbSchemaTable
cn → cn.Close
dt.Rows()["TABLE_NAME"]
//##################################################### /// <summary> /// テーブルリスト /// </summary> /// <param name="sender"></param> /// <param name="e"></param> //##################################################### private void btn_テーブルリスト_Click(object sender, EventArgs e) { DataTable dt = mth_テーブルリスト(); this.dgv.DataSource = dt; this.dgv.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells; } /// *************************************************** /// <summary> /// テーブルリスト /// </summary> /// *************************************************** private DataTable mth_テーブルリスト() { OleDbConnectionStringBuilder cnstr = new OleDbConnectionStringBuilder(); cnstr["Provider"] = _sProvider; cnstr["Data Source"] = _sDataSorce; cnstr["Jet OLEDB:Database Password"] = _sPass; OleDbConnection cn = new OleDbConnection(cnstr.ToString()); cn.Open(); //--------------------------------------------------------------- // TBリスト ⇒ 「OleDbSchemaGuid.Tables」 // - OleDbSchemaGuid クラス // - Tables フィールド //--------------------------------------------------------------- /* * 「OleDbSchemaGuid」は、結果を「DataTable」で返す。 * 「DataTable」は、1テーブルの情報を1レコードとしてテーブルごとの情報が格納されている * このうち、テーブル名は3番目の[TABLE_NAME]フィールドに格納されている * https://docs.microsoft.com/ja-jp/dotnet/api/system.data.oledb.oledbschemaguid.tables?view=dotnet-plat-ext-3.1 * */ //DataTable dt = new DataTable(); DataTable dt = cn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" } ); cn.Close(); foreach (DataRow dr in dt.Rows) { //Console.WriteLine(string.Join(",", dr.ItemArray)); //Console.WriteLine(dr[2]); Console.WriteLine(dr["TABLE_NAME"]); } return dt; }
フィールドリスト (cn.GetOleDbSchemaTable)
cnstr :
cn(cnstr)
cn → cn.Open
■DataTable dt = cn.GetOleDbSchemaTable
cn → cn.Close
dt.Rows().["COLUMN_NAME"]
//##################################################### /// <summary> /// フィールドリスト /// </summary> /// <param name="sender"></param> /// <param name="e"></param> //##################################################### private void btn_フィールドリスト_Click(object sender, EventArgs e) { DataTable dt = mth_257_フィールドリスト(); this.dgv.DataSource = dt; this.dgv.Font = new Font("Arial", 8.00F, FontStyle.Regular); this.dgv.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells; } /// ************************************************************ /// <summary> /// フィールドリスト /// </summary> /// <returns></returns> /// ************************************************************ private DataTable mth_フィールドリスト() { OleDbConnectionStringBuilder cnstr = new OleDbConnectionStringBuilder(); cnstr["Provider"] = _sProvider; cnstr["Data Source"] = _sDataSorce; cnstr["Jet OLEDB:Database Password"] = _sPass; OleDbConnection cn = new OleDbConnection(cnstr.ToString()); cn.Open(); //--------------------------------------------------------------- // [社員]テーブル のFLDリスト ⇒ 「OleDbSchemaGuid.Columns」 // - OleDbSchemaGuid クラス // - Columns フィールド //--------------------------------------------------------------- /* * 「OleDbSchemaGuid」は、結果を「DataTable」で返す。 * 「DataTable」は、1テーブルの情報を1レコードとしてテーブルごとの情報が格納されている * このうち、フィールド名は4番目の[COLUMN_NAME]フィールドに格納されている * https://docs.microsoft.com/ja-jp/dotnet/api/system.data.oledb.oledbschemaguid.columns?view=dotnet-plat-ext-3.1 */ DataTable dt = cn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Columns, new object[] { null, null, "Employees", null } ); cn.Close(); foreach (DataRow dr in dt.Rows) { Console.WriteLine(dr[3]); Console.WriteLine(dr["COLUMN_NAME"]); } return dt; }
テーブル
テーブル作成 (CREATE TABLE)
cnstr :
cn(cnstr)
cn → cn.Open
■ sSQL = "CREATE TABLE TB_テスト(コード INT, 商品名 TEXT"
■ OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(sSQL, cn);
■cmd.ExecuteNonQuery
cn → cn.Close
※cmd.ExecuteNonQuery
接続に対して Transact-SQL ステートメントを実行し、影響を受けた行数を返します。
//********************************************* /// <summary> /// テーブル作成 /// </summary> //********************************************* private void mth_テーブル作成() { OleDbConnectionStringBuilder cnstr = new OleDbConnectionStringBuilder(); cnstr["Provider"] = _sProvider; cnstr["Data Source"] = _sDataSorce; cnstr["Jet OLEDB:Database Password"] = _sPass; OleDbConnection cn = new OleDbConnection(cnstr.ToString()); cn.Open(); //--------------------------------------------------------------- // TB 作成 //--------------------------------------------------------------- /* * SQLで「OleDbConnection」を生成 * 「ExecuteNonQuery」で実行 */ string sSQL = ""; sSQL += "CREATE TABLE テスト(コード INT, 商品名 TEXT)"; try { // Connection + SQL var cmd = new System.Data.OleDb.OleDbCommand(sSQL, cn); // ExecuteNonQuery cmd.ExecuteNonQuery(); System.Windows.Forms.MessageBox.Show("Create TB", "通知"); } catch (Exception ex) { System.Windows.Forms.MessageBox.Show(ex.Message, "通知"); } finally { cn.Close(); } }
テーブル削除 (DROP TABLE)
cnstr :
cn(cnstr)
cn → cn.Open
■ sSQL = "DROP TABLE TB_テスト";
■ OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(sSQL, cn);
■cmd.ExecuteNonQuery
cn → cn.Close
※cmd.ExecuteNonQuery
接続に対して Transact-SQL ステートメントを実行し、影響を受けた行数を返します。
//****************************************** /// <summary> /// テーブル削除 /// </summary> //****************************************** private void mth_テーブル削除() { OleDbConnectionStringBuilder cnstr = new OleDbConnectionStringBuilder(); cnstr["Provider"] = _sProvider; cnstr["Data Source"] = _sDataSorce; cnstr["Jet OLEDB:Database Password"] = _sPass; OleDbConnection cn = new OleDbConnection(cnstr.ToString()); cn.Open(); //--------------------------------------------------------------- // TB 削除 //--------------------------------------------------------------- /* * SQLで「OleDbConnection」を生成 * 「ExecuteNonQuery」で実行 */ string sSQL = ""; sSQL += "DROP TABLE TB_テスト"; try { // Connection + SQL var cmd = new System.Data.OleDb.OleDbCommand(sSQL, cn); // ExecuteNonQuery cmd.ExecuteNonQuery(); System.Windows.Forms.MessageBox.Show("Delete TB", "通知"); } catch (Exception ex) { System.Windows.Forms.MessageBox.Show(ex.Message, "通知"); } finally { cn.Close(); } }
レコード
レコート追加(INSERT)
cnstr :
cn(cnstr)
cn → cn.Open
■ sSQL = "INSERT INTO 社員 VALUES(20201101, 'Bob, 'SEC-001')";
■ OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(sSQL, cn);
■cmd.ExecuteNonQuery
cn → cn.Close
※cmd.ExecuteNonQuery
接続に対して Transact-SQL ステートメントを実行し、影響を受けた行数を返します。
//##################################################### /// <summary> /// レコード追加 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> //##################################################### private void btn_レコード追加_Click(object sender, EventArgs e) { mth_レコード追加(); } //****************************************** /// <summary> /// レコード追加 /// </summary> //****************************************** private void mth_レコード追加() { OleDbConnectionStringBuilder cnstr = new OleDbConnectionStringBuilder(); cnstr["Provider"] = _sProvider; cnstr["Data Source"] = _sDataSorce; cnstr["Jet OLEDB:Database Password"] = _sPass; OleDbConnection cn = new OleDbConnection(cnstr.ToString()); cn.Open(); //--------------------------------------------------------------- // Add Record //--------------------------------------------------------------- /* * SQLで「OleDbConnection」を生成 * 「ExecuteNonQuery」で実行 */ string sSQL = ""; sSQL += "INSERT INTO 社員 VALUES(20201101, 'Bob, 'SEC-001')"; //string sSQL = ""; sSQL = "INSERT INTO TB_社員 ([社員番号], [氏名], [部門コード])" ; sSQL += " VALUES(20201101, 'Bob', 'SEC-001')"; try { // Connection + SQL OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(sSQL, cn); // ExecuteNonQuery cmd.ExecuteNonQuery(); System.Windows.Forms.MessageBox.Show("Add Record", "通知"); } catch (Exception ex) { System.Windows.Forms.MessageBox.Show(ex.Message, "通知"); } finally { cn.Close(); } }
レコード更新 (UPDATE)
cnstr :
cn(cnstr)
cn → cn.Open
■ sSQL = "UPDATE TB_社員 SET 氏名 ='Jon' WHERE 社員番号=20201010";
■ OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(sSQL, cn);
■cmd.ExecuteNonQuery
cn → cn.Close
※cmd.ExecuteNonQuery
接続に対して Transact-SQL ステートメントを実行し、影響を受けた行数を返します。
//##################################################### /// <summary> /// レコード更新 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> //##################################################### private void btn_レコード更新_Click(object sender, EventArgs e) { mth_レコード更新(); } //********************************************* /// <summary> /// レコード更新 /// </summary> //********************************************* private void mth_レコード更新() { OleDbConnectionStringBuilder cnstr = new OleDbConnectionStringBuilder(); cnstr["Provider"] = _sProvider; cnstr["Data Source"] = _sDataSorce; cnstr["Jet OLEDB:Database Password"] = _sPass; OleDbConnection cn = new OleDbConnection(cnstr.ToString()); cn.Open(); //--------------------------------------------------------------- // Update Record //--------------------------------------------------------------- /* * SQLで「OleDbConnection」を生成 * 「ExecuteNonQuery」で実行 */ string sSQL = ""; sSQL += "UPDATE TB_社員 SET 氏名 ='Jon' WHERE 社員番号=20201010"; sSQL = "UPDATE TB_社員"; sSQL += " SET 氏名 ='Jon'"; sSQL += " WHERE 社員番号=20201010"; try { // Connection + SQL var cmd = new System.Data.OleDb.OleDbCommand(sSQL, cn); // ExecuteNonQuery cmd.ExecuteNonQuery(); System.Windows.Forms.MessageBox.Show("Update Record", "通知"); } catch (Exception ex) { System.Windows.Forms.MessageBox.Show(ex.Message, "通知"); } finally { cn.Close(); } }
レコード削除 (DELETE)
cnstr :
cn(cnstr)
cn → cn.Open
■ sSQL = "UPDATE TB_社員 SET 氏名 ='Jon' WHERE 社員番号=20201010";
■ OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(sSQL, cn);
■cmd.ExecuteNonQuery
cn → cn.Close
※cmd.ExecuteNonQuery
接続に対して Transact-SQL ステートメントを実行し、影響を受けた行数を返します。
//##################################################### /// <summary> /// レコード削除 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> //##################################################### private void btn_レコード削除_Click(object sender, EventArgs e) { mth_レコード削除(); } //******************************************** /// <summary> /// レコード削除 /// </summary> //******************************************** private void mth_レコード削除() { OleDbConnectionStringBuilder cnstr = new OleDbConnectionStringBuilder(); cnstr["Provider"] = _sProvider; cnstr["Data Source"] = _sDataSorce; cnstr["Jet OLEDB:Database Password"] = _sPass; OleDbConnection cn = new OleDbConnection(cnstr.ToString()); cn.Open(); //--------------------------------------------------------------- // Delete Record //--------------------------------------------------------------- /* * SQLで「OleDbConnection」を生成 * 「ExecuteNonQuery」で実行 */ string sSQL = ""; sSQL += "DELETE FROM TB_社員 WHERE 社員番号=20201010"; try { // Connection + SQL var cmd = new System.Data.OleDb.OleDbCommand(sSQL, cn); // ExecuteNonQuery cmd.ExecuteNonQuery(); System.Windows.Forms.MessageBox.Show("Delete Record", "通知"); } catch (Exception ex) { System.Windows.Forms.MessageBox.Show(ex.Message, "通知"); } finally { cn.Close(); } }
レコード参照 (SELECT)
cnstr :
cn(cnstr)
cn → cn.Open
■ sSQL = "SELECT * FROM TB_社員";
■ OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(sSQL, cn);
■cmd.ExecuteReader
■while (dr.Read())
cn → cn.Close
※cmd.ExecuteReader
Readメソッドを何度か呼び出したあと、最後のレコードに達して、現在のレコードをそれ以上進めることができない場合にReadメソッドはfalseを返す
//##################################################### /// <summary> /// レコード参照 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> //##################################################### private void btn_レコード参照_Click(object sender, EventArgs e) { mth_レコード参照(); } //************************************* /// <summary> /// レコード参照 /// </summary> //************************************* private void mth_レコード参照() { OleDbConnectionStringBuilder cnstr = new OleDbConnectionStringBuilder(); cnstr["Provider"] = _sProvider; cnstr["Data Source"] = _sDataSorce; cnstr["Jet OLEDB:Database Password"] = _sPass; OleDbConnection cn = new OleDbConnection(cnstr.ToString()); cn.Open(); //--------------------------------------------------------------- // Select Record ⇒ cmd.ExecuteReader //--------------------------------------------------------------- /* * SQLで「OleDbConnection」を生成 * 「ExecuteReader」で実行(レコード取得) * Readメソッドで1件ずつ参照 */ string sSQL = ""; sSQL += "SELECT * FROM TB_社員"; // Connection + SQL OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(sSQL, cn); // ExecuteReader OleDbDataReader dr = cmd.ExecuteReader(); while (dr.Read()) { //Console.WriteLine($"{dr.GetInt32(0)} : {dr.GetString(1)}"); Console.WriteLine("{0}, {1}, {2}", dr["社員番号"], dr["氏名"], dr["部門コード"]); } dr.Close(); cn.Close(); }
レコード数 (SELECT COUNT(*))
cnstr :
cn(cnstr)
cn → cn.Open
■ sSQL ="SELECT COUNT(*) FROM 社員";
■ OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(sSQL, cn);
■int num = (int)cmd.ExecuteScalar();
cn → cn.Close
※cmd.ExecuteScalar
クエリを実行し、クエリによって返される結果セットの先頭行の最初の列を返します。 その他の列または行は無視されます。
//##################################################### /// <summary> /// レコード数 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> //##################################################### private void btn_レコード数_Click(object sender, EventArgs e) { mth_レコード数(); } //************************************** /// <summary> /// レコード数 /// </summary> //************************************** private void mth_レコード数() { OleDbConnectionStringBuilder cnstr = new OleDbConnectionStringBuilder(); cnstr["Provider"] = _sProvider; cnstr["Data Source"] = _sDataSorce; cnstr["Jet OLEDB:Database Password"] = _sPass; OleDbConnection cn = new OleDbConnection(cnstr.ToString()); cn.Open(); //--------------------------------------------------------------- // COUNT(*) Record ⇒ cmd.ExecuteScalar //--------------------------------------------------------------- /* * SQLで「OleDbConnection」を生成 * 「ExecuteReader」で実行(レコード取得) * ExecuteScalarメソッドで参照 * ・ExecuteScalarメソッド * 結果を1件だけ返す。レコード数、集計地など単一結果を返す時に使用 */ string sSQL = ""; sSQL += "SELECT COUNT(*) FROM TB_社員"; // Connection + SQL OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(sSQL, cn); // ExecuteScalar int num = (int)cmd.ExecuteScalar(); cn.Close(); System.Windows.Forms.MessageBox.Show(num + "件のレコードがあります。", "通知"); }
フィールド数 (cmd.ExecuteReader.FieldCount)
OleDbConnection cn = new OleDbConnection(cnstr.ToString()); cn.Open(); string sSQL = ""; sSQL += "SELECT * FROM テーブル"; // Connection + SQL OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(sSQL, cn); // ExecuteReader OleDbDataReader dr = cmd.ExecuteReader(); System.Windows.Forms.MessageBox.Show(dr.FieldCount + "フィールドがあります。", "通知"); dr.Close(); cn.Close();