大体でIT

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

HOME >  Excel VBA >  別アプリケーション操作
大体でIT

Excel VBAからAccessのデータを取得する方法についてご紹介します。Excel VBAからSQL文のSELECTとWHEREを使ってAccessのデータを取得することができます。

はじめに

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

Accessへの接続方法さえわかっておけばAccessをデータベースとして使って、Excel VBAで自由にデータを編集することができます。

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

この記事でわかること

  • Excel VBAでSQL文を使ってAccessのデータを取得する方法

こちらの記事も参考になります

目次

Excel VBAでAccessからデータ取得

あらかじめAccessファイルに『DB』というテーブルを作成しておきます。

『DB』に保存したデータはこちらです。

Accessのテーブル『DB』

取得するAccessのテーブル

テーブルの列が『フィールド』、行が『レコード』といいます。

フィールドの『ID』は自動的に連番が作成されます。

Excel VBAでAccessからデータを取得した結果です。

データを取得した結果

Accessから取得した結果

データを取得できています。

Excel VBAのコードを説明します。

Excel VBAコード

Accessからデータを取得するExcel VBAコードです。

'Accessのテーブルから全レコードを取得
Sub Test1()
    
    Dim DBpath As String 'Accessファイルのフルパス
    Dim adoCn As Object 'Accessへの接続用オブジェクト
    Dim adoRs As Object 'Accessからの取得用オブジェクト
    Dim strSQL As String 'SQL文
    Dim myArray As Variant '全レコードを格納する配列
    Dim tmpFldCnt As Variant 'フィール数
    Dim tmpRcdCnt As Variant 'レコード数
        
    'Accessへ接続する
    Set adoCn = CreateObject("ADODB.Connection") '接続用オブジェクト作成
    DBpath = ThisWorkbook.Path & "\Database.accdb" '接続するファイルのフルパス
    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）を開く
    
    'オブジェクトの設定（取得用）
    Set adoRs = CreateObject("ADODB.Recordset")
    adoRs.CursorLocation = 3 ' クライアントサイドカーソルに変更
    
    'SQL文（全レコード取得）
    strSQL = "SELECT ID,名前,身長,体重 FROM DB"
    
    'SQL文の実行（読込の場合）
    adoRs.Open strSQL, adoCn 'SQLを実行して取得したデータをadoRsへ格納する
    
    tmpFldCnt = adoRs.Fields.Count 'フィールド数を取得
    tmpRcdCnt = adoRs.RecordCount 'レコード数を取得
    
    'レコード数がゼロじゃない場合
    If tmpRcdCnt > 0 Then
        
        '全レコードを配列に格納
        myArray = adoRs.GetRows
        
        '配列を転置する
        Dim DBm As Variant
        ReDim DBm(1 To UBound(myArray, 2) + 1, 1 To UBound(myArray, 1) + 1) As Variant
        For i = 0 To UBound(myArray, 2)
            For j = 0 To UBound(myArray, 1)
                DBm(i + 1, j + 1) = myArray(j, i)
            Next
        Next
        
        'セルへ入力
        With ActiveSheet
            .Range(.Range("A7"), .Range("A7").Offset(UBound(DBm, 1) - 1, UBound(DBm, 2) - 1)) = DBm
        End With
        
    End If
    
    '後処理
    adoCn.Close 'Accessへの接続解除
    Set adoRs = Nothing '取得用オブジェクトの解放
    Set adoCn = Nothing '接続用オブジェクトの解放

End Sub

かなり長いですがAccessからデータ取得するのに重要なところは赤色の部分です。

そのほかはテンプレみたいなものですのであまり気にしなくて大丈夫です。

説明していきます。

オブジェクト作成（Accessへ接続）

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

これはほぼテンプレですのでとりあえずAccessへ接続する際はコピペしましょう。

ファイルパスの部分だけ変えればいいです。

'Accessへ接続する
Set adoCn = CreateObject("ADODB.Connection") '接続用オブジェクト作成
DBpath = ThisWorkbook.Path & "\Database.accdb" '接続するファイルのフルパス
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のバージョンでコードが変わりますので2007以降と2003以前のコードについて記載しておきます。

オブジェクト作成（取得用）

Accessからデータを取得する際に使用するオブジェクトです。

これもテンプレですのでデータを取得したい場合にコピペしましょう。

'オブジェクトの設定（取得用）
Set adoRs = CreateObject("ADODB.Recordset")
adoRs.CursorLocation = 3 ' クライアントサイドカーソルに変更

オブジェクトの作成は決まったものなのであまり深く考えずこういうものなんだぐらいでいいかと思います。

SQL文（SELECT）

この部分が重要です。

SQL文というコードを使います。データベースからデータを取得したりする際に使われるコードになります。

Accessからデータを取得する際のSQL文です。

'SQL文（全レコード取得）
strSQL = "SELECT ID,名前,身長,体重 FROM DB"

『BD』というテーブルから『ID』、『名前』、『身長』、『体重』のすべてのレコードを取得するという意味になります。

SQL文は文字列にするのがポイントですね。

SQL実行＆データ取得

ここも重要です。

SQLを実行してデータを取得します。

'SQL文の実行（データ取得の場合）
adoRs.Open strSQL, adoCn 'SQLを実行して取得したデータをadoRsへ格納する

tmpFldCnt = adoRs.Fields.Count 'フィールド数を取得
tmpRcdCnt = adoRs.RecordCount 'レコード数を取得

'全レコードを配列に格納
myArray = adoRs.GetRows

『adoRs.Open strSQL, adoCn』でSQLを実行して取得したデータをadoRsへ格納しています。

『adoRs.Fields.Count』でフィールド数（列数）を取得できます。

『adoRs.RecordCount』でレコード数（行数）を取得できます。

『adoRs.GetRows』ですべてのデータを取得できます。

データを転置

取得したデータを転置します。

ここ以降はテンプレでとりあえずコピペでいいです。

'配列を転置する
Dim DBm As Variant
ReDim DBm(1 To UBound(myArray, 2) + 1, 1 To UBound(myArray, 1) + 1) As Variant
For i = 0 To UBound(myArray, 2)
    For j = 0 To UBound(myArray, 1)
        DBm(i + 1, j + 1) = myArray(j, i)
    Next
Next

Accessのデータを取得すると行と列が転置された状態なのでそれを転置してあげています。こんなイメージです。

Accessの取得結果を転置する

これでExcelに貼り付ける準備ができました。

データを貼り付け

あとはExcelのシートへデータを貼付けます。

貼り付けたい場所に応じてコードを変えるといいかと思います。

'セルへ入力
With ActiveSheet
    .Range(.Range("A7"), .Range("A7").Offset(UBound(DBm, 1) - 1, UBound(DBm, 2) - 1)) = DBm
End With

貼り付ける範囲を指定した配列をセルへ貼付けています。

後処理

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

ここはテンプレになります。深く考える必要はないです。

'後処理
adoCn.Close 'Accessへの接続解除
Set adoRs = Nothing '取得用オブジェクトの解放
Set adoCn = Nothing '接続用オブジェクトの解放

オブジェクトを解放をすることでこの後のVBAコードにここで使ったオブジェクトが影響しないようにすることができます。

Excel VBAでAccessからデータ取得（条件指定）

Accessから条件を指定してテーブルの値を取得する方法です。

方法は先ほどの読み込みと同じで条件指定のため『WHERE』を使うところが違います。

一部のデータのみを取得したい場合に使用します。

取得するAccessのテーブル『DB』のデータです。

取得するAccessのテーブル『DB』のデータ

条件指定してAccessからデータを取得

この中からフィールドの『名前』の中から『田中』というデータのみを取得します。

取得した結果です。

条件指定してデータを取得した結果

条件指定してAccessからデータを取得した結果

フィールド名『名前』の中の『田中』は一つなので1つのデータが取得されました。条件が一致したデータが複数ある場合は結果も複数になります。

Excel VBAコード（条件指定）

条件指定でAccessからデータを取得するExcel VBAコードです。

'Accessのテーブルから条件を指定してレコードを取得
Sub Test2()
    
    Dim DBpath As String 'Accessファイルのフルパス
    Dim adoCn As Object 'Accessへの接続用オブジェクト
    Dim adoRs As Object 'Accessからの取得用オブジェクト
    Dim strSQL As String 'SQL文
    Dim myArray As Variant '全レコードを格納する配列
    Dim tmpFldCnt As Variant 'フィール数
    Dim tmpRcdCnt As Variant 'レコード数
    Dim GetName '変数でレコードを取得する場合
    
    'Accessへ接続する
    Set adoCn = CreateObject("ADODB.Connection") '接続用オブジェクト作成
    DBpath = ThisWorkbook.Path & "\Database.accdb" '接続するファイルのフルパス
    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）を開く
    
    'オブジェクトの設定（取得用）
    Set adoRs = CreateObject("ADODB.Recordset")
    adoRs.CursorLocation = 3 ' クライアントサイドカーソルに変更
    
    'SQL文（指定したレコードのみ取得）
    strSQL = "SELECT ID,名前,身長,体重 FROM DB WHERE [名前]='田中'"
    
    '---変数にする場合----
    '取得したい名前を設定する
    GetName = "田中"
    
    'SQL文（指定したレコードのみ取得）
    strSQL = "SELECT ID,名前,身長,体重 FROM DB WHERE [名前]='" & GetName & "'"
    '---------------------
    
    'SQL文の実行（取得の場合）
    adoRs.Open strSQL, adoCn  'SQLを実行して対象をadoRsへ
    
    tmpFldCnt = adoRs.Fields.Count 'フィールド数を取得
    tmpRcdCnt = adoRs.RecordCount 'レコード数を取得
        
    'レコード数がゼロじゃない場合
    If tmpRcdCnt > 0 Then
        
        '取得したレコードを配列に格納
        myArray = adoRs.GetRows
        
        '配列を転置する
        Dim DBm As Variant
        ReDim DBm(1 To UBound(myArray, 2) + 1, 1 To UBound(myArray, 1) + 1) As Variant
        For i = 0 To UBound(myArray, 2)
            For j = 0 To UBound(myArray, 1)
                DBm(i + 1, j + 1) = myArray(j, i)
            Next
        Next
    
        'シートへ入力
        With ActiveSheet
            .Range(.Range("A7"), .Range("A7").Offset(UBound(DBm, 1) - 1, UBound(DBm, 2) - 1)) = DBm
        End With

    End If
    
    '後処理
    adoCn.Close 'Accessへの接続解除
    Set adoRs = Nothing '取得用オブジェクトの解放
    Set adoCn = Nothing '接続用オブジェクトの解放

End Sub

かなり長いですがAccessからデータを指定して取得するのに重要なのは赤色の部分です。

それ以外はテンプレみたいなものですのであまり気にしなくていいです。

解説していきます。

オブジェクト作成（Accessへ接続）

先ほどと同じでAccessへ接続するためのオブジェクトを作成します。

これはテンプレです。ファイルパス以外はコピペでいいです。

'Accessへ接続する
Set adoCn = CreateObject("ADODB.Connection") '接続用オブジェクト作成
DBpath = ThisWorkbook.Path & "\Database.accdb" '接続するファイルのフルパス
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からデータを読み込む際のオブジェクトを作成します。

これもテンプレになります。データを取得する際にはとりあえずコピペです。

'オブジェクトの設定（取得用）
Set adoRs = CreateObject("ADODB.Recordset")
adoRs.CursorLocation = 3 ' クライアントサイドカーソルに変更

SQL文（SELECT, WHERE）

ここが重要です。

Accessから条件を指定してデータを取得する際のSQL文です。

条件指定してデータを読み込むSQL文です。『WHERE』を使って条件を指定します。

'SQL文（指定したレコードのみ取得）
strSQL = "SELECT ID,名前,身長,体重 FROM DB WHERE [名前]='田中'"

テーブル『DB』からフィールド名『名前』の中で『田中』というレコードの『ID』、『名前』、『身長』、『体重』を取得する。という意味になります。

SQL文の中に変数を使いたい場合は次のようにします。

'取得したい名前を設定する
GetName = "田中"

'SQL文（指定したレコードのみ取得）
strSQL = "SELECT ID,名前,身長,体重 FROM DB WHERE [名前]='" & GetName & "'"

SQL文は文字列で作成する必要があるので、変数の部分を『&』と『"』を使って囲います。

Excelのセルに数式を使って文字列を入力する方法と同じです。

SQL実行＆データ取得

ここも重要です。コードは先ほどと同じになります。

先ほどと同じように作成したSQL文を実行して、データを取得します。

'SQL文の実行（取得の場合）
adoRs.Open strSQL, adoCn  'SQLを実行して対象をadoRsへ

tmpFldCnt = adoRs.Fields.Count 'フィールド数を取得
tmpRcdCnt = adoRs.RecordCount 'レコード数を取得
    
'取得したレコードを配列に格納
myArray = adoRs.GetRows

『adoRs.Open strSQL, adoCn』でSQLを実行して取得したデータをadoRsへ格納しています。

『adoRs.Fields.Count』でフィールド数（列数）を取得できます。

『adoRs.RecordCount』でレコード数（行数）を取得できます。

『adoRs.GetRows』ですべてのデータを取得できます。

データを転置

データを取得するとデータが転置しているので、このデータを転置してあげます。先ほどと同じです。

これもテンプレで使えます。

'配列を転置する
Dim DBm As Variant
ReDim DBm(1 To UBound(myArray, 2) + 1, 1 To UBound(myArray, 1) + 1) As Variant
For i = 0 To UBound(myArray, 2)
    For j = 0 To UBound(myArray, 1)
        DBm(i + 1, j + 1) = myArray(j, i)
    Next
Next

データを貼り付け

取得したデータをシートへ貼り付けます。これも先ほどと同じです。

データを貼り付けたいセルの場所に応じて変更するといいかと思います。

'シートへ入力
With ActiveSheet
    .Range(.Range("A7"), .Range("A7").Offset(UBound(DBm, 1) - 1, UBound(DBm, 2) - 1)) = DBm
End With

後処理

後処理して終了です。

あと処理は基本テンプレです。あまり重要視しなくてもいいかと思います。

'後処理
adoCn.Close 'Accessへの接続解除
Set adoRs = Nothing '取得用オブジェクトの解放
Set adoCn = Nothing '接続用オブジェクトの解放

おわりに

Excel VBAでSQLを使ってAccessへ接続してデータを取得する方法をご紹介しました。

データベースを複数人で共有して同時に編集したい場合やデータ容量が増えてきた場合に活躍するかと思います。

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

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

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

関連する記事から探す

カテゴリから探す

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

サイト内を検索する

↓キーワードを入力する

アーカイブから探す