Access + Node.js + MySQLでリモート接続

Microsoft Accessは閉じたLAN内で使う分には申し分無い開発環境なのですが、リモートのDBサーバに接続したり、クラウドのSQLに接続して使ったシステムを構築する場合、いくつか気を遣う場面が存在します。とりわけ

1. ODBC経由でのリンクテーブルだと数万件のテーブルを見るだけでも遅くてカクカク
2. 単純なリンクテーブルの場合、そのテーブル内のデータが全部丸見えで、ケースによっては困る。
3. ADOを使ったインメモリレコードセットは接続時は遅いがスクロールは早い。ADOを使いこなせないといけない
4. MySQLなどの場合、Accessでクライアントを作る場合、クライアント側にODBCドライバと接続設定を追加する手間がある。DSN設定も面倒ですね。
5. Electronでクライアントを作る場合、ODBCドライバは不要ですが
6. VBAのようにクライアント側をあれこれ流用できない（Outlook起動してメール送り付けるなど）
7. セキュリティ面から、MySQLサーバへ直接クライアントから接続させたくない（localhostからの接続だけ受け付けたい）
8. VBAからは資格情報マネージャに直接アクセスできないので、パスワードの保存に気を使う

などなど。3.は割とこれまで業務用のAccessでは使われてきた手だと思います。ODBC、インメモリに加えて今回、Node.jsを間に加えてMySQLへREST API的に接続する手法をやってみたいと思います。もちろん、ODBCドライバもリンクテーブルも利用しません。

今回使用するファイルやライブラリ

• Microsoft Access 2013
• Node.js
• MySQL Server 5.7
• Ubuntu Linux 18.04 LTS

今回はクライアント側およびサーバー側にいくつかのロジックを追加している為、追加のモジュールが必要となっています。

• Node.js側で暗号化された文字列を復号化するcryptoモジュール（標準装備の為、追加不要）
• Node.js側でRESt APIを構築する為のexpressモジュール
• Node.js側でクライアント側からのリクエストパラメータを取得する為のbody-parserモジュール（expressを入れれば使える）
• Node.js側でMySQLへPromiseな感じで接続するpromise-mysqlモジュール
• Access側でパスワードを暗号化する為のAES128bit暗号化クラスモジュール
• Access側で文字列を色々変換する文字列<->バイト列変換ユーティリティの標準モジュール
• Access側でBase64文字列をエンコード・デコードするモジュール
• Access側でJSONを構築する為のVBA-JSONライブラリ
• Access側でレジストリの読み書きをするライブラリ
• Access側でデータ送信時にDictionaryを利用しているので、「Microsoft Scripting Runtime」を参照設定で追加します。

※MySQLのサーバ管理はHeidiSQLを利用すると簡単に管理ができるようになります。オススメ。

サーバー側（Node.js）

事前準備

Ubuntu Linux 18.04 LTSに対して、MySQL Server 5.7をインストールしてあります。また、Node.jsもインストール済みとします。サーバ側での実質追加モジュールはexpressなので、以下の手順で今回の環境を構築しましょう。この辺のくだりはこちらで詳しい作業を記録しておきましたので、参考にしてみてください。

1. 適当にホームディレクトリ内にフォルダを作っておく（例：mysqlapi）
2. ターミナルを起動し、1.のディレクトリ内に移動し、npm init -yを実行する。package.jsonが作成されます。
3. 続けて、ターミナルにて　npm install express --save にて、expressを導入する
4. 続けて、ターミナルにて　npm install promise-mysql にて、promise-mysqlを導入する
5. フォルダ内にindex.jsという空のファイルを作っておく。ここにコードを記述しておきます。
6. 予め、MySQLにtestdbというデータベースといくつかのテーブルを適当に作っておきます。
7. コードを記述したら、node index.jsにてスクリプトを起動します。Port:3200にて待機し始めます。

図：HeidiSQLでDB内を見てみた様子

図：スクリプト起動中の様子

ソースコード

基本パート

'use strict'

//Crypt用の設定
var crypto = require("crypto");
var key = "12345678abcdefgh";
var ive = "hgfedcba87654321";

//MySQL接続用モジュール読み込み
const mysql = require('promise-mysql');

//サーバー設定
var dbserver = "localhost";
var dbname = "dbname";
var port = 3306;

// expressフレームワーク
const express = require('express');
const app = express();
const bodyParser = require('body-parser');

//bodyParserを初期化
// urlencodedとjsonは別々に初期化する
app.use(bodyParser.urlencoded({
    extended: true
}));
app.use(bodyParser.json());

//3200番ポートでリクエスト待機
app.listen(3200, () => console.log('Listening on port 3200'))

• Cryptoモジュール読み込み時のkeyおよびiveについては、Access側で使ってるものと同じものを指定しています。
• keyは文字通り暗号化する為のキー。iveは暗号化の際に用いられる初期化ベクトルと呼ばれるもの。鍵と同じ文字数でランダムなものを使います。
• 今回はクライアント側からDB接続用パスワードはAES128bitで暗号化された状態で送っているので、Node.js側でデコードしないと、DB接続ができません。その為にこのモジュールを利用しています。
• Web API定義をする為にexpressを読み込み、別途body-parserも読み込みます（express4からの仕様）
• body-parserの初期化もセットで行う。初期化をしておかないと、Access側からのリクエストを取得する事ができなくなります（POST通信の場合）。
• 今回はPort:3200にて待ち受けするようにしました。デーモン化する為にforeverを使うと尚良いでしょう。

MySQL接続パート

今回通常のリクエスト（SELECT文など）の場合にはGET通信とし、レコードの追加リクエスト（INSERT）などの場合にはPOST通信として受けるようにいくつかのエンドポイントを用意しました。エンドポイントに対してAccess側から様々な引数を渡して、Node.js側で受け取ってあげる事でエンドポイント毎に違う処理を実装し、Web APIのような作りを実現しています。

GET通信

//テスト接続して対象ユーザのレコードを返す
app.get('/api/v1/userinfo', (req, res) =>{
  //リクエストパラメータを取得する
  var para = req.query;
  var retman = {};

  //パスワード復号化
  var pass = "";
  var temppw = para.pw;

  var decipher = crypto.createDecipheriv('aes-128-cbc', key, ive);
  var decoded = decipher.update(temppw, 'base64', 'utf8');
  decoded += decipher.final('utf8');
  pass = decoded;

  //ユーザIDを取得する
  var userid = para.uid;

  var connection;
  var result = ""

  //パスワードを取得する
  mysql.createConnection({
      host: dbserver,
      port: port,
      user: userid,
      password: pass,
      database: dbname
  }).then(function(conn){
      //レコード用変数
      var rlength = 0;    //レコードの数

      //クエリの実行
      connection = conn;
      var result = connection.query("SELECT * FROM mhr.userid where userid = '" + userid + "';", function (err, rows, fields) {
        //エラーが発生した場
        if (err) {
          console.log("接続エラー");
          retman.status = "ERR";
          retman.error = error;
          connection.end();
          //取得レコードを返す
          res.json(retman);
          return;
        }

        //レコードデータを格納する
        rlength = rows.length;

        //取得データを返す
        retman.status = "OK";
        retman.count = rlength;
        retman.recman = rows;
        console.log("OK");
        connection.end();
        //取得レコードを返す
        res.json(retman);
        return;
      });
  }).catch(function(error){
      if (connection && connection.end) connection.end();
      //logs out the error
      retman.status = "ERR";
      retman.error = "接続エラーですよ。パスワードが違うとかサーバアドレス間違ってるとか、ありませんか？";
      //取得レコードを返す
      res.json(retman);
      return;
  });
});

• /api/v1/userinfoとしてエンドポイントを設定しています。これに文字列を繋げてGET通信として受信します。
• 引数は、queryとして受け取れ自動的にJSON形式になって取得が可能です。
• passwordはAES128bitで暗号後、Base64でエンコードされた形で送られてくるので、Cryptoの復号化する時のパラメータとしてBase64を指定しています。
• uidの値でフィルタしたデータをSELECT文で取得し、クライアント側に返しています。
• get通信でアクセスURLを組み立ててブラウザで見ると、JSON形式で対象のレコードが返ってくるのを確認する事が可能です。
• 通常、パスワードなどのセンシティブな情報を送る場合には、GETではなくPOSTで通信するのが普通です。
• GETのURLは、http://10.0.0.1/api/v1/userinfo?uid=12345&pw=abudarakataburaといった組み立てになります。

POST通信

//POST通信で申請データを処理する
app.post('/api/v1/insertman', (req, res) =>{
  //リクエストパラメータを取得する
  var para = req.body;
  var retman = {};
  var userdata = "";

  //パスワード復号化
  var pass = "";
  var temppw = para.pass;

  var decipher = crypto.createDecipheriv('aes-128-cbc', key, ive);
  var decoded = decipher.update(temppw, 'base64', 'utf8');
  decoded += decipher.final('utf8');
  pass = decoded;

  //ユーザIDを取得する
  var userid = para.uid;

  //レコード情報を取得する
  var records = para.recman

  //useridテーブルから登録データを取得する（callback）
  uidgetsql([userid,pass],function (ret){
    //ユーザ情報を取得する
    var json = ret;

    switch(json.status){
      case "ERR":
        //エラーが発生した場合の処理
        res.json(ret);
        break;
      case "OK":
        //インサートデータを組み立てて追加する
        //クライアント側送信データの受信
        var values = records;
        var usrinfo = json.recman;
        console.log(usrinfo);
        insdatasql([userid,pass,values,usrinfo],function (ret){
          //追加結果とレコードIDを取得する
          var respon = ret;

          switch(respon.status){
            case "ERR":
              //エラーが発生した場合の処理
              res.json(respon);
              break;
            case "OK":
              //新レコードのIDをもとにレコードデータを取得し返す
              var newid = respon.recid;

              //指定IDのレコードデータを取得する
              seledatasql([userid,pass,newid],function (ret){
                var respon = ret;
                switch(respon.status){
                  case "ERR":
                    //エラーが発生した場合の処理
                    res.json(respon);
                    break;
                  case "OK":
                    res.json(respon);
                    break;
                }
                return;
              });
          }
        });
        break;
    }
  });
});

//指定の社員番号に基づく登録データを取得して返す
function uidgetsql(args,callback){
  //リクエストパラメータを取り出す
  var uid = args[0];
  var pass = args[1];

  var connection;
  var retman = {};
  var result = ""

  //MySQLに接続してデータを取得する
  //createConnectionでは接続が時々切れる
  mysql.createConnection({
      host: "localhost",
      port: 3306,
      user: uid,
      password: pass,
      database: "mhr"
  }).then(function(conn){
      //レコード用変数
      var rlength = 0;    //レコードの数
      var record = ""     //レコードデータを格納する

      //クエリの実行
      connection = conn;
      var result = connection.query("SELECT * FROM userid where userid = '" + uid + "';", function (err, rows, fields) {
        //エラーが発生した場合
        if (err) {
          console.log("接続エラー");
          retman.status = "ERR";
          retman.error = error;
          callback(retman);
          connection.end();
          return;
        }

        //レコードデータを格納する
        rlength = rows.length;

        //取得データを返す
        retman.status = "OK";
        retman.count = rlength;
        retman.recman = rows;
        console.log("OK");
        callback(retman);
        connection.end();
        return;
      });
  }).catch(function(error){
      if (connection && connection.end) connection.end();
      //logs out the error
      retman.status = "ER/R";
      retman.error = "接続エラーですよ。パスワードが違うとかサーバアドレス間違ってるとか、ありませんか？";
      callback(retman);
      return;
  });
}

//申請データをInsertし、IDを返す関数
function insdatasql(args,callback){
  //リクエストパラメータを取り出す
  var uid = args[0];
  var pass = args[1];
  var array = args[2];
  var usrinfo = args[3];
  var connection;
  var retman = {};
  var result = ""
  var recid = ""

  //MySQLに接続してデータを取得する
  //createConnectionでは接続が時々切れる
  mysql.createConnection({
      host: "localhost",
      port: 3306,
      user: uid,
      password: pass,
      database: "mhr"
  }).then(function(conn){
      //seat確保済みかどうかのチェック
      connection = conn;

      //インサート内容を配列で用意
      var param = [new Date(),uid,usrinfo[0].syozoku,usrinfo[0].fullname,array.kenshuname,array.orgname,array.lecturer,
                  array.schedule,String(array.starttime),String(array.endtime),array.cost,array.leader_mail,
                  usrinfo[0].mail,array.status,array.endflg,array.remarks];

      //インサート実行
      var result = connection.query("insert into kenshu (sinseiday,emp_id,syozoku,name,kenshuname,orgname,lecturer,"
                              + "schedule,starttime,endtime,cost,leader_mail,sender_mail,status,endflg,remarks) "
                              + "values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);",
      param,
      (err, results) => {
        //エラーが発生した場合
        if (err) {
          console.log("Insert実行エラー");
          retman.status = "ERR";
          retman.error = err.sqlMessage;
          console.log(err);
          callback(retman);
          connection.end();
          return;
        }

        //インサートしたレコードのIDを取得して次に渡す
        recid = results.insertId;

        //取得データを返す
        retman.status = "OK";
        retman.recid = recid;
        console.log("OK");
        callback(retman);
        connection.end();
        return;
      });
  }).catch(function(error){
      if (connection && connection.end) connection.end();
      //logs out the error
      retman.status = "ERR";
      retman.msg = "接続エラーですよ。パスワードが違うとかサーバアドレス間違ってるとか、ありませんか？";
      callback(retman);
      return;
  });
}

//指定IDのレコードデータを取得して返す
function seledatasql(args,callback){
  //リクエストパラメータを取り出す
  var uid = args[0];
  var pass = args[1];
  var rid = args[2];
  var connection;
  var retman = {};
  var result = ""
  var recid = ""

  //MySQLに接続してデータを取得する
  //createConnectionでは接続が時々切れる
  mysql.createConnection({
      host: "localhost",
      port: 3306,
      user: uid,
      password: pass,
      database: "mhr"
  }).then(function(conn){
      //レコード用変数
      var rlength = 0;    //レコードの数
      var record = ""     //レコードデータを格納する

      //クエリの実行
      connection = conn;
      var result = connection.query("SELECT * FROM kenshu where ID = " + rid + ";", function (err, rows, fields) {
        //エラーが発生した場合
        if (err) {
          console.log("接続エラー");
          retman.status = "ERR";
          retman.error = error;
          callback(retman);
          connection.end();
          return;
        }

        //レコードデータを格納する
        rlength = rows.length;

        //取得データを返す
        retman.status = "OK";
        retman.count = rlength;
        retman.recman = rows;
        console.log("OK");
        callback(retman);
        connection.end();
        return;
      });
  }).catch(function(error){
      if (connection && connection.end) connection.end();
      //logs out the error
      retman.status = "ERR";
      retman.error = "接続エラーですよ。パスワードが違うとかサーバアドレス間違ってるとか、ありませんか？";
      callback(retman);
      return;
  });
}

• /api/v1/insertmanとしてエンドポイントを設定し、JSON形式でPOST通信で送られてきたデータを引数から受け取ります。
• クライアント側からのデータを送信し、Insert文等を発行するPOST通信で受け取るエンドポイントです。
• POST通信なのでbody-parserを介して、URLデコードされた形でリクエストパラメータが取得できるようになります。
• passwordはAES128bitで暗号後、Base64でエンコードされた形で送られてくるので、Cryptoの復号化する時のパラメータとしてBase64を指定しています。
• クライアント側から送る情報は最小限なので、社員IDをもとにuseridテーブルから追加の情報を取得しレコードを追加する仕組みにしています（ユーザに毎回同じような情報を入れてもらう必要がなくなるため） - uidgetsql関数が担当。
• その後、insdatasql関数にて、Insertするレコードを生成して、実行。その後、resultからinsertIdとして「オートインクリメントで発行されたレコードのID」を取得して返しています。
• 最後に返されたIDをもってseledatasql関数を持ってして追加されたレコードを取得し、Access側へとres.jsonにてデータの塊を返しています。

クライアント側（Access）

サーバ設定登録画面

今回のプログラムはサーバ設定を保存するダイアログにて、パスワードをAES128bitで暗号化するとともに、レジストリに保存するようにしています。データ送信時は暗号化したまま、パスワードパラメータを送って、Node.js側で復号化する仕組みです。ここでは、データ保存時に暗号化しレジストリに保存する所までを記載しています。

パスワードのテキストボックスのみ定型入力プロパティは「パスワード」として設定しているので、*で表示するようにしています。

Private Sub Form_Load()
    On Error Resume Next
    'レジストリからIDとパスワードを読み取る
    Dim regid As Variant
    Dim regpass As String
    Dim svaddress As String
    
    regid = RegGetValue(HKEY_CURRENT_USER, _
            "Software\mysql" & _
            "\Settings", _
            "userid", _
            REG_SZ, _
            0)
    
    regpass = RegGetValue(HKEY_CURRENT_USER, _
            "Software\mysql" & _
            "\Settings", _
            "uid", _
            REG_SZ, _
            0)
    
    svaddress = RegGetValue(HKEY_CURRENT_USER, _
            "Software\mysql" & _
            "\Settings", _
            "server", _
            REG_SZ, _
            0)
            
    Me.loginid.value = regid
    Me.loginpass.value = aes128decode(regpass)
    Me.svaddress.value = svaddress

End Sub

'サーバーセッティングを保存する
Private Sub コマンド5_Click()
    'テキストボックスの値を取得
    Dim regid As Variant
    Dim regpass As String
    Dim svaddress As String
    Dim glflg As Integer
    Dim lRet As Variant
    
    regid = Me.loginid.value
    regpass = Me.loginpass.value
    svaddress = Me.svaddress.value
    
    lRet = RegSetValue(HKEY_CURRENT_USER, _
           "Software\mysql" & _
           "\Settings", _
           "userid", _
           REG_SZ, _
           regid)

    lRet = RegSetValue(HKEY_CURRENT_USER, _
           "Software\mysql" & _
           "\Settings", _
           "uid", _
           REG_SZ, _
           aes128encode(regpass))

    lRet = RegSetValue(HKEY_CURRENT_USER, _
           "Software\mysql" & _
           "\Settings", _
           "server", _
           REG_SZ, _
           svaddress)
    
    '完了報告
    MsgBox "設定を保存しました"
    
    'フォームを閉じる
    DoCmd.Close acForm, Me.Name

End Sub

'暗号化ルーチン
Public Function aes128encode(targetstr As String) As String
    Dim key() As Byte
    Dim iv() As Byte
    Dim data() As Byte
    Dim objCipher As Cipher

    key = StringUtility.stringToByte(enckey)
    iv = StringUtility.stringToByte(enciv)
    data = StringUtility.stringToByte(targetstr)
    
    On Error GoTo ErrorHandler
    Set objCipher = New Cipher
    
    Call objCipher.encrypt(key, iv, data)
    aes128encode = base64.encode(data)
    
    Exit Function

ErrorHandler:
    Dim message As String

    message = "エラーコード: &H" & Hex(Err.Number) & vbCrLf & _
        "ソース: " & Err.Source & vbCrLf & Err.Description
        MsgBox message, vbCritical

End Function

'複合化ルーチン
Public Function aes128decode(targetstr As String) As String
    Dim key() As Byte
    Dim iv() As Byte
    Dim data() As Byte
    Dim objCipher As Cipher
    
    key = StringUtility.stringToByte(enckey)
    iv = StringUtility.stringToByte(enciv)
    data = base64.decode(targetstr)

    On Error GoTo ErrorHandler
    Set objCipher = New Cipher
    Call objCipher.decrypt(key, iv, data)
    
    aes128decode = StringUtility.byteToString(data)

    Exit Function

ErrorHandler:
    Dim message As String

    message = "エラーコード: &H" & Hex(Err.Number) & vbCrLf & _
        "ソース: " & Err.Source & vbCrLf & Err.Description
        MsgBox message, vbCritical

End Function

• aes128encodeおよびaes128decodeの２つは、今回利用させていただいたライブラリを使って暗号化、復号化する関数です。
• Form_loadにて暗号化パスワードは復号化されてボックスに入れています。
• 保存時にパスワードを暗号化してレジストリに保存しています。
• サーバアドレス、ID、パスワードの３つをレジストリにエントリーとして追加しています。

図：こんな感じで表示、登録できるダイアログです

データ送信側設定

ユーザ側からは、例えばワークフローシステムのように何かの申請を送ったり、現在の登録データの取得リクエストなどを送りますが、なるべく個人データはサーバ側に登録してあるデータを流用し、ユーザの入力負担を減らすように作っています。

Option Compare Database

'プロキシー設定
Public Const proxyuri As String = "ここにプロキシーのアドレスを入力"

'エンドポイント
Public Const insposturl As String = "/api/v1/insertman"   'POST通信で申請データをJSON形式で追加するエンドポイント
Public Const userinfo As String = "/api/v1/userinfo?"     'テスト接続してglflgを取得するエンドポイント

'接続テストを実行してglflgを取得して返す（GET通信）
Public Function testconn() As Integer
    'レジストリから送りつけるデータを取得する
    Dim pass As String
    Dim userid As String
    Dim svaddress As String
    
    userid = RegGetValue(HKEY_CURRENT_USER, _
            "Software\mysql" & _
            "\Settings", _
            "userid", _
            REG_SZ, _
            0)
    
    pass = RegGetValue(HKEY_CURRENT_USER, _
            "Software\mysql" & _
            "\Settings", _
            "uid", _
            REG_SZ, _
            0)
    
    svaddress = RegGetValue(HKEY_CURRENT_USER, _
            "Software\mysql" & _
            "\Settings", _
            "server", _
            REG_SZ, _
            0)

    
    'リクエストURLをつなげる
    Dim requrl As Variant
    requrl = "http://" & svaddress & ":3200" & userinfo & "pw=" & pass & "&uid=" & userid
    Debug.Print requrl
 
    'リクエストを送信
    With CreateObject("WinHttp.WinHttpRequest.5.1")
        .Open "GET", requrl, False
        .setProxy 2, proxyuri
        .setRequestHeader "Content-Type", "application/json"
        .send
        
        If .status = 200 Then
            '無事にリクエスト処理が完了した場合の処理
            'JSONをパースする用の変数
            Dim doc, jsn
            Dim Json As String
            Dim ret As Variant
            Dim reccnt As Variant
            Dim tempjson
            
            'HTMLDocumentを取得
            Set doc = CreateObject("HtmlFile")
            'scriptタグを追加
            doc.Write "<script>document.JsonParse=function (s) {return eval('(' + s + ')');}</script>"
            
            'JSONデータを取得する
            Json = .ResponseText
    
            'パース関数でJSONオブジェクトを取得
            Set jsn = doc.JsonParse(Json)
            Dim execstatus As String
            Dim errormsg As String
            execstatus = CallByName(jsn, "status", VbGet)
            
            If execstatus = "OK" Then
                'レコード件数を取得する
                reccnt = CallByName(jsn, "count", VbGet)

                Dim i As Integer
                Dim glflg As Integer
                
                'レコードデータを取り込む
                For i = 0 To reccnt - 1
                    '配列データを取得する
                    Set tempjson = CallByName(jsn.recman, i, VbGet)
                    
                    'glflgを取得する
                    glflg = CallByName(tempjson, "glflg", VbGet)

                Next i
        
                '終了処理
                testconn = glflg
            
            Else
                'エラーだった場合の処理
                errormsg = CallByName(jsn, "error", VbGet)
                MsgBox errormsg
                Exit Function
            End If
        Else
            'エラーが返って来た場合の処理
            MsgBox .status & "エラーです"
            Exit Function
        End If
    
    End With

End Function


'MySQLへ申請データを送り付ける関数（POST通信）
Public Function postKenData(ByRef arr() As Variant)

    'サーバー設定
    Dim pass As String
    Dim userid As String
    Dim svaddress As String
    Dim result As Variant
    
    'レジストリ登録データを取得する
    userid = RegGetValue(HKEY_CURRENT_USER, _
            "Software\mysql" & _
            "\Settings", _
            "userid", _
            REG_SZ, _
            0)
    
    pass = RegGetValue(HKEY_CURRENT_USER, _
            "Software\mysql" & _
            "\Settings", _
            "uid", _
            REG_SZ, _
            0)
    
    svaddress = RegGetValue(HKEY_CURRENT_USER, _
            "Software\mysql" & _
            "\Settings", _
            "server", _
            REG_SZ, _
            0)
    
    'リクエストURLを作成する
    Dim requrl As Variant
    requrl = "http://" & svaddress & ":3200" & insposturl
    
    '送り付けるデータをJSON形式でくみ上げる
    Dim dat As Variant
    Dim param As Variant
    
    'JSONデータを作成する
    Dim JsonObject As Object
    Set JsonObject = New Dictionary
    
    '接続情報を追加する
    JsonObject.Add "uid", userid
    JsonObject.Add "pass", pass
    
    'JsonObject.Add "friend_ids", New Collection
    'JsonObject("friend_ids").Add 10
    'JsonObject("friend_ids").Add 20
    'JsonObject("friend_ids").Add 30
    
    'レコード情報を追加する
    JsonObject.Add "recman", New Dictionary
    JsonObject("recman").Add "sinseiday", Date
    JsonObject("recman").Add "emp_id", userid
    JsonObject("recman").Add "kenshuname", arr(0)
    JsonObject("recman").Add "orgname", arr(1)
    JsonObject("recman").Add "lecturer", arr(2)
    JsonObject("recman").Add "schedule", arr(3)
    JsonObject("recman").Add "starttime", arr(4)
    JsonObject("recman").Add "endtime", arr(5)
    JsonObject("recman").Add "cost", arr(6)
    JsonObject("recman").Add "leader_mail", arr(7)
    JsonObject("recman").Add "status", "未承認"
    JsonObject("recman").Add "endflg", 0
    JsonObject("recman").Add "remarks", arr(8)
    
    'JSONデータに変換する
    dat = JsonConverter.ConvertToJson(JsonObject)
    
    'リクエストを送信
    With CreateObject("WinHttp.WinHttpRequest.5.1")
        .Open "POST", requrl, False
        .setProxy 2, proxyuri
        .setRequestHeader "Content-Type", "application/json; charset=UTF-8"
        .send dat
        
        If .status = 200 Then
            '無事にリクエスト処理が完了した場合の処理recman
            'JSONをパースする用の変数
            Dim doc, jsn
            Dim Json As String
            Dim ret As Variant
            Dim reccnt As Variant
            Dim tempjson
            
            'HTMLDocumentを取得
            Set doc = CreateObject("HtmlFile")
            'scriptタグを追加
            doc.Write "<script>document.JsonParse=function (s) {return eval('(' + s + ')');}</script>"
            
            'JSONデータを取得する
            Json = .ResponseText
    
            'パース関数でJSONオブジェクトを取得
            Set jsn = doc.JsonParse(Json)
            
            'ステータスに応じて処理を分岐
            Dim execstatus As String
            Dim errormsg As String
            execstatus = CallByName(jsn, "status", VbGet)
            
            If execstatus = "OK" Then
            
                '配列データを取得する
                Set tempjson = CallByName(jsn.recman, 0, VbGet)
                
                '取得データを送信済みテーブルに追加する
                'DAO関係の変数の宣言と初期化
                Dim SQL As String
                Dim db As dao.Database
                Dim rs As dao.Recordset
                Dim tempday As Variant
                Dim temp As Variant
                
                Set db = CurrentDb()
                Set rs = db.OpenRecordset("送信済み", dbOpenDynaset)
                
                On Error Resume Next
                With rs
                    .AddNew
                        !申請ID = CallByName(tempjson, "ID", VbGet)
                        !申請日 = Format(CDate(tempday), "yyyy/mm/dd")
                        !社員番号 = CallByName(tempjson, "emp_id", VbGet)
                        !所属 = CallByName(tempjson, "syozoku", VbGet)
                        !名前 = CallByName(tempjson, "name", VbGet)
                        !研修名 = CallByName(tempjson, "kenshuname", VbGet)
                        !教育団体名 = CallByName(tempjson, "orgname", VbGet)
                        !講師名 = CallByName(tempjson, "lecturer", VbGet)
                        !日程 = CallByName(tempjson, "schedule", VbGet)
                        !開始時間 = CStr(CallByName(tempjson, "starttime", VbGet))
                        !終了時間 = CStr(CallByName(tempjson, "endtime", VbGet))
                        !費用 = CallByName(tempjson, "cost", VbGet)
                        !上長メアド = CallByName(tempjson, "leader_mail", VbGet)
                        !申請者メアド = CallByName(tempjson, "sender_mail", VbGet)
                        !ステータス = CallByName(tempjson, "status", VbGet)
                        !終了済み = CallByName(tempjson, "endflg", VbGet)
                        !備考欄 = CallByName(tempjson, "remarks", VbGet)
                        !コメント = CallByName(tempjson, "comment", VbGet)
                    .Update
                End With

                'クローズ処理
                MsgBox "申請と通知が完了しました。"
                rs.Close
                Set db = Nothing
                Set rs = Nothing
            
            Else
                'エラーだった場合の処理
                errormsg = CallByName(jsn, "error", VbGet)
                MsgBox errormsg
                Exit Function
            End If
            
        Else
            'エラーが返って来た場合の処理
            MsgBox .status & "エラーです"
            Exit Function
        End If
    
    End With

End Function

• Generalプロシージャにて、Node.js側で用意したエンドポイントを定数で宣言追加しています。実際のURLはサーバアドレスやパラメータを繋げて動的にコード内で組んでいます。
• Node.js側から送られてくるデータはJSON形式であるため、CallByName関数でパース処理をしています。
• 会社利用を想定しているため、WinHttpオブジェクトにプロキシ利用の設定を入れてあります。
• GET通信の場合、データはURLにつなげて送り込むので、URL組み立て時につなげています。
• POST通信の場合、JSON形式でNode.js側に送る必要があるので、Dictionaryオブジェクトを利用してくみ上げ、VBA-JSONのConvertToJsonにて変換し、送り付けています。
• WinHttpで送信時にURLエンコードはなされているので、個別データを別途URLエンコードする処理は加えていません。

複数レコードを一度に送る

前項では1レコードをJSON化して送っていますが、複数レコードをまとめて送りたい時もあります。この時のCollectionとDictionaryの使い方次第で、連想配列を組み上げて送る事が可能です。

Public Function memberupload()
    'データ取得問い合わせ
    Dim result As Variant
    
    result = MsgBox("サーバに利用者一覧データを登録します。サーバ側データは入れ替わりますよ。。", vbYesNo + vbDefaultButton2 + vbExclamation)
    If result = vbYes Then
    Else
        MsgBox "キャンセルされましたとさ。"
        Exit Function
    End If
    
    '変数と定数の宣言
    Dim JsonObject As Object
    Dim JsonItem As Object
    Set JsonObject = New Dictionary
    JsonObject.Add "shipTo", New Collection

    For i = 1 To 3
        Set JsonItem = New Dictionary

        JsonItem.Add "name", "111"
        JsonItem.Add "address", "222"
        JsonItem.Add "zip", "333"

        JsonObject("shipTo").Add JsonItem
    Next

    'JSON変換する
    dat = JsonConverter.ConvertToJson(JsonObject, Whitespace:=2)
 
        
    'サーバアドレスを取得する
    Dim svaddress As String
    svaddress = DLookup("値2", "setting", "ID=10") & ":1259"

    'リクエストURLをつなげる
    Dim requrl As Variant
    requrl = "http://" & svaddress & "/api/v1/userlist"

    'リクエストを送信
    Dim errormsg As String
    With CreateObject("WinHttp.WinHttpRequest.5.1")
        .Open "POST", requrl, False
        .setProxy 2, proxyuri
        .SetRequestHeader "Content-Type", "application/json; charset=UTF-8"
        .Send dat
        
        Debug.Print .Status
        
        'ステータスが200ならば処理を続行
        If .Status = 200 Then
        
        
        End If
    End With
End Function

この時のJSONデータの構築結果は以下の通り。

{
  "shipTo": [
    {
      "name": "111",
      "address": "222",
      "zip": "333"
    },
    {
      "name": "111",
      "address": "222",
      "zip": "333"
    }
  ]
}

Node.js側で、req.body.shipToで値を取り出せば、連想配列データとして取得する事が可能です。以下のようなコードをAPIとして用意しておきます。

//POST通信で利用者一覧データをバルクインサートする
app.post('/api/v1/userlist', (req, res) =>{
  //リクエストパラメータを取得する
  var para = req.body.shipTo;
  console.log(para[0]);
});

関連リンク

• Expressを用いた簡単なAPIの作り方 まとめ
• Nodeのcryptoモジュールで AES algorithmの暗号・復号
• Excel VBA 暗号化と復号化（TripleDES）
• PHPとNode.jsの暗号/復号相互変換
• Node.jsで暗号化とハッシュ
• node.jsでAES256のCBCブロックモード暗号化を行う
• Node.js MySQLへのバルクインサートと、AUTO_INCREMENTで振られた値
• 古いMySQL ODBC Connector Driver のダウンロード