次の操作で解消した：
※作業前のデータベースのバックアップをお忘れなく
phpMyAdmin
操作
照合順序
utf8mb4_general_ci
[チェック] すべてのテーブルの照合順序を変更
[チェック] すべてのテーブルのカラム照合順序を変更する
実行
Windows 10 から MySQL Server on Ubuntu に接続する手順のメモ / Connect to MySQL Server on Ubuntu from Windows 10
参考サイトを手がかりに進めます。
MySQL Server: 192.168.11.10:3306
Network: 192.168.11.*/24
|
1 2 |
$ uname -a Linux SERVER_NAME 4.4.0-140-generic #166-Ubuntu SMP Wed Nov 14 20:09:47 UTC 2018 x86_64 x86_64 x86_64 GNU/Linux |
tcp wrapper を使用しているか確認
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
$ ldd /usr/sbin/mysqld linux-vdso.so.1 => (0x00007fff0b57c000) libaio.so.1 => /lib/x86_64-linux-gnu/libaio.so.1 (0x00007f4ca141b000) liblz4.so.1 => /usr/lib/x86_64-linux-gnu/liblz4.so.1 (0x00007f4ca1203000) libnuma.so.1 => /usr/lib/x86_64-linux-gnu/libnuma.so.1 (0x00007f4ca0ff8000) libwrap.so.0 => /lib/x86_64-linux-gnu/libwrap.so.0 (0x00007f4ca0dee000) libcrypt.so.1 => /lib/x86_64-linux-gnu/libcrypt.so.1 (0x00007f4ca0bb6000) libdl.so.2 => /lib/x86_64-linux-gnu/libdl.so.2 (0x00007f4ca09b2000) libz.so.1 => /lib/x86_64-linux-gnu/libz.so.1 (0x00007f4ca0798000) librt.so.1 => /lib/x86_64-linux-gnu/librt.so.1 (0x00007f4ca0590000) libpthread.so.0 => /lib/x86_64-linux-gnu/libpthread.so.0 (0x00007f4ca0373000) libstdc++.so.6 => /usr/lib/x86_64-linux-gnu/libstdc++.so.6 (0x00007f4c9fff1000) libm.so.6 => /lib/x86_64-linux-gnu/libm.so.6 (0x00007f4c9fce8000) libgcc_s.so.1 => /lib/x86_64-linux-gnu/libgcc_s.so.1 (0x00007f4c9fad2000) libc.so.6 => /lib/x86_64-linux-gnu/libc.so.6 (0x00007f4c9f708000) /lib64/ld-linux-x86-64.so.2 (0x00007f4ca161d000) libnsl.so.1 => /lib/x86_64-linux-gnu/libnsl.so.1 (0x00007f4c9f4ef000) |
接続制限をかける
|
1 |
$ sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf |
|
1 2 3 |
[mysqld] # bind-address = 127.0.0.1 bind-address = 0.0.0.0 |
|
1 |
$ sudo vi /etc/hosts.allow |
|
1 |
mysqld: 192.168.11. |
|
1 |
$ sudo vi /etc/hosts.deny |
|
1 |
ALL: ALL |
|
1 |
$ sudo /etc/init.d/mysql restart |
クライアント PC のブラウザで http://192.168.11.10:3306/ を開いてみて接続できるか確認
管理者ユーザーで mysql を開いてデーターベースへのアクセス権を変更する
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
$ mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 23 Server version: 5.7.24-0ubuntu0.16.04.1 (Ubuntu) Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> use database_name; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> GRANT ALL PRIVILEGES ON database_name.* TO database_username@"%" IDENTIFIED BY 'database_password' WITH GRANT OPTION; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> \q; |
ODBC ドライバーのダウンロード
下記からダウンロード
https://dev.mysql.com/downloads/file/?id=480026
- mysql-connector-odbc-5.3.11-win32.msi
- mysql-connector-odbc-5.3.11-winx64.msi
32/64bit 両方共インストールする
スタートボタン
> Windows 管理ツール
> ODBC Data Sources (32-bit) ※今回はこちらを実行した Excel が 32bit なので
> ODBC データソース (64 ビット)
ユーザー DSN
追加
MySQL ODBC 5.3 Unicode Driver
完了
Connection Parameters
Data Source Name: hoge
Description: hoge description
TCP/IP Server: 192.168.11.10
Port: 3306
User: データーベースユーザー名
Password: データーベースパスワード
Database: データーベース名
ボタン Test を押して接続確認
下記のユーザーデータソースが登録される
hoge 32/64 ビット MySQL ODBC 5.3 Unicode Driver
Excel 2007 から登録したユーザーデータソースに接続してみる
データ タブ
その他のデータソース
データ接続ウィザード
ODBC DSN
ボタン 次へ
先ほど登録したものを選ぶと
テーブルの一覧が表示される
VBA から接続テスト
|
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 |
Sub test() Dim adoCon As Object ' ADOコネクション Dim adoRs As Object ' ADOレコードセット Dim SQL As String ' SQL Dim i As Long ' ADOコネクションを作成 Set adoCon = CreateObject("ADODB.Connection") On Error GoTo ErrorTrap ' ODBC接続 adoCon.Open _ "DRIVER={MySQL ODBC 5.3 Unicode Driver};" & _ " SERVER=192.168.11.10;" & _ " DATABASE=データーベース名;" & _ " UID=データーベースユーザー名;" & _ " PWD=データーベースパスワード;" MsgBox "DB接続成功" ' SQL文 SQL = "SELECT * FROM table" ' SQLの実行 Set adoRs = adoCon.Execute(SQL) ' レコードセット内の全ての行の読込が ' 終了するまで処理を繰り返す i = 2 Do Until adoRs.EOF Cells(i, 1) = adoRs!id Cells(i, 2) = adoRs!name i = i + 1 ' 次の行に移動する adoRs.MoveNext Loop ' 解放処理 adoRs.Close adoCon.Close Set adoRs = Nothing Set adoCon = Nothing Exit Sub ErrorTrap: Set adoRs = Nothing Set adoCon = Nothing MsgBox (Err.Description) End Sub |
ODBC ドライバーのインストールに失敗する場合
‘This appliction required visual studio 2013 …’ と表示されてインストールできない場合
Visual Studio 2013 の Visual C++ 再頒布可能パッケージ を下記からダウンロード＆インストールする
https://www.microsoft.com/ja-jp/download/confirmation.aspx?id=40784
Excel 2007 で開発タブの表示
左上の丸いボタン
開いたメニューの一番下の「Excel のオプション」
基本設定
[開発] タブをリボンに表示する
にチェック
まさに メニュー ウィザードリィ
参考:
https://www.724685.com/weekly/qa111027.htm
http://dotnsf.blog.jp/archives/1013344582.html
https://excelwork.info/excel/databasemysql/
http://d.hatena.ne.jp/pyopyopyo/20100327/p1
追加 vba の例 :
|
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 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 |
' ' See: ' http://www.multidriver.jp/Cgi-bin/ASP/ASPPage.asp?P1=84 ' http://fnya.cocolog-nifty.com/blog/2015/04/access-postgr-1.html ' https://excelwork.info/excel/adobofeof/ ' http://fnya.cocolog-nifty.com/blog/2015/04/access-postgr-1.html ' http://yasusigi.net/iasdocs/aspdocs/ref/comp/daprop02_7.htm ' http://yasusigi.net/iasdocs/aspdocs/ref/comp/daprop03_3.htm ' https://www.moug.net/tech/acvba/0070053.html ' http://accessvba.pc-users.net/ado/open_record.html ' ' Date: 2019-03-14 ' ' マクロを含むエクセルファイルは ".xlsm" ファイルに保存すること ' ' 開発タブの表示方法 ' Excel 2007 ' 左上の丸いボタンを押す ' 開いたメニューの一番下の [Excel のオプション] ' 基本設定 ' [開発] タブをリボンに表示するにチェック ' Option Explicit ' LockType Const adLockReadOnly As Integer = 0 ' 既定値で読み取り専用です。データの更新・追加・削除はできません。 Const adLockPessimistic As Integer = 1 ' レコード単位で排他的ロックを行います。編集直後にレコードをロックします。 Const adLockOptimistic As Integer = 2 ' レコード単位で共有的ロックを行います。Updateメソッドを呼び出した場合にのみ、共有的ロックします。 Const adLockBatchOptimistic As Integer = 3 ' 複数のレコードをバッチ更新します。 ' CursorLocation Const adUseNone As Integer = 1 ' カーソルサービスを使いません。 Const adUseServer As Integer = 2 ' サーバー側カーソル（既定値） Const adUseClient As Integer = 3 ' クライアント側カーソル ' CursorType Const adOpenForwardOnly As Integer = 0 ' 前方スクロール カーソル。レコードの前方スクロールだけが可能であること以外は静的カーソルとまったく同じです。レコードセットを 1 回の操作で渡せばよい場合にこの前方スクロール カーソルを使うと、パフォーマンスが向上します (デフォルト)。 Const adOpenKeyset As Integer = 1 ' キーセット カーソル。動的カーソルに似ていますが、ほかのユーザーによるレコードの追加操作を見ることはできません。また、ほかのユーザーが自分のレコードセットからレコードを削除しているときはそのレコードにアクセスすることができません。ほかのユーザーによるデータの変更操作は見ることができます。 Const adOpenDynamic As Integer = 2 ' 動的カーソル。ほかのユーザーによる追加、変更、および削除の操作を見ることができます。ブックマークを利用する以外のすべての操作が Recordset を介して可能ですが、プロバイダがブックマークをサポートしていればそれを利用できます。 Const adOpenStatic As Integer = 3 ' 静的カーソル。データの検索やレポートの生成のために使用する、レコード集合の静的なコピーです。ほかのユーザーによるデータの追加、変更、または削除の操作を見ることはできません。 ' Command Const adCmdText As Integer = 1 ' SQL 文 Const adCmdTable As Integer = 2 ' テーブル名 Const adCmdStoredProc As Integer = 4 ' ストアド プロシージャ名 Const adCmdUnknown As Integer = 8 ' 不明（既定値） Const adCmdFile As Integer = 256 ' 永続的に保存された Recordset のファイル名 Const adCmdTableDirect As Integer = 512 ' 列がすべて返されるテーブル名 Const adCmdUnspecified As Integer = -1 ' コマンドタイプ引数を指定しません ' Parameter Const adParamUnKnown As Integer = 0 ' パラメータ方向不明 Const adParamInput As Integer = 1 ' 入力パラメータ（デフォルト） Const adParamOutput As Integer = 2 ' 出力パラメータ Const adParamInputOutput As Integer = 3 ' 入出力パラメータ Const adParamReturnValue As Integer = 4 ' 戻り値 ' DataType Const adEmpty As Integer = 0 ' 型の指定なし Const adBoolean As Integer = 11 ' ブール型 Const adCurrency As Integer = 6 ' 通貨型 Const adDate As Integer = 7 ' 日付型 Const adDBDate As Integer = 133 ' yyyymmdd形式の日付型 Const adDBTime As Integer = 134 ' hhmmss形式の時刻型 Const adDBTimeStamp As Integer = 135 ' yyyymmdd hhmmss形式の時刻型 Const adDecimal As Integer = 14 ' バリアント10進型 Const adDouble As Integer = 5 ' 倍精度不動小数点型 Const adError As Integer = 10 ' 32ビットエラーコード Const adGUID As Integer = 72 ' 固有のグローバルID Const adIDispatch As Integer = 9 ' OLEオブジェクトIdispatchインターフェースのポインタ Const adIUnknown As Integer = 13 ' OLEオブジェクトのIUnknownインターフェースのポインタ Const adNumeric As Integer = 131 ' 数値型 Const adSmallInt As Integer = 2 ' 2バイトの符号付整数 Const adInteger As Integer = 3 ' 4バイトの符号付整数 Const adSingle As Integer = 4 ' 単精度浮動小数点型 Const adTinyInt As Integer = 16 ' 1バイトの符号付整数 Const adUnsignedTinyInt As Integer = 17 ' 1バイトの符号無し整数 Const adUnsignedSmallInt As Integer = 18 ' 2バイトの符号無し整数 Const adUnsignedInt As Integer = 19 ' 4バイトの符号無し整数 Const adBigInt As Integer = 20 ' 8バイトの符号付整数 Const adUnsignedBigInt As Integer = 21 ' 8バイトの符号無し整数 Const adUserDefined As Integer = 132 ' ユーザー定義の変数 Const adVarBinary As Integer = 204 ' バイナリ型（パラメータオブジェクトのみ） Const adLongVarBinary As Integer = 205 ' 長バイナリ型（パラメータオブジェクトのみ） Const adVariant As Integer = 12 ' バリアント型 Const adBinary As Integer = 128 ' バイナリ型 Const adBSTR As Integer = 8 ' Nullで終了するUnicode文字列 Const adChar As Integer = 129 ' 文字列型 Const adWchar As Integer = 130 ' Nullで終了するUnicode文字列型1 Const adVarChar As Integer = 200 ' 文字列型（パラメータオブジェクトのみ） Const adLongVarChar As Integer = 201 ' 長文字列型（パラメータオブジェクトのみ） Const adVarWChar As Integer = 202 ' Nullで終了するUnicode文字列型（パラメータオブジェクトのみ） Const adLongVarWChar As Integer = 203 ' Nullで終了するUnicode文字列型（パラメータオブジェクトのみ） Dim adoCon As Object ' ADOコネクション Function DBConnect() Const ODBCDriver As String = "{MySQL ODBC 5.3 Unicode Driver}" Const ServerAddress As String = "192.168.11.10" Const DatabaseName As String = "DBNAME" Const DatabaseUID As String = "DBUSERNAME" Const DatabasePWD As String = "DBPASSWORD" On Error GoTo DatabaseConnect If adoCon.State = 1 Then Debug.Print "DB接続済み" Exit Function End If DatabaseConnect: On Error GoTo ErrorTrap ' ADOコネクションを作成 Set adoCon = CreateObject("ADODB.Connection") ' ODBC接続 adoCon.Open _ "DRIVER=" & ODBCDriver & ";" & _ "SERVER=" & ServerAddress & ";" & _ "DATABASE=" & DatabaseName & ";" & _ "UID=" & DatabaseUID & ";" & _ "PWD=" & DatabasePWD & ";" Debug.Print "DB接続成功" Exit Function ErrorTrap: Set adoCon = Nothing ErrMsgBox End Function Function DBDisConnect() On Error Resume Next adoCon.Close Set adoCon = Nothing End Function Function ErrMsgBox() Dim msg As String If Err.Number = 0 Then Exit Function msg = "エラー番号:" & Err.Number & vbCrLf & _ "エラー内容：" & Err.Description & vbCrLf & _ "ヘルプファイル名" & Err.HelpContext & vbCrLf & _ "プロジェクト名：" & Err.Source Debug.Print msg MsgBox msg End Function Sub test2() Dim rs As Object ' ADOレコードセット Dim param As Object ' ADOレコードセット ' Dim rs As ADODB.Recordset Dim SQL As String ' SQL Dim i As Integer DBConnect Dim cmd As Object Set cmd = CreateObject("ADODB.Command") ' SQL文 SQL = "SELECT * FROM wp_posts WHERE ID = ?;" ' SQL を宣言 ' SQL = "INSERT INTO users (user_id, user_name) values (?,?);" 'Commandをインスタンス化 ' set cmd = New ADODB.Command ' Set cmd.ActiveConnection = con cmd.ActiveConnection = adoCon cmd.CommandType = adCmdText cmd.CommandText = SQL cmd.Prepared = True 'パラメータを作成、１つずつ ' Set param = cmd.CreateParameter("ID", adChar, adParamInput, 5) ' Set param = cmd.CreateParameter("ID", adInteger, adParamInput) cmd.Parameters.Append cmd.CreateParameter("ID", adInteger, adParamInput) cmd.Parameters("ID").Value = 2 ' cmd.Parameters.Append cmd.CreateParameter("ID", adInteger, adParamInput, 2) ' cmd.Parameters("ID").Value = 2 ' Set param = cmd.CreateParameter("NAME", adWChar, adParamInput, 255) ' cmd.Parameters.Append param 'パラメータに値を設定 ' cmd.Parameters("ID").Value = uId ' cmd.Parameters("NAME").Value = uName ' トランザクション開始 ' adoCon.BeginTrans ' SQL実行 Set rs = cmd.Execute ' コミット ' adoCon.CommitTrans ' rs.MoveFirst ' Cells(1, 1) = rs(0).Value Set rs = CreateObject("ADODB.Recordset") ' New ADODB.Recordset rs.CursorLocation = adUseClient ' rs.CursorType = adOpenStatic rs.Open cmd Debug.Print rs.RecordCount Debug.Print rs.fields.Count ' SQLの実行 ' Set adoRs = adoCon.Execute(SQL) On Error GoTo ErrorTrap rs.MoveFirst ' レコードセット内の全ての行の読込が終了するまで処理を繰り返す i = 2 Do Until rs.EOF ' Cells(i, 1) = adoRs!post_date ' Cells(i, 2) = adoRs!post_title Cells(i, 1) = rs("post_date") Cells(i, 2) = rs("post_title") i = i + 1 ' 次のレコードに移動する rs.MoveNext Loop Debug.Print rs.RecordCount Debug.Print rs.fields.Count ' 解放処理 rs.Close ErrorTrap: Set rs = Nothing ErrMsgBox End Sub Sub test3() Dim rs As Object ' ADOレコードセット Dim param As Object ' Dim cmd As Object ' ADODB.Command Dim SQL As String ' SQL Dim i As Integer DBConnect On Error GoTo ErrorTrap Set cmd = CreateObject("ADODB.Command") SQL = "SELECT * FROM wp_posts WHERE ID = ?" cmd.ActiveConnection = adoCon cmd.CommandType = adCmdText cmd.CommandText = SQL cmd.Prepared = True cmd.Parameters.Append cmd.CreateParameter("ID", adInteger, adParamInput) cmd.Parameters("ID").Value = 3 ' SQL実行 cmd.Execute Set rs = CreateObject("ADODB.Recordset") rs.CursorLocation = adUseClient ' rs.ActiveConnection = adoCon rs.Open cmd Debug.Print rs.RecordCount Debug.Print rs.fields.Count ' rs.MoveFirst ' レコードセット内の全ての行の読込が終了するまで処理を繰り返す i = 2 Do Until rs.EOF Cells(i, 1) = rs("post_date") Cells(i, 2) = rs("post_title") i = i + 1 ' 次のレコードに移動する rs.MoveNext Loop ' 解放処理 rs.Close ErrorTrap: Set rs = Nothing ErrMsgBox End Sub ' ' Usage: ' =PostTitle(2) ' Function PostTitle(id As Integer, Optional notFoundValue = "") Dim rs As Object ' ADOレコードセット Dim cmd As Object ' ADODB.Command Dim param As Object ' ADODB.Command.Parameter Dim SQL As String ' SQL DBConnect On Error GoTo ErrorTrap SQL = "SELECT * FROM wp_posts WHERE ID = ?" Set cmd = CreateObject("ADODB.Command") cmd.ActiveConnection = adoCon cmd.CommandType = adCmdText cmd.CommandText = SQL cmd.Prepared = True cmd.Parameters.Append cmd.CreateParameter("ID", adInteger, adParamInput) cmd.Parameters("ID").Value = id ' SQL実行 cmd.Execute Set rs = CreateObject("ADODB.Recordset") rs.CursorLocation = adUseClient rs.Open cmd PostTitle = notFoundValue If rs.RecordCount = 0 Then Debug.Print "PostTitle: 見つかりません: ID: " & id Else PostTitle = rs("post_title") End If ' 解放処理 rs.Close Set rs = Nothing Set cmd = Nothing Exit Function ErrorTrap: Set rs = Nothing Set cmd = Nothing ErrMsgBox PostTitle = Err.Description ' エラー内容を返す End Function ' ' Usage: ' =PostIdFromTitle("title", True/False, "Not found!") ' Function PostIdFromTitle(post_title As String, Optional Matching As Boolean = False, Optional notFoundValue = -1) Dim rs As Object ' ADOレコードセット Dim cmd As Object ' ADODB.Command Dim param As Object ' ADODB.Command.Parameter Dim SQL As String ' SQL DBConnect On Error GoTo ErrorTrap If Matching Then SQL = "SELECT * FROM wp_posts WHERE post_title = ?" Else SQL = "SELECT * FROM wp_posts WHERE post_title like ?" post_title = "%" & post_title & "%" End If Set cmd = CreateObject("ADODB.Command") cmd.ActiveConnection = adoCon cmd.CommandType = adCmdText cmd.CommandText = SQL cmd.Prepared = True ' cmd.Parameters.Append cmd.CreateParameter("post_title", adVarChar, adParamInput, 255) ' cmd.Parameters.Append cmd.CreateParameter("post_title", adVarWChar, adParamInput, 255) cmd.Parameters.Append cmd.CreateParameter("post_title", adWchar, adParamInput, 255) cmd.Parameters("post_title").Value = post_title ' SQL実行 cmd.Execute Set rs = CreateObject("ADODB.Recordset") rs.CursorLocation = adUseClient rs.Open cmd PostIdFromTitle = notFoundValue If rs.RecordCount = 0 Then Debug.Print "PostIdFromTitle: 見つかりません: post_title: " & post_title Else PostIdFromTitle = rs("ID") End If ' 解放処理 rs.Close Set rs = Nothing Set cmd = Nothing Exit Function ErrorTrap: Set rs = Nothing Set cmd = Nothing ErrMsgBox PostIdFromTitle = Err.Description ' エラー内容を返す End Function |