Access 2016でテーブル内容を固定長テキストで出力する

できない……。散々悩んだのですが今日時点までの状況をメモっておきます。

やりたいこと

文字列や数値を含んだテーブルの内容をテキスト形式で出力したい（CSVではなく）
できれば、クエリーやテーブルを指定すると、決まった名前のテキストファイルに出力するような共通機能としてまとめたい。

できなかったこと

DoCmd.TransferTextでacExportFixedを指定してのテキスト出力ができなかった。
- Specificationを指定しろと言われるので、エクスポートウィザードの操作を保存したものを指定しても「定義名が見つからない」でダメだった。
- Schema.ini とやらを指定せよとのことだったので作って指定してみても「Schema.iniが見つからない」でダメだった。
- 定義を指定する機能がインポートウィザードにはあったので、それで定義したインポート定義をacExportFixedで指定してみたけれど、インポートしようとしてだめだった。

できたこと / わかったこと

海外のフォーラムで、ADODBのオブジェクトを使ってSQLから直接ファイルにエクスポートする方法が提案されていたので、試して見たらSchema.iniも使えてエクスポートもできた。
エクスポートウィザードの過程で操作ではなく定義を指定する機能はAccess 2010から消えてしまっているらしいことが判った。

つくってみたもの

テスト用テーブル

簡単なものを用意しました。

f:id:frontline:20191031171006p:plain — サンプルテーブル「test」の内容

f:id:frontline:20191031171043p:plain — サンプルテーブル「test」の定義

テスト用ロジック

これに、ボタンを1つだけ持つフォームを用意し、VBAで下記のコードを実行するようにしました。ロジックはクラスのメソッドとして配置しています。

Public Function ExportBySQL() As Boolean

    Set con = CreateObject("ADODB.Connection")
    
    ' 改めてAccessファイルのDBに接続する
    con.Open "Provider=Microsoft.Ace.OLEDB.12.0;Data Source=C:\Work\DB\TestDB.accdb"
    
    Dim sql As String
    ' CSV形式だとこれ。一度これで出力すると、Schema.iniが自動生成されるので改変すればFixed用の定義ができる
    'sql = "select * into [text;database=c:\work\;FMT=Delimited;HDR=Yes].[csvfile.csv] from test"

    ' 固定長テキストの出力SQL
    sql = "select * into [text;database=c:\work\;FMT=Fixed;HDR=Yes].[sample.txt] from test"
    
    ' エクスポートSQL実行
    con.Execute sql

End Function

Schema.ini

下記のようなものを作りました。作ったと言ってもゼロからではなく、上記の処理で FMT=Fixed の代わりに「Delimited」を指定すると、出力先フォルダに自動的にSchema.iniが生成されたので、それを少し書き換えただけです。

[sample.txt]
ColNameHeader=True
CharacterSet=932
Format=FixedLength
Col1=ID Integer Width 10
Col2=UsereName Char Width 255
Col3=Company Char Width 255

できたこと

下記のようなファイルが出てきました。とりあえず直近の目的は達成。

f:id:frontline:20191031171555p:plain — 生成されたファイル

参考にしたサイト

いろいろ見た結果、ストレートに「Access 2016で定義を作るAdvancedというボタンの所在を検索しよう」と思って見つけたのがこちら。

social.msdn.microsoft.com

ここの下記の文章が決定打でした。

In current design, it seems that we can't create an export-definition with custom filed separators. <

要するに、その機能は無い、と。ただ、職場でもAccessに詳しい人はその方法で出来たといっていたので、Access 2010から機能が消えてしまったのか……。でも、TransferTextでSchema.iniは指定できる、と書いてあったので、その利用法を調べる過程で行き着いたのが下記。ここでSQLによるアプローチを知ります。

stackoverflow.com

サンプルを改変しながら適用し、まずはCSVで出してみて、次は固定長文字列にトライしました。FMT＝によるフォーマット指定方法は下記サイトで確認しました。

www.connectionstrings.com