【ExcelVBA】SQL ServerのテーブルデータをAccessのテーブルにインポートするには

この記事では、SQL ServerのテーブルデータをAccessのテーブルにインポートする方法についてご説明します。

【動画】SQL ServerのテーブルデータをAccessのテーブルにインポートする実際の動き

本題に入る前に、まずは次のツイートをご覧ください。

SQL ServerのテーブルデータをAccessのテーブルにインポートするマクロを作ってみました😊

テーブルのデータ型は、
・整数型と数値型
・文字列型とテキスト型
・日付型と日付/時刻型

※前者がSQL Server、後者がAccess

とりあえず上記の型で試してみました😃#ExcelVBA#SQLServer#Access pic.twitter.com/khT04rLZ9d

— まさ@Excel、VBA、マクロ（経験年数16年） (@masamasa9785) August 27, 2021

SQL Serverのテーブルデータをマクロが取得し、その取得したテーブルデータをAccessのテーブルにインポートしています。

考え方は2つとシンプル

SQL ServerのテーブルデータをAccessのテーブルにインポートするのに、考え方は次の2つです。

1. SQL Serverからテーブルデータを取得
2. 取得したSQL ServerのテーブルデータをAccessのテーブルにインポート

①SQL Serverからテーブルデータを取得

まずはSQL Serverからテーブルデータを取得します。

SQL Serverからテーブルデータを取得するには次の記事が参考になるかと思います。

4月 10, 2021【ExcelVBA】マクロからSQL Serverのデータを取得するには

②取得したテーブルデータをAccessのテーブルにインポート

SQL Serverからテーブルデータを取得したら、RecordsetオブジェクトのAddnewメソッドに渡して(引数に指定して)実行することでAccessのテーブルにインポートすることができます。

SQL ServerのテーブルデータをAccessのテーブルにインポートする方法

SQL ServerのテーブルデータをAccessのテーブルにインポートするには、次の流れの通りにコードを書いていきます。

Excelのマクロ

コードの例

Excelのマクロのコード(例)

    Dim DBName      As String               'データベース名
    Dim connDB      As String               'データベース接続情報
    Dim tblNM_exp   As String               '取得元のテーブル名(SQL Server)
    Dim tblNM_imp   As String               'インポート先のテーブル名(Access)
    Dim dataStr     As String               'SQL Serverから取得したテーブルデータ格納用変数
    Dim outputFile  As String               'SQL Serverから取得したテーブルデータを書き出すファイル
    Dim itm         As Variant              '配列から取得した値を格納する変数
    Dim sqlStr      As String               'SQL文
    Dim cnt         As Long                 'カウンタ
    Dim aryCnt_F    As Long                 '2次元配列用カウンタ
    Dim aryCnt_S    As Long                 '2次元配列用カウンタ
    Dim dataNum     As Long                 'データ総数
    Dim getVal()    As Variant              '取得したデータ格納用並列
    Dim valAry()    As Variant              '編集データを格納する配列
    Dim fNMAry()    As Variant              '列名格納用配列
    Dim oCon        As ADODB.Connection     'Connection用変数
    Dim oRS         As ADODB.Recordset      'レコードセット用変数
    Dim adoCON      As New ADODB.Connection 'Connection用変数
    Dim adoRS       As ADODB.Recordset      'レコードセット用変数
    
    'カウンタを初期化する
    cnt = 0
 
    'DBの名前
    DBName = "testDataDB"
 
    'データを取得するテーブル名を取得(SQL Server側)
    tblNM_exp = "tbl_data_list"
    
    'DB(SQL Server)接続情報を取得
    connDB = "Provider=SQLNCLI11.1;"
    connDB = connDB & "Data Source=(LocalDB)\MSSQLLocalDB;"
    connDB = connDB & "Initial Catalog=" & DBName & ";"
    connDB = connDB & "Trusted_Connection=yes;"
    
    'Connectionオブジェクトのインスタンスを生成する
    Set oCon = New ADODB.Connection
    
    'SQL Serverに接続する
    oCon.Open connDB
 
    'Recordsetオブジェクトのインスタンスを生成する
    Set oRS = New ADODB.Recordset
 
    '先ほど取得したデータベース接続情報が設定されているoConをActiveConnectionプロパティに設定し、
    'ConnectionオブジェクトとRecordsetオブジェクトを関連付ける
    '⇒Recordsetオブジェクトを開いたり操作するのに必要な設定
    oRS.ActiveConnection = oCon
    
    'テーブルの列名を取得する
    sqlStr = "SELECT"
    sqlStr = sqlStr & " c.name "
    sqlStr = sqlStr & " FROM"
    sqlStr = sqlStr & " sys.objects t"
    sqlStr = sqlStr & " INNER JOIN sys.columns c ON "
    sqlStr = sqlStr & " t.object_id = C.object_id "
    sqlStr = sqlStr & " WHERE "
    sqlStr = sqlStr & " t.type = 'U'"
    sqlStr = sqlStr & " AND t.name='" & tblNM_exp & "' "
    sqlStr = sqlStr & " Order BY "
    sqlStr = sqlStr & " C.column_id "
 
    'データを抽出するSQL文をSourceプロパティに設定する
    oRS.Source = sqlStr
     
    '指定したテーブルのデータを参照する(アクセスする)
    oRS.Open
    
    'GetRowsメソッドでデータを取得する(取得したデータを配列getValに格納)
    getVal = oRS.GetRows
    
    'レコードセットを閉じる
    oRS.Close
    
    '列名格納用配列の要素数の変更(列数を指定)
    ReDim fNMAry(UBound(getVal, 2))
    
    'SQL Serverからテーブルデータを取得する
    sqlStr = "SELECT "
    For Each itm In getVal()
        '取得した列名をSELECT文に使う
        sqlStr = sqlStr & itm & ","
        fNMAry(cnt) = itm
        cnt = cnt + 1
    Next
    sqlStr = Left(sqlStr, Len(sqlStr) - 1)  '末尾の「'」を削除
    sqlStr = sqlStr & " FROM " & tblNM_exp
    sqlStr = sqlStr & " ORDER BY 1 ASC"

    'データを抽出するSQL文をSourceプロパティに設定する
    oRS.Source = sqlStr
 
    '指定したテーブルのデータを参照する(アクセスする)
    oRS.Open
    
    'GetRowsメソッドでデータを取得する(取得したデータを配列getValに格納)
    getVal = oRS.GetRows
    
    'レコードセットを閉じる
    oRS.Close
     
    'カレントディレクトリのデータベースパスを取得(Access)
    DBName = ActiveWorkbook.Path & "\" & "0065.mdb"
 
    'データを挿入するテーブル名を取得(Access側)
    tblNM_imp = "tbl_data_list_ac"
    
    'Accessへの接続情報を取得する
    adoCON.ConnectionString = "provider=Microsoft.ACE.OLEDB.12.0;" _
                            & "Data Source=" _
                            & DBName & ""
                            
    'Accessに接続する
    adoCON.Open
 
    'レコードセットの作成
    Set adoRS = New ADODB.Recordset
    
    With adoRS
        
        .Source = tblNM_imp             'データを追加するテーブル名
        .ActiveConnection = adoCON      '先ほど取得したデータベース接続情報が設定されているadoCONをActiveConnectionプロパティに設定し、
                                        'ConnectionオブジェクトとRecordsetオブジェクトを関連付ける
        .CursorType = adOpenKeyset      'カーソルタイプにキーセットカーソル使用
        .LockType = adLockPessimistic   'データの編集中のレコード単位排他的ロック
        .Open                           'SQL Serverに接続

        '列名格納用配列の要素数の変更(列数を指定)
        ReDim fNMAry(UBound(getVal, 1))
        
        For cnt = 0 To .Fields.Count - 1
            '列名をSELECT文に使う
            fNMAry(cnt) = .Fields.Item(cnt).Name
        Next
        
        '取得したSQL Serverのテーブルデータ格納用配列の要素数の変更(列数を指定)
        ReDim valAry(UBound(getVal, 1))
        
        'カウンタを初期化する
        aryCnt_S = 0
        
        For aryCnt_S = 0 To UBound(getVal, 2)

            For aryCnt_F = 0 To UBound(getVal, 1)

                '取得したSQL Serverのテーブルデータを、Accessに挿入するための配列に入れ替える
                valAry(aryCnt_F) = getVal(aryCnt_F, aryCnt_S)

            Next

            'テーブルにデータを追加する
            .AddNew fNMAry, valAry

            '変更内容を保存する
            .Update
            
            DoEvents

        Next
    
    End With
        
    '各終了処理
    oCon.Close
    If Not oRS Is Nothing Then Set oRS = Nothing
    If Not oCon Is Nothing Then Set oCon = Nothing

コードの解説

注目すべきコード①

最初に見て頂きたいのは79行目から97行目です。

    'SQL Serverからテーブルデータを取得する
    sqlStr = "SELECT "
    For Each itm In getVal()
        '取得した列名をSELECT文に使う
        sqlStr = sqlStr & itm & ","
        fNMAry(cnt) = itm
        cnt = cnt + 1
    Next
    sqlStr = Left(sqlStr, Len(sqlStr) - 1)  '末尾の「'」を削除
    sqlStr = sqlStr & " FROM " & tblNM_exp
    sqlStr = sqlStr & " ORDER BY 1 ASC"
 
    'データを抽出するSQL文をSourceプロパティに設定する
    oRS.Source = sqlStr
 
    '指定したテーブルのデータを参照する(アクセスする)
    oRS.Open
    
    'GetRowsメソッドでデータを取得する(取得したデータを配列getValに格納)
    getVal = oRS.GetRows

このコードでSQL Serverのテーブルデータを取得します。

79行目から88行目でSELECT文を用意し、97行目のGetrowsメソッドでテーブルデータを取得します。

注目すべきコード②

最初に見て頂きたいのは152行目から155行目です。

            'テーブルにデータを追加する
            .AddNew fNMAry, valAry
 
            '変更内容を保存する
            .Update

152行目でRecordsetオブジェクトのAddnewメソッドにSQL Serverのテーブルデータを引数に指定して実行することでAccessのテーブルにインポートすることができます。

なお、SQL ServerのテーブルデータはAddnewメソッドの第2引数に、第1引数にはAccessのテーブルの列名を指定します。

上記のコードだと、配列fNMAryにAccessのテーブルの列名が格納されています。

注目すべきコード③

最初に見て頂きたいのは144行目から149行目です。

            For aryCnt_F = 0 To UBound(getVal, 1)
 
                '取得したSQL Serverのテーブルデータを、Accessに挿入するための配列に入れ替える
                valAry(aryCnt_F) = getVal(aryCnt_F, aryCnt_S)
 
            Next

147行目で、SQL Serverのテーブルデータが格納されている配列getValから配列valAryに入れ替えています。

なぜわざわざ入れ替えているのかというと、「注目すべきコード②」で説明したAddnewメソッドの引数には単一行のデータが格納されている配列だけでしか使えないからです。(複数行のデータが格納されている配列を引数に指定して実行するとエラーになります)

配列getValに複数行のデータが入っている場合はそのままAddnewメソッドの引数に指定できないため、わざわざ配列getValのデータを1行ずつ別の配列に入れ替えているというわけです。

144行のFor文は配列getValの列数分繰り返します。

もし配列getValが5列ある場合は、5回ループを繰り返します。

補足

本記事では、取得するSQL Serverテーブルデータのテーブルの列名を取得しています。

    'テーブルの列名を取得する
    sqlStr = "SELECT"
    sqlStr = sqlStr & " c.name "
    sqlStr = sqlStr & " FROM"
    sqlStr = sqlStr & " sys.objects t"
    sqlStr = sqlStr & " INNER JOIN sys.columns c ON "
    sqlStr = sqlStr & " t.object_id = C.object_id "
    sqlStr = sqlStr & " WHERE "
    sqlStr = sqlStr & " t.type = 'U'"
    sqlStr = sqlStr & " AND t.name='" & tblNM_exp & "' "
    sqlStr = sqlStr & " Order BY "
    sqlStr = sqlStr & " C.column_id "
 
    'データを抽出するSQL文をSourceプロパティに設定する
    oRS.Source = sqlStr
     
    '指定したテーブルのデータを参照する(アクセスする)
    oRS.Open
    
    'GetRowsメソッドでデータを取得する(取得したデータを配列getValに格納)
    getVal = oRS.GetRows

この列名はSQL Serverのデータを取得するSELECT文で使います。

ただし、SELECT文に直接列名を指定したり、列名は使わずに「SELECT *」でテーブルデータを取得する場合は、列名の取得は不要です。

もしテーブルの列名を使いたい場合は次にお見せする、列名を取得するSELECT文を参考にしてください。

SELECT
  c.name
FROM
  sys.objects t
  INNER JOIN sys.columns c ON t.object_id = C.object_id
WHERE
  t.type = 'U'
  AND t.name = 'tbl_data_list'
Order BY
  C.column_id

テーブルの列数や列の型はSQL ServerとAccessで合わせましょう

テーブルの列数や列の型はSQL ServerとAccessで合わせましょう。

列数が違っていたり、列の型が違う場合はマクロ実行時にエラーとなりますので、SQL ServerとAccessのテーブルの状態を確認しましょう。

【注意】参照設定が必要です

一つ注意点があるのですが、先ほどのコードを動かすには参照設定が必要です。

参照設定の一覧(下の画像を参考)から次の項目(ライブラリ)にチェックを付けて「OK」ボタンをクリックします。

1. Microsoft ActiveX Data Objects 2.8 Library(msado28.tlb)

なぜ必要かというと、先ほどのコードの16行目と18行目の「ADODB.Connection」と、17行目と19行目の「ADODB.Recordset」というオブジェクトが、「msado28.tlb」というファイルを参照するからです。

    Dim oCon        As ADODB.Connection     'Connection用変数
    Dim oRS         As ADODB.Recordset      'レコードセット用変数
    Dim adoCON      As New ADODB.Connection 'Connection用変数
    Dim adoRS       As ADODB.Recordset      'レコードセット用変数

この参照設定をしないと下の画像のエラーが出ますので必ず行う必要があります。

ここでは「msado28.tlb」とは何者かについては記事の本題から逸れてしまうので詳細は割愛しますが、マクロで「ADODB.Connection」「ADODB.Recordset」というオブジェクトを使う場合は参照設定しないと動かない、程度に思って頂ければと思います。

最後に

本記事では、SQL ServerのテーブルデータをAccessのテーブルにインポートする方法についてご説明しました。

ザックリとした処理の流れとしては、

以上になります。

SQL ServerのテーブルデータをAccessのテーブルにインポートしたい時に本記事を参考にしていただけたら幸いです。

プログラミングのスキルを習得するなら

プログラミングのスキルを習得したい、今のスキルをもっと高めたい、そう考えているなら「プログラミングスクール」がおすすめです。

プログラミングのスキルの基礎を身につけるなら「TechAcademy」で1週間の無料体験があるので、これで「プログラミングの基礎」を学ぶのにおすすめですよ。

→　TechAcademyの「1週間 無料体験」はこちら