今日のトリビア PowershellでSQLを使用してMicrosoft Accessのテーブルを作成するときは WITH COMP属性は特定の場合以外エラーになる

# 環境

 - Powershell_ise 5.1
 - Access 32bitくらい
 - Windows10 くらい

# 再現手順
Powershell_iseでこんなコードを実行する。
定数の設定、既存ファイルチェックとかは省略している。

```powershell
 $acApp = New-Object -ComObject Access.Application;
 $acApp.NewCurrentDataBase((Join-Path $sFolder $ac2007),$acNewDatabaseFormatAccess12);
 $acApp.CloseCurrentDataBase(); # いったん閉じる
 $dao = New-Object -ComObject DataEngine.Dao.120
 $ModeNum=2
 IF($ModeNum -eq 1){
 $cDB=$dao.OpenDatabase((Join-Path $sFolder $ac2007))
 $sCNSQL=  "CREATE TABLE T_Masterファイルリスト(ID Counter Primary Key, 作成DT DATE,"  `                                     +  "接続DT DATE, 更新DT DATE," `
          +  "[ディレクトリ] MEMO WITH COMP, フルネーム MEMO WITH COMP," `
          +  "ファイルN Text(255), ベースN Text(255), 拡張子 Text(10));"
$cDB.execute($sCNSQL) ; $cDB.TableDefs.Refresh() ;
$cDB.Close()
}Else{
$aCN2= New-Object -ComObject Adodb.Connection
$Db= Join-Path $sFolder $ac2007
$aCN2.Open("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=$sFolder")
$sCNSQL=  "CREATE TABLE T_Masterファイルリスト(ID Counter Primary Key, 作成DT DATE,"  `                                     +  "接続DT DATE, 更新DT DATE," `
         +  "[ディレクトリ] MEMO WITH COMP, フルネーム MEMO WITH COMP," `
         +  "ファイルN Text(255), ベースN Text(255), 拡張子 Text(10));"
$aCN2.Execute($sCNSQL)
$aCN2.Close()
$aCN2=$null
}
```

# 問題
この前段でUTF-8で作成されたCSVを読み込みます。
そのCSVはサーバーにあるファイルのリストです。
サーバーにある名前は長く色々です。
このため、MEMO型を使います。
` $ModeNum=1`のときはDAOで、それ以外はADODBで接続してテーブルを作ります。
このとき、両方とも問題なくテーブルが作られるでしょうか

# 答え
Daoは失敗する。
ADODBは成功する。


[CREATE TABLE ステートメント (Microsoft Access SQL)](https://learn.microsoft.com/ja-jp/office/client-developer/access/desktop-database-reference/create-table-statement-microsoft-access-sql)
[アーティクル]
2022/08/31
適用先: Access 2013、Office 2013
新しいテーブルを作成します。

:::note warning
 注意
Microsoft Access データベース エンジンは、Microsoft Access データベース エンジン以外のデータベースでは CREATE TABLE 句や DDL (データ定義言語) ステートメントを使用できません。Microsoft Access データベース エンジン以外のデータベースでは代わりに DAO の Create 系メソッドを使用してください。
:::

このような見出しを見ると、DAOを使ったほうが良いようだが、実は古くから知られている。
そしてやっぱり魔界の仮面弁士氏である。

[オートナンバー属性に設定するには？](https://www.petitmonte.com/bbs/answers?question_id=14274)
> はづき  2003-04-02 11:51:56  No: 77518

>連投稿申し訳ありません
先の方法でフィールドの追加ができました
ご教授ありがとうございました

>オートナンバー型に設定ができません
長整数などならDBLONG等でできるようですが
オートナンバーに設定することができません

>フィールド作成時もしくは作成後にオートナンバーに設定する方法
お願いします・・・

>---

>魔界の仮面弁士  2003-04-02 19:10:28  No: 77519
夜遅くまでご苦労様です。
ADOやDAOのオブジェクトモデルを使っても追加できるのですが、
今回はSQL文で指定する方法を回答しますね。
（オブジェクトで追加する方法が知りたい場合は、再質問してください）
(中略)
[新規表にオートナンバー列を作成]

>先と同様、"COUNTER" もしくは "AUTOINCREMENT" で指定します。
例えば、以下のようなSQL文が利用できます。

<blockQuote>

```SQL
CREATE TABLE 従業員 (
   社員番号 AUTOINCREMENT CONSTRAINT PK_社員番号 PRIMARY KEY,
   社員姓 TEXT(50) WITH COMP NOT NULL,
   社員名 TEXT(50) WITH COMPRESSION NOT NULL,
   電話番号 TEXT(10),
   Email TEXT(50),
   住所 TEXT(40) DEFAULT 不明)
```

これは、以下のような[従業員]というテーブルを作成するための物です。

```tex
  社員番号: オートナンバー(初期値=1、増分値=1)
  社員姓  : テキスト型(50文字、Unicode圧縮あり) [値要求あり]
  社員名  : 文字列型(50文字、Unicode圧縮あり) [値要求あり]
  電話番号: 文字列型(10文字、Unicode圧縮なし) [値要求なし]
  Email   : 文字列型(50文字、Unicode圧縮なし) [値要求なし]
  住所    : 文字列型(50文字、Unicode圧縮なし) [値要求なし、初期値=不明]
```
</blockquote>

>また、CONSTRAINT指定により、このテーブルには[社員番号]列に対して
"PK_社員番号" という名前の主キーが設けられています。

><b># なお上記には Microsoft.ACE.OLEDB.12.0~~Jet 4.0~~の <font color=RED>ANSI-92モード</font>でしか利用できないキーワードが
# いくつか含まれていますので(<font color=RED>WITH COMP</Font>やDEFAULT等)、このままですと、
# 「ADO + "Microsoft.ACE.OLEDB.12.0"~~"Microsoft.Jet.OLEDB.4.0"~~」以外から実行する事はできません。
# <font color=RED>DAOから実行したい場合には、WITH COMP等のキーワードを削って、
# ANSI-89モードのSQL構文にしてから試してみてください。</font></b>

## 2022年現在に読み変えると
このJETは今ではあまり使われないのでMicrosoft ACE 12.0に置き換えて読むと、実は今でも通じるということになる。

### クエリではたしかにWITH COMPは使えない
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/126919/9c7e8486-159d-db97-38c6-c198d4879967.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/126919/8948f4bf-22ab-e67e-b3e1-32386be3f6f2.png)

```sql
Create Table T_Master(fld01 MEMO WITH COMP);
```

## VBAでは
### DAO
CurrentDBはDao.Databeseなので、DAOになる。
```vb
Sub test()
Dim cDB As DAO.Database
Set cDB = CurrentDb
Dim Q As QueryDef
cDB.Execute ("Create Table T_Master(fld01 MEMO WITH COMP);")
End Sub
```

これは構文エラーになる

```vb
Sub test2()
Dim cDB As DAO.Database
Set cDB = CurrentDb
Dim Q As QueryDef
cDB.Execute ("Create Table T_Master(fld01 MEMO);")
Application.RefreshDatabaseWindow
End Sub
```

成功する。やはりDAOではWITHが使えない。

```vb
Sub test3()
Dim cDB As DAO.Database
Dim CN As Object: Set CN = CreateObject("ADODB.Connection")
Set cDB = CurrentDb
Dim Q As QueryDef
Set CN = CurrentProject.AccessConnection
CN.Execute "Create Table T_Master(fld01 MEMO WITH COMP);"
Application.RefreshDatabaseWindow
End Sub
```

ADOは成功する。VBAでもWITH　COMPはADOを使ったときだけである。


## それではDAOは役立たないのか
このWITH COMP属性はUTF-8のCSVを読み込ませるときデータ型が違うとしてエラーになる場合がある。
これを解決するにはUTF-8ではなくUNICODE（とMSが言っているUTF-16）でCSVを作るか、WITH COMPを使わないでDAOで作成するかである。
UTF-8のほうが汎用性が高く、DAOを使うほうが基本的に早いため、WITH COMPを使わないDAOにも活躍の余地がある。
さらにDAOは接続文字列を使わないため、コードも短く、簡潔である。さらにアクションクエリでもWITH　COMPは使えない。ご覧のようにWITHでエラーになる。クエリとVBAで同じSQLが使える。つまりSQL文を共用できる。