Visual Basicを使って、いくつかのツールを作って活用しているが、その都度、色々な作法を調べてもすぐ忘れてしまうので、メモとして思いつくままに残しておくことにする。
【Access データベースへのアクセス】
Visual BasicからAccessのデータベースを使うためのお約束。
moduleとして、別ファイルにしておいた方が、何かと便利。Accessの場合はプロバイダとして、”Microsoft.ACE.OLEDB.12.0″ と宣言することになる。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 |
Module Module1 '設定 Private DB_provider As String = "Microsoft.ACE.OLEDB.12.0" 'provider For Access database Private DB_folder_name As String = "C:\CaserStatusMonitor\Data" 'folder for database file Private DB_file_name As String = "RcvData.accdb" 'database file name Private DB_connection_text As String = "Provider=" & DB_provider & "; Data Source=" & DB_folder_name & "\" & DB_file_name Private DB_connection As New OleDb.OleDbConnection(DB_connection_text) Private DB_command As OleDb.OleDbCommand = DB_connection.CreateCommand Sub DBselect() DB_connection.Open() DB_command.CommandText = "SELECT * FROM Data2" Try Dim reader As OleDb.OleDbDataReader = DB_command.ExecuteReader() If reader.HasRows = True Then While reader.Read() Debug.WriteLine(reader(0).ToString) End While Else Console.WriteLine("No rows found.") End If Debug.WriteLine("Select Done.") reader.Close() Catch ex As Exception Debug.WriteLine(DB_command.CommandText) Debug.WriteLine(ex.Message, "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error) Finally DB_connection.Close() End Try End Sub Sub DBimport(ByVal buf As String) 'add set of records, multi lines DB_connection.Open() Dim dd1, dd2 As Object dd1 = Split(buf, vbCrLf) 'split into lines Dim i As Integer Dim last_row As Long = UBound(dd1) - 1 For i = 0 To last_row 'data structure in tmp() '0 , 1 , 2 , 3 , 4 , 5 'date, time, TX/RX, pipe, mode(C/T), data dd2 = Split(dd1(i), ",") Try Dim s As String If dd2(4) = "C" Then s = "INSERT INTO DATA2 " s += "(RxDateTime, TXRX, PIPE, MODE, value1) " s += "VALUES " s += "('" & dd2(0) & " " & dd2(1) & "','" & dd2(2) & "','" & dd2(3) & "','" & dd2(4) & "','" & dd2(5) & "');" DB_command.CommandText = s DB_command.ExecuteNonQuery() End If Catch ex As Exception Debug.WriteLine(DB_command.CommandText) Debug.WriteLine(ex.Message, "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error) Finally End Try Next DB_connection.Close() Debug.WriteLine("Insert Done") End Sub Sub DBinsert(ByVal buf As String) 'add one record DB_connection.Open() Dim dd2 As Object 'data structure in tmp() '0 , 1 , 2 , 3 , 4 , 5 'date, time, TX/RX, pipe, mode(C/T), data dd2 = Split(buf, ",") Try Dim s As String If dd2(4) = "C" Then s = "INSERT INTO DATA2 " s += "(RxDateTime, TXRX, PIPE, MODE, value1) " s += "VALUES " s += "('" & dd2(0) & " " & dd2(1) & "','" & dd2(2) & "','" & dd2(3) & "','" & dd2(4) & "','" & dd2(5) & "');" DB_command.CommandText = s DB_command.ExecuteNonQuery() End If Catch ex As Exception Debug.WriteLine(DB_command.CommandText) Debug.WriteLine(ex.Message, "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error) Finally End Try DB_connection.Close() Debug.WriteLine("Insert Done") End Sub Sub DBread(ByVal table_name As String) 'SQL文 Dim SQL_txt As String = "SELECT * FROM " & table_name 'データアダプターを生成 Dim adapter As New OleDb.OleDbDataAdapter(SQL_txt, DB_connection_text) 'データの読み込み Dim Table1 As New DataTable() adapter.Fill(Table1) 'データソースを設定してDataGridViewにデータを表示 Dim BindingSource1 As New BindingSource BindingSource1.DataSource = Table1 Form2.DataGridView1.DataSource = BindingSource1 'Fomr2を表示 Form2.Text = table_name 'タイトルをテーブル名に Form2.Show() End Sub Sub DBinsertTable() Dim SQL_txt As String = "SELECT * FROM Data2" Dim adapter As New OleDb.OleDbDataAdapter(SQL_txt, DB_connection_text) Dim Table1 As New DataTable() adapter.Fill(Table1) ' Dim table As New DataTable("Orders") ' table.Columns.Add("OrderID", GetType(Int32)) ' table.Columns.Add("OrderQuantity", GetType(Int32)) ' table.Columns.Add("CompanyName", GetType(String)) ' table.Columns.Add("Date", GetType(DateTime)) Dim newRow As DataRow = Table1.NewRow() newRow("RxDateTime") = "2016/9/14 00:12:13" newRow("Value1") = "100" ' Add the row to the rows collection. Table1.Rows.Add(newRow) ' Presuming the DataTable has a column named Date. Dim expression As String = "RxDate = '2016/9/14' or Value1 = 100" ' Sort descending by column named CompanyName. Dim sortOrder As String = "RxTime" Dim foundRows As DataRow() ' Use the Select method to find all rows matching the filter. foundRows = Table1.[Select](expression, sortOrder) ' Print column 0 of each returned row. For i As Integer = 0 To foundRows.Length - 1 Console.WriteLine(foundRows(i)(2)) Next 'データソースを設定してDataGridViewにデータを表示 Dim BindingSource1 As New BindingSource BindingSource1.DataSource = Table1.Select(expression, sortOrder) Form2.DataGridView1.DataSource = BindingSource1 'Fomr2を表示 ' Form2.Text = table_name 'タイトルをテーブル名に Form2.Show() End Sub End Module |
