ExcelVBAを利用して、取得したJSONデータをAccessのテーブルにInsertするときにハマったので、メモとして残しておく。実際は、sql発行全般にいえることみたいですが、ハマったのはinsert文だったので以下はinsert文と例となります。
予め結論ですが、Accessでは、[と]で括ることが重要だと勉強になりました。
###各種環境について
- Access2013
- Excel2013
- 参考)利用したデータはLINEWORKSのアドレス帳のデータをAPIで取得したもの
###ハマった部分について
頭に記載している通りInsertの発行時にハマりました。
以下、抜粋ですが、Executeした時に、insert構文エラーと表示されてしまう。試しにinsertのsql文だけDebug.Printで出力されたものをAccessのクエリ画面で実行してみると正しくinsert文が発行される。
'Access接続用オブジェクト
Dim con as Object
Set con = CreateObject("ADODB.Connection")
con.Open = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=Accessファイルのフルパス;"
'insert用のsql文
Dim sql as string
sql = "insert into address" & _
"(resourceId,isOpen,isCoEditing,masterUserId)" & _
"values('123456789',1,0,'xxx@xxx');"
'sqlの実行
con.Execute sql
insert構文エラーの原因について
以下抜粋です。ほぼ↑のソースコードと変わりませんが、一部異なる部分があります。ぱっと見一緒すぎてわかりにくいのですが、insert文の列名の部分に着目します。↑だとresourceId,isOpen,isCoEditing,masterUserIdとなっていますが、↓だと[resourceId],[isOpen],[isCoEditing],[masterUserId]となっています。列名を[と]で括っているか括っていないかの違いのみ。これですんなりinsert文が発行できました……。
'Access接続用オブジェクト
Dim con as Object
Set con = CreateObject("ADODB.Connection")
con.Open = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=Accessファイルのフルパス;"
'insert用のsql文
Dim sql as string
sql = "insert into address" & _
"([resourceId],[isOpen],[isCoEditing],[masterUserId])" & _
"values('123456789',1,0,'xxx@xxx');"
'sqlの実行
con.Execute sql
所感
Accessのクエリ画面で発行すると正常に動作して、ExcelのVBA経由で実行すると上手くいかないという部分でかなりハマってしまいました。AccessとExcel経由でSQL構文の判断基準が違うなんて知らなかった。
Access触った事ほぼ無かったですが、いけるやろうと軽く手を出して、返り討ちにあい、数年後になんとか領地を取り戻した気分でした。
とりあえずドツボにハマらなくて良かった。
とはいえ、時間は溶かしたなあ……。