ExcelVBA データベース連携

自分用のメモなので、形は整ってないです。

MySQLと接続する

Sub aaa()

Dim adoCon As Object

Set adoCon = CreateObject("ADODB.Connection")

'rootユーザーで接続
adoCon.Open "Driver={MySQL ODBC 8.0 Unicode Driver};" & _
           "SERVER=localhost;" & _
           "DATABASE=practice;" & _
           "Port=3306;" & _
           "USER=root;" & _
           "password=brownAzarashi21;"
           'Driverの数値は、MySQLのバージョンに合わせて
adoCon.Close


'user21ユーザーで接続
adoCon.Open "Driver={MySQL ODBC 8.0 Unicode Driver};" & _
           "SERVER=127.1.1.1;" & _
           "DATABASE=practice;" & _
           "Port=3306;" & _
           "USER=user21;" & _
           "password=blueAzarashi21;"
           'SERVERはIPアドレス指定をするのが本来
adoCon.Close

Set adoCon = Nothing

'注意すべきなのは、MySQLのODBCドライバのBIT数
'OSのBIT数ではなく、ExcelのBIT数に合わせたドライバを用意して、指定しないとエラーになる

End Sub

CSVファイルをデータベースとして読み込む

Sub ccc()

Dim adoCon As Object
Dim adoRs As Object
Dim sqlStr As String

Set adoCon = CreateObject("ADODB.Connection")
Set adoRs = CreateObject("ADODB.Recordset")

With adoCon
  .Provider = "Microsoft.ACE.OLEDB.12.0"
  .Properties("Extended Properties") = "Text;HDR=NO"
  .Open "C:\work\"
End With

sqlStr = "SELECT * FROM aaa.csv"
'aaa.csvは5行3列、カンマ区切り、データ囲み文字無しのCSV
'CSVファイルを開いているとエラーになる

Set adoRs = adoCon.Execute(sqlStr)

Debug.Print adoRs.Fields.Count         '3
Debug.Print adoRs.Fields.Item(0).Name  'F1　　F1から自動で振られるらしい
Debug.Print adoRs.Fields.Item(1).Name  'F2

Cells(1, 1).CopyFromRecordset adoRs


sqlStr = "SELECT * FROM bbb.csv"
'bbb.csvは5行5列、カンマ区切り、データ囲み文字は「"」のCSV
'2バイト文字は、文字コードがShift-JISでないと文字化けするようだ

Set adoRs = adoCon.Execute(sqlStr)
Cells(10, 1).CopyFromRecordset adoRs

adoRs.Close
adoCon.Close

Set adoCon = Nothing
Set adoRs = Nothing

End Sub

テーブルの全データを取得　　データを配列に格納することも可能

Sub AAA()  'table_all_datatypeテーブルから、全データを取得  ※MySQL接続

  Dim adoCon As Object
  Dim adoRs As Object
  Dim sqlStr As String
  Dim var1 As Variant
  Dim i As Long
  
  Set adoCon = CreateObject("ADODB.Connection")
  Set adoRs = CreateObject("ADODB.Recordset")
  
  adoCon.Open "Driver={MySQL ODBC 8.0 Unicode Driver};" & _
             "SERVER=localhost;" & _
             "DATABASE=excelvba_sample;" & _
             "Port=3306;" & _
             "USER=user21;" & _
             "password=blueAzarashi21;"
  
  'sqlStr = "SELECT * FROM table_all_datatype"
  sqlStr = "SELECT * FROM table_all_datatype WHERE code='cccccode0001'"
  
  adoRs.Open sqlStr, adoCon  'レコードセットを開く
  
  Debug.Print adoRs.BOF
  '現在のレコード位置が、 最初のレコードの前にあるか。データが全く無かった場合は、データ取得時にTrue
  
  Debug.Print adoRs.EOF
  '現在のレコード位置が、 最後のレコードの後にあるか。データが全く無かった場合は、データ取得時にTrue
  
  'Cells(1, 1).CopyFromRecordset adoRs
  '全レコードをセルに出力、なのだが、レコードセットの内容が変になる？　使わないほうがいいかも
  
  Debug.Print adoRs.Fields.Count         'フィールド数
  
  '全フィールド名を取得。インデックスは0から
  For i = 0 To adoRs.Fields.Count - 1
    Debug.Print adoRs.Fields.Item(i).Name
  Next i
  
  Debug.Print "------------------------"
  
  If adoRs.EOF = False Then  'データが取得できている場合
    var1 = adoRs.GetRows   '取得したデータを配列に格納
    '配列は1次元目のインデックス0が1番目のフィールドのデータ。2次元目のインデックス0が1行目のデータになる
    
    Debug.Print var1(0, 1)  '1番目のフィールドの2行目のデータ
    Debug.Print var1(4, 3)  '5番目のフィールドの4行目のデータ
    
    Debug.Print VarType(var1(0, 0))   '8   このフィールドのデータ型は char(8)
    Debug.Print VarType(var1(1, 0))   '2   このフィールドのデータ型は tinyint(4)
    Debug.Print VarType(var1(2, 0))   '2   このフィールドのデータ型は smallint(6)
    Debug.Print VarType(var1(3, 0))   '3   このフィールドのデータ型は mediumint(9)
    Debug.Print VarType(var1(4, 0))   '3   このフィールドのデータ型は int(11)
    Debug.Print VarType(var1(5, 0))   '14   このフィールドのデータ型は bigint(20)
    Debug.Print VarType(var1(6, 0))   '4   このフィールドのデータ型は float
    Debug.Print VarType(var1(7, 0))   '5   このフィールドのデータ型は double
    Debug.Print VarType(var1(8, 0))   '14   このフィールドのデータ型は decimal(10,3)
    Debug.Print VarType(var1(9, 0))   '7   このフィールドのデータ型は date
    Debug.Print VarType(var1(10, 0))  '7   このフィールドのデータ型は datetime
    Debug.Print VarType(var1(11, 0))  '7   このフィールドのデータ型は timestamp
    Debug.Print VarType(var1(12, 0))  '7   このフィールドのデータ型は time
    Debug.Print VarType(var1(13, 0))  '8   このフィールドのデータ型は varchar(100)
    Debug.Print VarType(var1(14, 0))  '8   このフィールドのデータ型は text
    Debug.Print VarType(var1(15, 0))  '8   このフィールドのデータ型は meduimtext
    Debug.Print VarType(var1(16, 0))  '8   このフィールドのデータ型は longtext
  Else
    Debug.Print "データ無し"
  End If
  
  Debug.Print "------------------------"
  
  If adoRs.EOF = False Then  'データが取得できている場合
    With adoRs
      .MoveFirst
      '先頭のレコードへ移動。一度全レコードの取得しているので、これが必要になる
      Do Until .EOF
        Debug.Print !code        'フィールド名を指定してデータを取得
        Debug.Print !tynyint1
        Debug.Print !smallint1
        Debug.Print !mediumint1
        Debug.Print !intnum1
        Debug.Print !bigint1
        Debug.Print !float1
        Debug.Print !double1
        Debug.Print !decimal1
        Debug.Print !date1
        Debug.Print !datetime1
        Debug.Print !timestamp1
        Debug.Print !time1
        Debug.Print !varchar1
        Debug.Print !text1
        Debug.Print !mediumtext1
        Debug.Print !longtext1
        Debug.Print "------------------------"
        .MoveNext    '次のレコードへ
      Loop
    End With
  Else
    Debug.Print "データ無し"
  End If
  
  adoRs.Close
  adoCon.Close
  
  Set adoCon = Nothing
  Set adoRs = Nothing

End Sub



Sub BBB()  'table_all_datatypeテーブルから、全データを取得  ※MariaDB接続

  Dim adoCon As Object
  Dim adoRs As Object
  Dim sqlStr As String
  Dim var1 As Variant
  
  Set adoCon = CreateObject("ADODB.Connection")
  Set adoRs = CreateObject("ADODB.Recordset")
  
  adoCon.Open "Driver={MySQL ODBC 8.0 Unicode Driver};" & _
             "SERVER=localhost;" & _
             "DATABASE=excelvba_sample;" & _
             "Port=3307;" & _
             "USER=user21;" & _
             "password=blueAzarashi21;"
  
  sqlStr = "SELECT * FROM staff_master"
  
  adoRs.Open sqlStr, adoCon
  
  If adoRs.EOF = False Then
    With adoRs
      .MoveFirst
      Do Until .EOF
        Debug.Print !staff_code
        Debug.Print !Name    'フィールド名は "name" なのだが、Nameになってしまう。エディタの問題だろう
        Debug.Print !age
        Debug.Print !department
        Debug.Print VarType(!staff_code)    '8
        Debug.Print VarType(!Name)          '8
        Debug.Print VarType(!age)           '3
        Debug.Print VarType(!department)    '8
        Debug.Print "------------------------"
        .MoveNext
      Loop
    End With
  Else
    Debug.Print "データ無し"
  End If
  
  adoRs.Close
  adoCon.Close
  
  Set adoCon = Nothing
  Set adoRs = Nothing

End Sub




*****************  以下、テーブル作成とデータ挿入  ******************

drop table if exists table_all_datatype;

CREATE TABLE `table_all_datatype` (
`code` char(8),
`tynyint1` TINYINT,
`smallint1` SMALLINT,
`mediumint1` MEDIUMINT,
`intnum1`int,
`bigint1` bigint,
`float1` float,
`double1` double,
`decimal1` decimal(10,3),
`date1` date,
`datetime1` datetime,
`timestamp1` timestamp,
`time1` time,
`varchar1` varchar(100),
`text1` text,
`mediumtext1` mediumtext,
`longtext1` longtext,
PRIMARY KEY (`code`)
)character set utf8mb4 collate utf8mb4_general_ci;


INSERT INTO table_all_datatype (code,tynyint1,smallint1,mediumint1,intnum1,bigint1,float1,double1,decimal1,date1,datetime1,timestamp1,time1,varchar1,text1,mediumtext1,longtext1) VALUES
('code0001',1,10,100,1000,10000,1.1,-12.12,111.111,'2012-1-1','2000-3-1 01:01:01','1995-5-1 10:10:11','14:15:21','あああ','短いテキスト1','中くらいのテキスト1','長いテキスト1'),
('code0002',2,20,200,2000,20000,1.2,-12.13,222.222,'2012-1-2','2000-3-1 01:01:02','1995-5-1 10:10:12','14:15:22','漢字','短いテキスト2','中くらいのテキスト2','長いテキスト2'),
('code0003',3,30,300,3000,30000,1.3,-12.14,333.333,'2012-1-3','2000-3-1 01:01:03','1995-5-1 10:10:13','14:15:23','日本語','短いテキスト3','中くらいのテキスト3','長いテキスト3'),
('code0004',4,40,400,4000,40000,1.4,-12.15,444.444,'2012-1-4','2000-3-1 01:01:04','1995-5-1 10:10:14','14:15:24','熊嵐','短いテキスト4','中くらいのテキスト4','長いテキスト4'),
('code0005',5,50,500,5000,50000,1.5,-12.16,555.555,'2012-1-5','2000-3-1 01:01:05','1995-5-1 10:10:15','14:15:25','指定桁数','短いテキスト5','中くらいのテキスト5','長いテキスト5'),
('code0006',6,60,600,6000,50001,1.6,-12.17,666.666,'2012-1-6','2000-3-1 01:01:06','1995-5-1 10:10:16','14:15:26','種類','短いテキスト6','中くらいのテキスト6','長いテキスト6'),
('code0007',7,70,700,7000,70000,1.7,-12.18,777.777,'2012-1-7','2000-3-1 01:01:07','1995-5-1 10:10:17','14:15:27','カキク','短いテキスト7','中くらいのテキスト7','長いテキスト7'),
('code0008',8,80,800,8000,80000,1.8,-12.19,888.888,'2012-1-8','2000-3-1 01:01:08','1995-5-1 10:10:18','14:15:28','サシス','短いテキスト8','中くらいのテキスト8','長いテキスト8'),
('code0009',9,90,900,9000,90000,1.9,-12.20,999.999,'2012-1-9','2000-3-1 01:01:09','1995-5-1 10:10:19','14:15:29','ａｂｃ','短いテキスト9','中くらいのテキスト9','長いテキスト9'),
('code0010',10,100,1000,10000,100000,1.10,-12.21,11111.101,'2012-1-10','2000-3-1 01:01:10','1995-5-1 10:10:20','14:15:30','AADDEE','短いテキスト10','中くらいのテキスト10','長いテキスト10');



drop table if exists staff_master;

CREATE TABLE `staff_master` (
`staff_code` char(10),
`name` varchar(20) not null,
`age` int(10) not null,
`department` varchar(30) not null,
PRIMARY KEY (`staff_code`)
)character set utf8mb4 collate utf8mb4_general_ci;


INSERT INTO staff_master (staff_code,name,age,department) VALUES
('staff00001','岩崎拓郎',25,'営業'),
('staff00002','安藤真人',48,'開発'),
('staff00003','小幡卓司',17,'秘書'),
('staff00004','伊藤貴昭',61,'人事'),
('staff00005','永井達明',23,'総務'),
('staff00006','川嶋創',19,'経理'),
('staff00007','古川勝',33,'営業'),
('staff00008','栗原明',29,'経理'),
('staff00009','長谷川守',51,'開発'),
('staff00010','羽賀昭一',49,'開発');

データの挿入・更新・削除

Sub CCC()  'データの更新、追加、削除  ※MySQL接続

Dim adoCon As Object
Dim adoRs As Object
Dim sqlStr As String
Dim id As Long
Dim staff_code As String

Set adoCon = CreateObject("ADODB.Connection")
Set adoRs = CreateObject("ADODB.Recordset")

  adoCon.Open "Driver={MySQL ODBC 8.0 Unicode Driver};" & _
             "SERVER=localhost;" & _
             "DATABASE=excelvba_sample;" & _
             "Port=3306;" & _
             "USER=user21;" & _
             "password=blueAzarashi21;"
             
sqlStr = "INSERT INTO shop_sales VALUES(31,'2012-12-5','shop00002',45000,'staff00006')"
Set adoRs = adoCon.Execute(sqlStr)  '新規データを挿入。この時点ですでにコミットされているようだ

sqlStr = "UPDATE shop_sales SET sales=55555 WHERE `id`=5"
Set adoRs = adoCon.Execute(sqlStr)  'データを更新

sqlStr = "DELETE FROM shop_sales WHERE staff_code='staff00003'"
Set adoRs = adoCon.Execute(sqlStr)  'データを削除


id = 8
sqlStr = "UPDATE shop_sales SET sales=9988 WHERE id=" & id
Set adoRs = adoCon.Execute(sqlStr)  '変数を使ってSQL編集し、データを更新

staff_code = "staff00007"
sqlStr = "UPDATE shop_sales SET sales=77777 WHERE staff_code='" & staff_code & "'"
'文字列の変数をSQLに組み込む場合は、前後にシングルクォーテーションが必要らしい
Set adoRs = adoCon.Execute(sqlStr)

adoCon.Close

Set adoCon = Nothing
Set adoRs = Nothing

End Sub





***************************  以下、テーブル作成とデータ挿入   ***********************************

drop table if exists shop_sales;

CREATE TABLE `shop_sales` (
`id` int(10),
`sales_date` date not null, 
`shop_code` char(9) not null,
`sales` int(10) not null,
`staff_code` char(10),
PRIMARY KEY (`id`)
)character set utf8mb4 collate utf8mb4_general_ci;


INSERT INTO shop_sales (id,sales_date,shop_code,sales,staff_code) VALUES
(1,'2011-1-1','shop00001',11000,'staff00002'),
(2,'2011-1-1','shop00002',12000,'staff00004'),
(3,'2011-1-1','shop00003',13000,'staff00006'),
(4,'2011-1-1','shop00004',14000,'staff00008'),
(5,'2011-1-1','shop00005',15000,'staff00009'),
(6,'2011-1-1','shop00006',16000,'staff00001'),
(7,'2011-1-1','shop00007',17000,'staff00001'),
(8,'2011-1-1','shop00008',18000,'staff00002'),
(9,'2011-1-1','shop00009',19000,'staff00005'),
(10,'2011-1-1','shop00010',20000,'staff00003'),
(11,'2011-1-2','shop00001',21000,'staff00006'),
(12,'2011-1-2','shop00002',22000,'staff00004'),
(13,'2011-1-2','shop00003',23000,'staff00010'),
(14,'2011-1-2','shop00004',24000,'staff00003'),
(15,'2011-1-2','shop00005',25000,'staff00007'),
(16,'2011-1-2','shop00006',26000,'staff00008'),
(17,'2011-1-2','shop00007',27000,'staff00009'),
(18,'2011-1-2','shop00008',28000,'staff00006'),
(19,'2011-1-2','shop00009',29000,'staff00001'),
(20,'2011-1-2','shop00010',30000,'staff00002'),
(21,'2011-1-3','shop00001',31000,'staff00008'),
(22,'2011-1-3','shop00002',32000,'staff00009'),
(23,'2011-1-3','shop00003',33000,'staff00007'),
(24,'2011-1-3','shop00004',34000,'staff00008'),
(25,'2011-1-3','shop00005',35000,'staff00010'),
(26,'2011-1-3','shop00006',36000,'staff00002'),
(27,'2011-1-3','shop00007',37000,'staff00003'),
(28,'2011-1-3','shop00008',38000,'staff00004'),
(29,'2011-1-3','shop00009',39000,'staff00006'),
(30,'2011-1-3','shop00010',40000,'staff00009');

トランザクションの基本

Sub DDD()  'トランザクション基本。簡易なものなので、これで実装はどうかな

  Dim adoCon As Object
  Dim adoRs As Object
  Dim sqlStr As String
  Dim isBeginTransaction As Boolean  'トランザクションが開始されているか
  Dim isCommitTransaction As Boolean  'トランザクションがコミットされているか
  Dim isOpenConnection As Boolean    'ADODB.ConnectionがOpenされているか
  
  isBeginTransaction = False
  isCommitTransaction = False
  isOpenConnection = False
  
  On Error GoTo ErrorHandler1
  
  Set adoCon = CreateObject("ADODB.Connection")
  Set adoRs = CreateObject("ADODB.Recordset")
  
  adoCon.Open "Driver={MySQL ODBC 8.0 Unicode Driver};" & _
             "SERVER=localhost;" & _
             "DATABASE=excelvba_sample;" & _
             "Port=3306;" & _
             "USER=user21;" & _
             "password=blueAzarashi21;"
  isOpenConnection = True
    

  adoCon.BeginTrans  'トランザクションの開始
  isBeginTransaction = True
               
  sqlStr = "UPDATE shop_sales SET sales_date='2012/5/21' WHERE id=1"

  Set adoRs = adoCon.Execute(sqlStr)

  adoCon.CommitTrans  'コミット
  isCommitTransaction = True

  adoCon.Close
  Set adoCon = Nothing
  Set adoRs = Nothing
               
  Exit Sub
             
             
ErrorHandler1:  'エラーハンドラ

  If isBeginTransaction And isCommitTransaction = False Then
  'トランザクションが開始されていて、かつコミットしていない場合に限り、ロールバック
    adoCon.RollbackTrans
  End If
  
  If isOpenConnection Then  'ConnectionをOpenせずにCloseするとエラーになるので、確認してから
    adoCon.Close
  End If
  
  Set adoCon = Nothing
  Set adoRs = Nothing
End Sub



***************  テーブル作成とデータ挿入   ********************

drop table if exists shop_sales;

CREATE TABLE `shop_sales` (
`id` int(10),
`sales_date` date not null, 
`shop_code` char(9) not null,
`sales` int(10) not null,
`staff_code` char(10),
PRIMARY KEY (`id`)
)character set utf8mb4 collate utf8mb4_general_ci;


INSERT INTO shop_sales (id,sales_date,shop_code,sales,staff_code) VALUES
(1,'2011-1-1','shop00001',11000,'staff00002'),
(2,'2011-1-1','shop00002',12000,'staff00004'),
(3,'2011-1-1','shop00003',13000,'staff00006'),
(4,'2011-1-1','shop00004',14000,'staff00008'),
(5,'2011-1-1','shop00005',15000,'staff00009'),
(6,'2011-1-1','shop00006',16000,'staff00001'),
(7,'2011-1-1','shop00007',17000,'staff00001'),
(8,'2011-1-1','shop00008',18000,'staff00002'),
(9,'2011-1-1','shop00009',19000,'staff00005'),
(10,'2011-1-1','shop00010',20000,'staff00003'),
(11,'2011-1-2','shop00001',21000,'staff00006'),
(12,'2011-1-2','shop00002',22000,'staff00004'),
(13,'2011-1-2','shop00003',23000,'staff00010'),
(14,'2011-1-2','shop00004',24000,'staff00003'),
(15,'2011-1-2','shop00005',25000,'staff00007'),
(16,'2011-1-2','shop00006',26000,'staff00008'),
(17,'2011-1-2','shop00007',27000,'staff00009'),
(18,'2011-1-2','shop00008',28000,'staff00006'),
(19,'2011-1-2','shop00009',29000,'staff00001'),
(20,'2011-1-2','shop00010',30000,'staff00002'),
(21,'2011-1-3','shop00001',31000,'staff00008'),
(22,'2011-1-3','shop00002',32000,'staff00009'),
(23,'2011-1-3','shop00003',33000,'staff00007'),
(24,'2011-1-3','shop00004',34000,'staff00008'),
(25,'2011-1-3','shop00005',35000,'staff00010'),
(26,'2011-1-3','shop00006',36000,'staff00002'),
(27,'2011-1-3','shop00007',37000,'staff00003'),
(28,'2011-1-3','shop00008',38000,'staff00004'),
(29,'2011-1-3','shop00009',39000,'staff00006'),
(30,'2011-1-3','shop00010',40000,'staff00009');