VBAでAccessデータベースを活用しよう～データ追加&削除編～
前回の記事ではAccessデータベースの列の追加と削除を行いました。
↓前回の記事
takehito33711.hatenablog.com
今回はテーブルにデータの追加と削除をする方法を紹介します。
前回作成した列に以下のデータを入力していきます。
| ID | 名前 | 生年月日 | 年齢 | 職業 |
|---|---|---|---|---|
| 1 | サザエ | 1922/11/22 | 27 | 主婦 |
データの追加と削除の方法は、
オブジェクトを使う方法と、SQLを使う方法の2つあります。
お好みの方法を使ってください。
それでは行ってみましょう!
データの追加~オブジェクト使用~
まずはオブジェクトを使ったデータ追加方法をご紹介します。
先にコードを載せておきます。
Public Sub オブジェクト使用データ追加 '参照設定：Microsoft ADO Ext.6.0 for DDL and Security Dim RS As ADODB.Recordset Dim strDB As String Dim DBFile As String DBFile = "ファイルの絶対パス" strDB = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DBFile Set RS = New ADODB.Recordset RS.Source = "テーブル名" RS.ActiveConnection = strDB RS.CursorType = adOpenDynamic RS.LockType = adLockOptimistic RS.Open RS.AddNew RS.Fields("ID") = 1 RS.Fields("名前") = "サザエ" RS.Fields("生年月日") = 1922 / 11 / 22 RS.Fields("年齢") = 27 RS.Fields("職業") = "主婦" RS.Update RS.Close Set RS = Nothing End Sub
★簡単に解説していくよ
データを追加するにはRecordsetオブジェクトを使用します。
4行目に変数をRSとして宣言しています。
RSはこの段階では宣言されただけなので、11行目でRSを召喚しています。
11~16行目はRecordcetのopen方法を指定しています。
ここで注意なのが、15行目のLockTypeです。
規定値では読み取り専用の「adLockReadOnly」となっているので、ここでは「adLockOptimistic」としてください。
18行目以降でデータの入力を行っています。
RS.AddNew
RS.Field("列名")=データ
RS.Update
の部分がデータ入力の公式となりますね。
26行目以降は終了操作になります。
データの追加~SQL使用~
次はSQLを用いたデータ追加の方法を紹介します。
まずはコードから
Public Sub SQL使用データ追加 '参照設定：Microsoft ADO Ext.6.0 for DDL and Security Dim CMD As ADODB.Command Dim strDB As String Dim DBFile As String Dim SQL As String DBFile = "ファイルの絶対パス" strDB = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DBFile SQL = "INSERT INTO テーブル名(ID,名前,生年月日,年齢,職業)" & _ " VALUES (1,'サザエ',#1922/11/22#,27,'主婦')" Set CMD = New ADODB.Command CMD.ActiveConnection = strDB CMD.CommandText = SQL CMD.Execute Set CMD = Nothing End Sub
★簡単に解説していくよ
SQLを用いたデータ追加は次のようになります。
SQL = "INSERT INTO テーブル名(列名1,列名2,....) VALUES (データ1,データ2,....)"
列名1にデータ1が、列名2にデータ2が入ります。
ここで注意点なのですが、データは
文字列なら「'データ'」のように「'」でくくり、
日付型なら「#データ#」のように「#」でくくる必要があります。
SQLを実行するにはCommandオブジェクトが必要になるので始めに宣言しておきましょう。
データの削除~オブジェクト使用~
続いてデータの削除を行っていきます。
以下は先ほどのデータを削除するコードです。
Public Sub オブジェクト使用データ削除() '参照設定：Microsoft ADO Ext.6.0 for DDL and Security Dim RS As ADODB.Recordset Dim strDB As String Dim DBFile As String DBFile = "ファイルの絶対パス" strDB = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DBFile Set RS = New ADODB.Recordset RS.Source = テーブル名 RS.ActiveConnection = strDB RS.CursorType = adOpenDynamic RS.LockType = adLockOptimistic RS.Open RS.Fields("ID") = 1 RS.Delete RS.Close Set RS = Nothing End Sub
★簡単に解説していくよ
16行目まではオブジェクト使用データ追加と一緒ですね。
18行目で削除する対象を指定し、19行目で削除を実行しています。
このコードではIDが1であるものは全て削除されます。
削除する対象は複数指定することができます。
例えばIDが1で、名前がサザエであるデータを削除したいときは、
RS.Fields("ID") = 1
RS.Fields("名前") = "サザエ"
RS.Delete
とすることで削除できます。
したがってデータ削除の公式は次のように書けます。
RS.Fields("列名X") = データ名x
RS.Fields("列名Y") = データ名y
RS.Fields("列名Z") = データ名z
...
RS.Delete
これで列名X、Y、Zのデータx、y、zである全てのデータを削除することができました。
データの削除~SQL使用~
最後にSQLを使用したデータ削除をご紹介します。
コードはこちら
Public Sub SQL使用データ削除() '参照設定：Microsoft ADO Ext.6.0 for DDL and Security Dim CN As ADODB.Connection Dim CMD As ADODB.Command Dim strDB As String Dim DBFile As String Dim SQL As String Dim ID As Integer Dim i DBFile = "ファイルの絶対パス" strDB = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DBFile SQL = "DELETE FROM テーブル名 WHERE ID =1" Set CMD = New ADODB.Command CMD.ActiveConnection = strDB CMD.CommandText = SQL CMD.Execute Set CMD = Nothing End Sub
★簡単に解説していくよ
SQLを使用したデータ削除の方法は次のようになります。
SQL="DELETE FROM テーブル名 WHERE 条件"
このSQL文で条件に該当するデータ全てが削除されることになります。
条件を複数設定したい場合はWHEREのあとに「AND」で繋ぎます。
例えばIDが1で、名前がサザエであるデータを削除したいときは
SQL="DELETE FROM テーブル名 WHERE ID=1 AND 名前='サザエ'"
となります。
まとめてデータ追加
最後に以下のデータをまとめてAccessに入れてみましょう。
まとめて入れるにはFor~nextで繰り返しを行っていきます。
Public Sub データまとめて追加() '参照設定：Microsoft ADO Ext.6.0 for DDL and Security Dim RS As ADODB.Recordset Dim strDB As String Dim DBFile As String Dim i DBFile = "ファイルの絶対パス" strDB = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DBFile Set RS = New ADODB.Recordset RS.Source = テーブル名 RS.ActiveConnection = strDB RS.CursorType = adOpenDynamic RS.LockType = adLockOptimistic RS.Open For i = 2 To 8 RS.AddNew RS.Fields("ID") = Cells(i, 1) RS.Fields("名前") = Cells(i, 2) RS.Fields("生年月日") = Cells(i, 3) RS.Fields("年齢") = Cells(i, 4) RS.Fields("職業") = Cells(i, 5) RS.Update Next i RS.Close Set RS = Nothing MsgBox "終了しました" End Sub
メッセージボックスに「終了しました」が表示されれば成功です。
お疲れ様でした。
