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

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

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

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

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

考え方は2つとシンプル

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

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

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

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

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

【ExcelVBA】ExcelのマクロからMicrosoft Accessのデータを取得するには

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

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

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

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

Excelのマクロ

STEP.1
Accessへの接続情報取得
Accessに接続するための接続情報を取得します。
STEP.2
Accessに接続
STEP.2の接続情報をもとにAccessに接続します。
STEP.3
(Access用の)Recordsetオブジェクトのインスタンスの生成
(Access用の)Recordsetオブジェクトのインスタンスを生成します。
このインスタンスは、Accessのテーブルデータの参照・挿入に必要です。
STEP.4
(Access用の)Recordsetオブジェクトに接続先情報を設定
(Access用の)RecordsetオブジェクトのActiveConnectionプロパティに、STEP.1で取得した接続先情報が設定されているConnectionオブジェクトのインスタンスを設定します。
この設定により、(Access用の)Recordsetオブジェクトを開いたり操作することができるようになります。
STEP.5
列名を取得
列名を取得します。
この列名はAccessのテーブルデータを取得するSELECT文で使います。
(SELECT文に直接列名を指定したり、列名は使わずに「SELECT *」でテーブルデータを取得する場合は、本STEPの処理は必要ありません。)
STEP.6
Accessからテーブルデータを取得
SELECT文を実行しAccessからテーブルデータを取得します。
取得したテーブルデータは配列に格納しておきます。
STEP.7
SQL Serverへの接続情報取得
SQL Serverに接続するための接続情報を取得します。
STEP.8
SQL Serverに接続
STEP.7の接続情報をもとにSQL Serverに接続します。
STEP.9
(SQL Server用の)Recordsetオブジェクトのインスタンスの生成
(SQL Server用の)Recordsetオブジェクトのインスタンスを生成します。
このインスタンスは、SQL Serverのテーブルデータの参照・挿入に必要です。
STEP.10
AccessのテーブルデータをインポートするSQL Serverのテーブル名を(SQL Server用の)RecordsetオブジェクトのSourceプロパティに設定
AccessのテーブルデータをインポートするSQL Serverのテーブル名を(SQL Server用の)RecordsetオブジェクトのSourceプロパティに設定します。
STEP.11
(SQL Server用の)Recordsetオブジェクトに接続先情報を設定
(SQL Server用の)RecordsetオブジェクトのActiveConnectionプロパティに、STEP.7で取得した接続先情報が設定されているConnectionオブジェクトのインスタンスを設定します。
この設定により、(SQL Server用の)Recordsetオブジェクトを開いたり操作することができるようになります。
STEP.12
SQL Serverのカーソルを開く
SQL Serverのカーソルを開きます。
STEP.13
インポート先のSQL Serverテーブルの列名を取得
インポート先のSQL Serverテーブルの列名を取得します。
STEP.14
AddNewメソッドでSTEP.6のデータをSQL Serverのテーブルに追加
AddNewメソッドでSTEP.6のデータをSQL Serverのテーブルに追加します。
AddNewメソッドを実行する場合は、STEP.13で取得した列名とSTEP.6で取得したテーブルデータの2つをパラメタに指定します。

コードの例

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

    Dim DBName      As String               'データベース名
    Dim connDB      As String               'データベース接続情報
    Dim tblNM_exp   As String               '取得元のテーブル名(Access)
    Dim tblNM_imp   As String               'インポート先のテーブル名(SQL Server)
    Dim dataStr     As String               'Accessから取得したテーブルデータ格納用変数
    Dim outputFile  As String               'Accessから取得したテーブルデータを書き出すファイル
    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      'レコードセット用変数
        
    'カレントディレクトリのデータベースパスを取得
    DBName = ActiveWorkbook.Path & "\" & "0066.mdb"
 
    'データを取得するテーブル名を取得
    tblNM_exp = "tbl_data_list_ac"
 
    'データベース接続情報の取得
    adoCON.ConnectionString = "provider=Microsoft.ACE.OLEDB.12.0;" _
                            & "Data Source=" _
                            & DBName & ""
    
    'Accessに接続する
    adoCON.Open connDB
 
    'Recordsetオブジェクトのインスタンスを生成する
    Set adoRS = CreateObject("ADODB.Recordset")
    
    'クライアントサイドカーソルに変更
    adoRS.CursorLocation = 3
    
    '先ほど取得したデータベース接続情報が設定されているadoCONをActiveConnectionプロパティに設定し、
    'ConnectionオブジェクトとRecordsetオブジェクトを関連付ける
    '⇒Recordsetオブジェクトを開いたり操作するのに必要な設定
    adoRS.ActiveConnection = adoCON
    
    'カーソルを開く
    adoRS.Open tblNM_exp, adoCON
        
    'Accessからテーブルデータを取得するSQL文を作成
    sqlStr = "SELECT "
    For cnt = 0 To adoRS.Fields.Count - 1
        '列名をSELECT文に使う
        sqlStr = sqlStr & adoRS.Fields.Item(cnt).Name & ","
    Next
    sqlStr = Left(sqlStr, Len(sqlStr) - 1)  '末尾の「'」を削除
    sqlStr = sqlStr & " FROM " & tblNM_exp
    sqlStr = sqlStr & " ORDER BY 1 ASC"
    
    'レコードセットを閉じる
    adoRS.Close
    
    'Accessからテーブルデータを取得する
    adoRS.Open sqlStr, adoCON, adOpenDynamic
    
    'GetRowsメソッドでデータを取得する(取得したデータを配列getValに格納)
    getVal = adoRS.GetRows
    
    'レコードセットを閉じる
    adoRS.Close
    
    'DBの名前
    DBName = "testDataDB"
    
    '(テータを追加する)テーブル名
    tblNM_imp = "tbl_data_list"
    
    'データベース接続情報を取得
    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
    
    With oRS
    
        .Source = tblNM_imp             'データを追加するテーブル名の取得
        .ActiveConnection = oCon        '先ほど取得したデータベース接続情報が設定されているoConをActiveConnectionプロパティに設定し、
                                        'ConnectionオブジェクトとRecordsetオブジェクトを関連付ける
        .CursorType = adOpenKeyset      'カーソルタイプにキーセットカーソル使用
        .LockType = adLockPessimistic   'データの編集中のレコード単位排他的ロック
        .Open                           'カーソルを開く

        '列名格納用配列の要素数の変更(列数を指定)
        ReDim fNMAry(UBound(getVal, 1))
        
        For cnt = 0 To .Fields.Count - 1
            '列名を取得する
            fNMAry(cnt) = .Fields.Item(cnt).Name
        Next
        
        '取得したAccessのテーブルデータ格納用配列の要素数の変更(列数を指定)
        ReDim valAry(UBound(getVal, 1))
        
        For aryCnt_S = 0 To UBound(getVal, 2)

            For aryCnt_F = 0 To UBound(getVal, 1)

                '取得したAccessのテーブルデータを、SQL Serverに挿入するための配列に入れ替える
                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

コードの解説

注目すべきコード①

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

    'Accessからテーブルデータを取得するSQL文を作成
    sqlStr = "SELECT "
    For cnt = 0 To adoRS.Fields.Count - 1
        '列名をSELECT文に使う
        sqlStr = sqlStr & adoRS.Fields.Item(cnt).Name & ","
    Next
    sqlStr = Left(sqlStr, Len(sqlStr) - 1)  '末尾の「'」を削除
    sqlStr = sqlStr & " FROM " & tblNM_exp
    sqlStr = sqlStr & " ORDER BY 1 ASC"
    
    'レコードセットを閉じる
    adoRS.Close
    
    'Accessからテーブルデータを取得する
    adoRS.Open sqlStr, adoCON, adOpenDynamic
    
    'GetRowsメソッドでデータを取得する(取得したデータを配列getValに格納)
    getVal = adoRS.GetRows

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

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

注目すべきコード②

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

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

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

Addnewメソッドとは
Addnewメソッドとは、SQL Serverにテーブルデータを追加するメソッドです。

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

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

注目すべきコード③

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

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

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

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

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

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

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

補足

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

以下の46・47行目と、51行目から54行目が列名を取得しているコードになります。

    'カーソルを開く
    adoRS.Open tblNM_exp, adoCON
    For cnt = 0 To adoRS.Fields.Count - 1
        '列名をSELECT文に使う
        sqlStr = sqlStr & adoRS.Fields.Item(cnt).Name & ","
    Next

まずは47行目のOpenメソッドの引数に、テーブル名が格納された変数tblNM_expと、Accessに接続するのに必要なConnection用変数adoCONを設定してOpenメソッドを実行します。

すると、recordsetオブジェクトFieldsコレクションのItemプロパティのNameプロパティから列名を取得することができるようになります。(53行目で列名を取得しています)

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

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

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

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

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

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

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

参照設定の一覧(下の画像を参考)から次の項目(ライブラリ)にチェックを付けて「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」というオブジェクトを使う場合は参照設定しないと動かない、程度に思って頂ければと思います。

最後に

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

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

  • ①Accessのテーブルデータを抽出するSQL文を記述
  • ②Accessに接続
  • ③ ①のSQL文の条件を満たしたテーブルデータを抽出し、その抽出データをレコードセットから取得
  • ④SQL Serverに接続
  • ⑤ ③のデータを、AddnewメソッドでSQL Serverにインポートする

以上になります。

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

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

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

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

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