Excel VBA ADOで外部データベースのテーブルにデータを追加する

スポンサーリンク
スポンサーリンク

外部データベースのテーブルにデータを追加する方法

外部データベースのテーブルに更新可能な空白の
新しいレコードを追加するには
Recordset【レコードセット】オブジェクト
AddNew【アドニュー】メソッドを使用します。

新しいレコードが追加されると
追加されたレコードにカーソルが移動します。

追加したレコードの各Field【フィールド】オブジェクト
Value【バリュー】プロパティでデータを設定し
Recordset【レコードセット】オブジェクトの
Update【アップデイト】メソッドを使用して
レコードの更新処理を実行します。

Recordset.AddNew【レコードセットアドニュー】メソッド

外部データベースのテーブルに新しいレコードを追加するには
Recordset【レコードセット】オブジェクトの
AddNew【アドニュー】メソッドを使用します。

Recordset.AddNew【レコードセットアドニュー】メソッドの書式

オブジェクト変数.AddNew


設定値の説明


Recordset.Update【レコードセットアップデイト】メソッド

外部データベースのレコードの更新処理を実行するには
Recordset【レコードセット】オブジェクトの
Uodate【アップデイト】メソッドを使用します。

Recordset.Update【レコードセットアップデイト】メソッドの書式

オブジェクト変数.Update


設定値の説明

  • オブジェクト変数(必須)
    Recordset【レコードセット】オブジェクトのインスタンスが格納されている
    オブジェクト変数を指定します。

Accessの商品マスターテーブルに新しいレコードを追加して
ワークシートの商品マスターデータを登録する例


コード例

Sub レコード追加()
Dim コネクション As New ADODB.Connection
Dim レコード As New ADODB.Recordset
コネクション.Open ConnectionString:= _
"Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=C:¥Users¥Desktop¥Database1.accdb;"
レコード.Open Source:="商品マスター", ActiveConnection:=コネクション, _
CursorType:=adOpenKeyset, LockType:=adLockPessimistic
With レコード
.AddNew
.Fields("商品コード").Value = Range("A2").Value
.Fields("商品名").Value = Range("B2").Value
.Fields("仕入れ単価").Value = Range("C2").Value
.Fields("販売単価").Value = Range("D2").Value
On Error GoTo エラー処理
.Update
End With
MsgBox "商品データを登録しました。"
レコード.Close: Set レコード = Nothing
コネクション.Close: Set コネクション = Nothing
Exit Sub
エラー処理:
MsgBox "データが重複しています。"
Set レコード = Nothing
コネクション.Close: Set コネクション = Nothing
End Sub

ADOのライブラリファイルへの参照設定がされていることが前提のコード例です。


コードの解説

2行目
【Dim コネクション As New ADODB.Connection】

DimステートメントでNewキーワード使用して外部データベースとの
接続を確立する
Connection【コネクション】オブジェクトのインスタンスを生成して
オブジェクト変数「コネクション」に格納します


3行目
【Dim レコード As New ADODB.Recordset】

DimステートメントでNewキーワード使用して外部データベースの
レコードを参照する
Recordset【レコードセット】オブジェクトのインスタンスを生成して
オブジェクト変数「レコード」に格納します。


4行目~6行目
【コネクション.Open ConnectionString:= _
“Provider=Microsoft.ACE.OLEDB.12.0;” & _
“Data Source=C:¥Users¥Desktop¥Database1.accdb;”】

Connection【コネクション】オブジェクトのOpenメソッドを
使用して外部データベースの接続をします。


7行目~8行目
【レコード.Open Source:=”商品マスター”, ActiveConnection:=コネクション, _
CursorType:=adOpenKeyset, LockType:=adLockPessimistic】
Recordset【レコードセット】オブジェクトのOpenメソッド
使用して外部データベースのレコードを参照します。
引数CursorType【カーソルタイプ】を他のユーザーによって削除された
レコードにアクセスできない点以外、すべての動作が許可される
動的カーソルのadOpenKeyset【キーセット】に指定し
引数LockType【ロックタイプ】を編集直後のレコードをロックする
adLockPessimistic【パスミステイク】に指定します。


9行目
【With レコード】

Rcordset【レコードセット】オブジェクトが格納されている
オブジェクト変数「レコード」を
Withステートメントで指定します。


10行目
【.AddNew】
Withステートメントで指定した
Recordset【レコードセット】オブジェクトが格納されている
オブジェクト変数「レコード」に対して
AddNew【アドニュー】メソッドで新しい空白のレコードを
追加してカーソルを取得します。


11行目
【.Fields(“商品コード”).Value = Range(“A2”).Value】

Withステートメントで指定した
Recordset【レコードセット】オブジェクトが格納されている
オブジェクト変数「レコード」に対して
Fields【フィールズ】プロパティ
商品コードフィールドのField【フィールド】オブジェクトを参照して
A2セルの値を代入します。


12行目
【.Fields(“商品名”).Value = Range(“B2”).Value】

Withステートメントで指定した
Recordset【レコードセット】オブジェクトが格納されている
オブジェクト変数「レコード」に対して
Fields【フィールズ】プロパティで
商品名フィールドのField【フィールド】オブジェクトを参照して
B2セルの値を代入します。


13行目
【.Fields(“仕入れ単価”).Value = Range(“C2”).Value】

Withステートメントで指定した
Recordset【レコードセット】オブジェクトが格納されている
オブジェクト変数「レコード」に対して
Fields【フィールズ】プロパティで
仕入れ単価フィールドのField【フィールド】オブジェクトを参照して
C2セルの値を代入します。


14行目
【.Fields(“販売単価”).Value = Range(“D2”).Value】

Withステートメントで指定した
Recordset【レコードセット】オブジェクトが格納されている
オブジェクト変数「レコード」に対して
Fields【フィールズ】プロパティで
販売単価フィールドのField【フィールド】オブジェクトを参照して
D2セルの値を代入します。


15行目
【On Error GoTo エラー処理】

外部データベースの主キーとなる商品コードが
既に登録されている場合(重複している場合)
次のUpdateメソッド(データの更新)でエラーになるので
処理が中断してデバックモードにならないように
On Error(オンエラー)ステートメント
GoToステートメントを使用して
エラーが発生した場合、エラー処理ラベルがある
22行目まで処理をジャンプします。


16行目
【.Update】

Withステートメントで指定した
Recordset【レコードセット】オブジェクトが格納されている
オブジェクト変数「レコード」に対して
Update【アップデイト】メソッドを実行して
レコードの更新を実行します。


18行目
【MsgBox “商品データを登録しました。”】

MsgBox【メッセージボックス】関数を使用して
データの登録が完了したことをユーザーに伝えます。


19行目
【レコード.Close: Set レコード = Nothing】

Recordset【レコードセット】オブジェクトのClose【クローズ】メソッドで
レコードの参照を切断し、Nothing【ナッシング】を代入して
メモリー領域を解放します。


20行目
【コネクション.Close: Set コネクション = Nothing】
Connection【コネクション】オブジェクトのClose【クローズ】メソッドで
外部データベースの接続を切断し、Nothing【ナッシング】を代入して
メモリー領域を解放します。


21行目
【Exit Sub】

Exit【エグジット】ステートメントで
Sub【サブ】プロシージャーを途中で終了させます。


22行目
【エラー処理:】

16行目のUpdate【アップデイト】メソッドで
主キーの商品コードの重複がありエラーになった場合
このラベルまで処理がジャンプします。
以降の処理はエラーが発生した場合の処理になります。


23行目
【MsgBox “データが重複しています。”】

MsgBox【メッセージボックス】関数を使用して
ユーザーにエラーが発生したことを伝えます。


24行目
【Set レコード = Nothing】

エラーが発生している
Recordsetオブジェクトのインスタンス(複製)が
格納されているオブジェクト変数「レコード」に
Nothing【ナッシング】を代入してメモリーを解放します。


25行目
【コネクション.Close: Set コネクション = Nothing】

Connection【コネクション】オブジェクトのClose【クローズ】メソッドで
外部データベースの接続を切断し、Nothing【ナッシング】を代入して
メモリー領域を解放して処理を終了します。


実行結果


以上で
ADOで外部データベースのテーブルにデータを追加する方法についての
解説を終了します。
ありがとうございます。

スポンサーリンク
スポンサーリンク

フォローする

スポンサーリンク
スポンサーリンク