【ExcelVBA】AccessのDBに新規でテーブルを作成してそのテーブルにCSVファイルのデータを追加するには

7分

SHARE

この記事では、AccessのDBに新規でテーブルを作成してそのテーブルにCSVファイルのデータを追加する方法についてご説明します。

すぐにソースコードを見たい場合はこちらをクリック

【動画】AccessのDBに新規でテーブルを作成してそのテーブルにCSVファイルのデータを追加する実際の動き

本題に入る前に、まずは次の動画をご覧ください。


テーブル作成するAccessのファイル名をフルパスでGetObject関数の引数に指定して実行したあとにCreate TableのSQL文を実行。

作成したテーブルに対し、追加したいデータが保存されているCSVファイルをTransferTextメソッドの引数に指定して実行すると作成したテーブルにCSVファイルのデータが追加されます。

AccessのDBに新規でテーブルを作成してそのテーブルにCSVファイルのデータを追加するには、次の流れの通りマクロを作成します。

マクロ作成の流れ

STEP.1
GetObject関数でAccessオブジェクトとコレクションを取得し、Applicationクラスの新しいインスタンスを作成する
GetObject関数でAccessオブジェクトとコレクションを取得し、Application クラスの新しいインスタンスを作成します。
STEP.2
TransferTextメソッドを実行してCSVファイルのデータを追加する
TransferTextメソッドを実行してCSVファイルのデータを追加します。

コードの例

Excelのマクロのコードは次の通りに記述していきます。

Option Explicit

Private Sub btn_insCSVFileData_Click()

    Dim sqlStr              As String                   'SQL文
    Dim accApp              As New Access.Application   'Accessアプリケーション参照用変数
    Dim tbldef              As DAO.TableDef             'Accessテーブル定義用変数
    Dim tmptblExistflg      As Boolean                  '一時テーブル存在確認フラグ
    
    Const impTbl            As String = "tbl_datalist"  'CSVのデータを追加するテーブル名
    Const accessFileNM      As String = "0112.mdb"      'Accessファイル名
    Const fldrFileNMExptTxt As String = "data.csv"      'フォルダ名とファイル名を出力させるCSVファイル名
    
    'Accessファイルを開く
    Set accApp = GetObject(ActiveWorkbook.Path & "\" & accessFileNM)

    'Accessを非表示にする
    accApp.Visible = False

    'Accessの確認ダイアログを非表示にする
    accApp.DoCmd.SetWarnings False

    'テーブル存在確認
    For Each tbldef In accApp.CurrentDb.TableDefs

        If tbldef.Name = impTbl Then

            'テーブルが存在している場合

            '変数「tmptblExistflg」にTrue(テーブルが存在している)を設定する
            tmptblExistflg = True

        End If

    Next

    If tmptblExistflg = False Then

        'テーブルが存在しない場合

        'テーブルを作成する
        sqlStr = "CREATE TABLE " & impTbl & "("
        sqlStr = sqlStr & "fdata memo"
        sqlStr = sqlStr & ")"

        'SQL文を実行する
        accApp.DoCmd.RunSQL sqlStr
        
    Else
    
        'テーブルが存在する場合
    
        'テーブルデータを削除する
        sqlStr = "DELETE FROM " & impTbl
        
        'SQL文を実行する
        accApp.DoCmd.RunSQL sqlStr

    End If
    
    'CSVファイルのデータをテーブルにインポートする
    accApp.DoCmd.TransferText acImportDelim, "T定義", impTbl, ActiveWorkbook.Path & "\" & fldrFileNMExptTxt

End Sub

コードの解説

注目すべきコード①

最初に見て頂きたいのは15行目です。

    'Accessファイルを開く
    Set accApp = GetObject(ActiveWorkbook.Path & "\" & accessFileNM)

GetObject関数でAccessオブジェクトとコレクションを取得し、Applicationクラスの新しいインスタンスを作成します。

インスタンスが生成されるとAccessのDoCmdオブジェクトをExcelのマクロ側で扱うことができ、本記事の本題である「AccessのDBに新規でテーブルを作成」「新規で作ったテーブルにCSVファイルのデータを追加」が可能になります。

DoCmdオブジェクトとは、Accessが持つ様々なデータベースオブジェクトをVBAから操作する為に用意されたオブジェクトです。

例えば、Excelのマクロから(Accessのテーブルに対して)SQL文を実行する、ビューを開く、などさまざまな操作が可能になります。

今回実行したい「AccessのDBに新規でテーブルを作成」「新規で作ったテーブルにCSVファイルのデータを追加」といった操作も、DoCmdオブジェクトを使って実行します。

注目すべきコード②

次に見て頂きたいのは42行目から47行目です。

        'テーブルを作成する
        sqlStr = "CREATE TABLE " & impTbl & "("
        sqlStr = sqlStr & "fdata memo"
        sqlStr = sqlStr & ")"
 
        'SQL文を実行する
        accApp.DoCmd.RunSQL sqlStr

42行目から44行目でCREATE TABLE文を作成し、47行目で作成したSQL文を実行します。

実行後はテーブルが作成されます。

注目すべきコード③

次に見て頂きたいのは62行目です。

    'CSVファイルのデータをテーブルにインポートする
    accApp.DoCmd.TransferText acImportDelim, "T定義", impTbl, ActiveWorkbook.Path & "\" & fldrFileNMExptTxt

TransferTextメソッドはCSVファイルのデータをAccessのテーブルに追加します。

なお、今回使ったTransferTextメソッドに設定した引数は次の通りです。

  1. 第1引数：データを取り込む際の変換の種類
  2. 第2引数：インポートの定義名
  3. 第3引数：CSVファイルのデータをインポートするテーブル名
  4. 第4引数：取り込むCSVファイル名

第1引数：データを取り込む際のデータ変換の種類

第1引数には、データを取り込む際のデータ変換の種類を指定します。

今回指定した定数「acImportDelim」は、カンマ、タブなど区切り記号つきのデータをインポートする場合に指定する定数です。

第2引数：インポートの定義名

第2引数には、取り込むCSVデータのインポートの定義名を指定します。

この定義名とは一体何なのかというと、Accessのインポート定義の画面で作成するインポートの定義名のことです。

Accessのインポート定義の作成をあらかじめ行い、インポート定義名をこの第2引数に指定しないとCSVデータのインポートができないので必ずマクロを実行する前に定義の作成を行う必要があります。

Accessのインポート定義の作成については「Accessのインポート定義の作成方法について」の説明をご覧ください。

第3引数：CSVファイルのデータをインポートするテーブル名

第3引数には、CSVファイルのデータをインポートするテーブル名を指定します。

今回は一時テーブル「tmpTbl」を第3引数に指定しています。

第4引数：取り込むCSVファイル名

第4引数には、取り込むCSVファイル名を指定します。

なお、指定するにはCSVファイル名だけではなく、フルパスで指定します。

Accessのインポート定義の作成方法について

TransferTextメソッドを使ってCSVファイルのデータをAccessのテーブルに追加するにはAccessのインポート定義を引数に指定する必要があります。

Accessのインポート定義の作成は次の通りに作成します。

補足

今回のマクロで必要なAccessのインポート定義はマクロの実行中に作成するテーブルに対してです。

マクロを実行する前は新規テーブルは存在しないので、あらかじめ手動で新規テーブルを作成しておき、新規テーブルのインポート定義を作成しておきます。

①テーブルのエクスポートで「テキストファイル」をクリック

エクスポートするテーブルを右クリックし、「エクスポート」→「テキストファイル」をクリックします。

②「データのエクスポート先の選択ウィザード」でファイル名を入力

「データのエクスポート先の選択ウィザード」でファイル名を入力し、OKボタンをクリックします。(参照先とファイル名は任意です。今回はAccessファイルと同じ場所に作成しています)

エクスポートのオプションは任意です。本記事では何も選択しないで説明を続けます。

③「テキストエクスポートウィザード」の「設定」ボタンをクリック

「テキストエクスポートウィザード」の「設定」ボタンをクリックします。

⑤保存ボタンをクリックする

保存ボタンをクリックします。

なお、読み込むCSVファイルの文字コードに合わせて「コードページ」の設定値を変更してください。下の画像のように文字化けする可能性があります。

⑥インポート定義名の入力

「インポート/エクスポート定義の保存」ダイアログボックスでインポート定義名を入力します。

今回は「T定義」という名称にしました。

この「T定義」という名称をTransferTextメソッドの第2引数に設定します。

    accApp.DoCmd.TransferText acImportDelim, "T定義", impTbl, ActiveWorkbook.Path & "\" & fldrFileNMExptTxt

⑦OKをクリックする

OKボタンをクリックします。

⑧完了ボタンをクリックする

完了ボタンをクリックします。

⑨閉じるボタンをクリック

閉じるボタンをクリックします。

エクスポート定義の作成は以上です。

エクスポート定義の確認

エクスポート定義は「エクスポート定義ウィザード」画面で「定義」ボタンをクリックすると、「インポート/エクスポート定義」ダイアログが表示されるので、先ほど作成したエクスポート定義名が表示されていることを確認できればOKです。

目次へ戻る

動作確認

CSVファイルに書き込まれた都道府県の名称を、新規でAccessにテーブルを作りそのテーブルに追加します。

以下はマクロを実行する前のAccessのファイルのテーブル一覧ですが、何もテーブルが存在しない状態です。

以下はマクロを実行した後のAccessのファイルのテーブル一覧です。

新規でテーブルが作成されて、そのテーブルに都道府県の名称が追加されています。

目次へ戻る

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

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

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

  1. Microsoft Access 15.0 Object Library(MSACC.OLB)
  2. microsoft office 15.0 access database engine object library(ACEDAO.DLL)

なぜ必要かというと、Excelのマクロのコードの6行目の「Access.Application」というオブジェクトが「MSACC.OLB」というファイルを、7行目の「DAO.TableDef」というオブジェクトが「ACEDAO.DLL」というファイルを参照するからです。

    Dim accApp              As New Access.Application   'Accessアプリケーション参照用変数
    Dim tbldef              As DAO.TableDef             'Accessテーブル定義用変数

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

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

目次へ戻る

最後に

本記事では、AccessのDBに新規でテーブルを作成してそのテーブルにCSVファイルのデータを追加する方法についてご説明しました。

手動ではなく、マクロの処理中に新規でテーブルを作りたい、そのテーブルにCSVファイルを追加したい場合に参考になるかと思います。

是非参考にしてみてくださいね。

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

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

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

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



目次へ戻る

前の記事

【ExcelVBA】Excelのマクロ・コマンドプロンプト・A…

次の記事

【ExcelVBA】フォルダ構成そのままにサブフォルダ含めて全…