大体でIT

-ちょっと使えるネタを紹介-

大体でIT

Excel VBAでAccessのレコードを追加する方法をご紹介します。Excel VBAからSQL文のINSERTを使ってAccessのレコードを追加することができます。

はじめに

訪問ありがとうございます。この記事ではExcel VBAからAccessへ接続してレコードを追加する方法についてご紹介します。

Excel VBAでAccessのレコードを追加する方法がわかれば、Access VBAがわからなくてもExcel VBAでAccessのレコードを自由に追加することができます。

扱うデータ量が増えてきたり複数人が同時に作業する必要があるといった事情でAccessを使ってみたいと考えている方に参考になるかと思います。

この記事でわかること

  • Excel VBAでSQL文を使ってAccessのレコードを追加する方法

目次

Excel VBAでAccessのレコード追加

Excel VBAでAccessのレコードを追加する方法です。

フィールドの『名前』、『身長』、『体重』がそれぞれ『鈴木』、『190』、『68』であるレコードを追加します。

フィールド『ID』は自動で連番が追加されます。

Accessのレコードを追加した結果

Accessへレコードを追加した結果

新しくレコードが追加されました。Excel VBAのコードについて説明していきます。

Excel VBAコード

Accessのレコードを追加するExcel VBAコードです。

'Accessのレコードを追加する
Sub Test4()
    
    Dim DBpath As String 'Accessファイルのフルパス
    Dim adoCn As Object 'Accessへ接続用のオブジェクト
    Dim strSQL As String 'SQL文
    
    'Accessファイルへ接続する
    Set adoCn = CreateObject("ADODB.Connection") 'Accessへ接続用のオブジェクトを作成
    DBpath = ThisWorkbook.Path & "\Database.accdb" '接続するAccessファイルのフルパス
    adoCn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DBpath & ";" 'Accessファイル(2007~)を開く
    'adoCn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBpath & ";" 'Access(~2003)ファイルを開く

    'レコード追加のSQL文
    strSQL = "INSERT INTO DB(名前,身長,体重) VALUES('鈴木','190','68')" 'レコード追加
    
    '---変数を使う場合---
    Dim CurName, CurTall, CurWeight
    CurName = "鈴木"
    CurTall = 190
    CurWeight = 68
    'レコード追加のSQL文
    strSQL = "INSERT INTO DB(名前,身長,体重) VALUES('" & CurName & "','" & CurTall & "','" & CurWeight & "')"
    '---------------------
    
    'SQLの実行(レコードの更新・追加・削除の場合)
    adoCn.Execute strSQL 'SQLを実行
    
    '後処理
    adoCn.Close 'Accessへの接続を解除する
    Set adoCn = Nothing 'Accessへの接続用のオブジェクトを開放
    
End Sub

少し長いですが、Accessへレコードを追加するのに重要なところ赤色の部分です。

そのほかは、テンプレみたいなものですのであまりに気にする必要はありません。

では、Excel VBAコードについて説明していきます。

オブジェクト作成(Accessへ接続)

Accessへ接続するためのオブジェクトを作成します。

これはテンプレみたいなものですので、あまり重要視する必要はありません。

'Accessファイルへ接続する
Set adoCn = CreateObject("ADODB.Connection") 'Accessへ接続用のオブジェクトを作成
DBpath = ThisWorkbook.Path & "\Database.accdb" '接続するAccessファイルのフルパス
adoCn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DBpath & ";" 'Accessファイル(2007~)を開く
'adoCn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBpath & ";" 'Access(~2003)ファイルを開く

ファイルパスの部分だけ取得したいAccessファイルに合わせて変更するだけでいいです。

Accessのバージョンでコードが変わりますので、Access 2007以降とAccess2003以前のExcel VBAコードを載せておきます。

SQL文(INSERT)

ここが重要です。

Accessにレコードを追加するSQL文です。『INSERT』を使います。

'レコード追加のSQL文
strSQL = "INSERT INTO DB(名前,身長,体重) VALUES('鈴木','190','68')" 'レコード追加

テーブル『DB』のフィールド『名前、身長、体重』に対し『鈴木、190、68』であるレコードを追加する。という意味になります。

フィールド『ID』は自動で連番が追加されますので記載する必要はありません。

変数を使う場合は次のようにします。

'変数を使う場合
Dim CurName, CurTall, CurWeight
CurName = "鈴木"
CurTall = 190
CurWeight = 68
'レコード追加のSQL文
strSQL = "INSERT INTO DB(名前,身長,体重) VALUES('" & CurName & "','" & CurTall & "','" & CurWeight & "')"

『&』と『"』で変数を囲っています。

エクセルでセルに文字列の数式を入れる方法と同じです。

SQL実行(レコード追加)

ここも重要です。

作成したSQLを実行するExcel VBAコードです。

'SQLの実行(レコードの更新・追加・削除の場合)
adoCn.Execute strSQL 'SQLを実行

これでAccessへレコードが追加されます。

後処理

最後に後処理をして終了です。

これはテンプレなので気にせず貼り付ければいいです。

'後処理
adoCn.Close 'Accessへの接続を解除する
Set adoCn = Nothing 'Accessへの接続用のオブジェクトを開放

Accessへの接続を解除して、作成したオブジェクトを開放しています。

おわりに

Excel VBAでSQLを使ってAccessへ接続してレコードを追加する方法をご紹介しました。

データベースを複数人で共有して同時に更新したい場合やデータ容量が増えてきた場合にAccessは便利です。

Accessをデータベースとして使う場合は、Accessの接続制限でファイルが壊れたり動かなくなってしまわないようにExcel VBAから接続する方法をおすすめします。

Excel VBAから接続する方法を使えば入出力フォームもExcel VBAで作成できるのでAccess VBAの知識がなくて簡単です。

参考になればと思います。最後までご覧くださいましてありがとうございました。

関連する記事から探す

カテゴリから探す

カテゴリから見たい項目を探すと便利ですよ。

アーカイブから探す