大体でIT

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

大体でIT

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

はじめに

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

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

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

この記事でわかること

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

目次

Excel VBAでAccessのレコード更新

Excel VBAでAccessのレコードを更新する方法です。

Accessのテーブル『DB』でID『32』のレコードの『身長』を更新してみます。

レコード更新前

Accessのレコードを修正する前

レコードを更新した結果です。

レコード更新後

Accessのレコードを修正した後

IDが『32』のレコードで『身長』が『171』から『123』へ更新されました。

Excel VBAコード

Accessのレコードを更新するExcel VBAコードです。

'Accessのレコードを更新する
Sub Test3()
    
    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 = "UPDATE DB SET 身長='123' WHERE ID=32"
    
    '---変数を使う場合---
    Dim ID, CurTall
    ID = 36
    CurTall = 123
    'レコード更新のSQL文
    strSQL = "UPDATE DB SET 身長='" & CurTall & "' WHERE ID=" & ID
    '---------------------
    
    'SQLの実行(レコードの更新・追加・削除の場合)
    adoCn.Execute strSQL 'SQLを実行
    
    '後処理
    adoCn.Close 'Accessへの接続を解除する
    Set adoCn = Nothing 'Accessへの接続用のオブジェクトを開放
    
End Sub

少し長いコードですが、Accessのレコード更新について重要なところ赤色の部分です。

それ以外はテンプレなのであまり重要視しなくても大丈夫です。

では解説していきます。

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

レコードを更新する際に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 2007以降とAccess2003以前のExcel VBAコードを載せておきます。

SQL文(UPDATE)

ここが重要です。

レコードを更新する際のSQL文です。

'レコード更新のSQL文
strSQL = "UPDATE DB SET 身長='123' WHERE ID=32"

テーブル『DB』のフィールド『ID』が『32』のレコードに対して、フィールド『身長』に『123』を入力しています。

元のデータから『身長』のデータだけ更新しています。

変数を使いたい場合は次のようにするとできます。

'変数を使う場合
Dim ID, CurTall
ID = 32
CurTall = 123
'レコード更新のSQL文
strSQL = "UPDATE DB SET 身長='" & CurTall & "' WHERE ID=" & ID

変数の部分を『&』と『"』で囲って全体を文字列にしています。

SQL実行(レコード更新)

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

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

このExcel VBAコードでAccessのレコードを更新することができます。

このコードは、レコードの更新、追加、削除の場合で共通です。

後処理

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

これはテンプレなので特に重要視せずコピペでいいです。

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

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

おわりに

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

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

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

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

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

関連する記事から探す

カテゴリから探す

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

アーカイブから探す