【AccessVBA】テキストファイルのリンクテーブルの更新にてこずりました

14773 ワード
access CSV VBA テキストファイル 初心者 VBA テキストリンク

概要
この投稿の構成をツリーにあらわしました。

  1. はじめに
  2. 基本編：まずは現状把握しよう
  3. 応用編：文法がわかれば自分色に染めてやろう
    1. テキストファイル編（CSV）
    2. ACCDBファイル編

はじめに

先日お客先からデータ集約・加工の要望をいただき、そのために私がとった方法として、Accessに複数のテキストファイルをリンクテーブルで接続しデータを加工する、というものでした。

なんてことない、Google大明神に質問したら一発で解決だと思いきや、これだという方法が全然見つからない。

リンクテーブルを削除して再度リンク設定するやり方も見つけはしたのですが、”更新”にこだわりました。
理由は、クエリを設定していたのと、削除後のリンク再設定時にこけたら復旧に時間がかかるだろうと思い。お客様もツールを利用する可能性があるので、ひょんなことからクエリのリンクが外れてしまい予期せぬエラーが発生する可能性を排除したかったのです。

あっちゃこっちゃいって何とか、そもそものところを理解できて来たのと、素人にちょっと毛が生えたくらいの目線から、今後の備忘録として今回の解決策を記録することにしました。

基本編：まずは現状把握しよう

現状把握

まずは、どういった記述方法かを知る必要があることに意識をシフトしました。
一旦手動でテキストファイルをリンクさせ、下記のコードを走らせ接続文字列を掃き出し、どういう内容なのかを確認しました。

サンプルコード①

AccessVBA
Sub SampleLinkInfoRef()

Dim dbs As Database
Dim dtf As TableDef

'インスタンス生成
Set dbs = CurrentDB
Set dtf = dbs.TableDefs("サンプルテーブル")

'接続情報を確認（デバッグプリント）
Debug.print dtf.TableName
Debug.print dtf.Connect

'後始末
Set dtf = Nothing
Set dbs = Nothing

End Sub

結果、下記の接続文字列ということがわかりました。

イミディエイトウィンドウの表示
'dtf.TableName
"サンプルテーブル"

'dtf.Connect
"TEXT;DSN=;FMT=Delimited;HDR=YES;IMEX=2;ACCDB=YES;DATABASE=パス（ディレクトリパス）"

これさえわかればこっちのもんだ！
ここに到達するのに結構かかりました。
次回はもっと効率的に…精進します😢

記述に際してのメモ

・テキストファイルをリンクする場合、"DATABASE="の後のパスは、ファイルパスではなくフォルダパスを指定しなくてはエラーが発生する
・ACCDBの場合は、ファイルパスを指定しなくてはエラーが発生する
・事前に参照設定で"Microsoft DAO x.x Object Libirary"を有効にしておくことが必要

応用編：文法がわかれば自分色に染めてやろう

1. テキストファイル編（CSV）

ツールの仕様

ここまでわかれば、私が実現したい内容にちゃちゃっと加工すればOK！

ということで、次の仕様で定例処理として実施できるようにしてみた。

【ツールの仕様】
・リンク対象のCSVファイルは計30ファイルある
・あらかじめ、CSVファイルを3つのACCDBファイルに分けてリンクさせている状態にしている
・毎月、CSVのzipファイルが受領され、任意のフォルダに解凍しCSVを配置
・リンク対象のCSVファイル名を、テーブル（"リンク対象CSVリスト"）にあらかじめ設定
・ツールでは、CSVの配置フォルダ（txtFolderPath）を指定することで、テキストファイルのリンクテーブル更新を一括で実行できるようにする

めちゃくちゃ荒い説明で済みませんねぇ…
質問がある方はお気軽にどぞ！

サンプルコード②

テーブル：リンク対象CSVリスト
| 拡張子付きファイル名 |
|--------------------|
| CSVファイル1.csv    |
| CSVファイル2.csv    |
| CSVファイル3.csv    |
| CSVファイル4.csv    |
| CSVファイル5.csv    |
| CSVファイル6.csv    |
| CSVファイル7.csv    |
| CSVファイル8.csv    |
| CSVファイル9.csv    |
| CSVファイル10.csv   |
AccessVBA
Sub SampleLinkInfoUpdate_CSV()

Dim dbs As Database
Dim dtf As TableDef
Dim rs  As Recordset
Dim rcnt As integer  'レコードカウント
Dim i As integer  'イテレーター
Dim pos as Integer  '拡張子の位置情報

Dim TABLE_NAME As String  '拡張子なし
Dim FILE_NAME As String  '拡張子あり
''Dim FILE_PATH As String  '拡張子ありのフルパス

'インスタンス生成
Set dbs = CurrentDB
Set rs = dbs.OpenRecordset("リンク対象CSVリスト",dbOpenTable)

rs.MoveFirst
rcnt = rs.RecordCount

'テーブルのレコードの数（CSVファイルの数）だけループ
For Each i In rcnt

  FILE_NAME = rs!拡張子付きファイル名

  '拡張子を外す
  pos = InStrRev(FILE_NAME , ".")  '拡張子の場所を特定
  TABLE_NAME = Left(FILE_NAME ,pos-1)  '左から拡張子の1つ前までがファイル名

  'インスタンス生成
  Set dtf = dbs.TableDefs(TABLE_NAME)

  'CSVのフォルダパス（txtFolderPath）を接続文字列に指定
  dtf.Connect = "TEXT;DSN=;FMT=Delimited;HDR=YES;IMEX=2;ACCDB=YES;DATABASE=" & txtFolderPath
  '接続を更新する
  dtf.RefreshLink

  '次のレコードへ移動
  rs.MoveNext
Next i

'接続情報を確認（デバッグプリント）
Debug.print dtf.TableName
Debug.print dtf.Connect

'後始末
rs.Close
Set rs = Nothing
Set dtf = Nothing
Set dbs = Nothing

End Sub

拙い書き方のところがあるかと思いますがご容赦ください。

2. ACCDBファイル編

ツールの仕様

テキストファイルのリンクテーブル更新との比較で（より記憶に残せるとおもって）、ACCDBファイルのリンクにも挑戦！

ということで、仮に（！？）次の仕様で定例処理として実施できるようにしてみた。

【ツールの仕様】
・リンク対象のACCDBファイルは、計4ファイルあります
・あらかじめ、マージ用ACCDBファイルに、参照用ACCDBファイルをリンクさせている状態
・毎月、任意のフォルダに4つの参照用ACCDBファイルを配置
・参照用ACCDBファイル名を、テーブル（"リンク対象ACCDBリスト"）にあらかじめ設定
・ツールでは、ACCDBの配置フォルダ（txtFolderPath）を指定することで、リンクテーブル更新を一括で実行できるようにする

サンプルコード③

テーブル：リンク対象ACCDBリスト
|  拡張子付きファイル名  |   テーブル名   |
|----------------------|---------------|
| ACCDBファイル1.accdb  |ACCDBファイル1  |
| ACCDBファイル2.accdb  |ACCDBファイル2  |
| ACCDBファイル3.accdb  |ACCDBファイル3  |
| ACCDBファイル4.accdb  |ACCDBファイル4  |
AccessVBA
Sub SampleLinkInfoUpdate_ACCDB()

Dim dbs As Database
Dim dtf As TableDef
Dim rs  As Recordset
Dim rcnt As integer  'レコードカウント
Dim i As integer  'イテレーター
''Dim pos as Integer  '拡張子の位置情報

Dim TABLE_NAME As String  '拡張子なし
Dim FILE_NAME As String  '拡張子あり
Dim FILE_PATH As String  '拡張子ありのフルパス

'インスタンス生成
Set dbs = CurrentDB
Set rs = dbs.OpenRecordset("リンク対象ACCDBリスト",dbOpenTable)

rs.MoveFirst
rcnt = rs.RecordCount

'テーブルのレコードの数（ACCDBファイルの数）だけループ
For Each i In rcnt
  
  TABLE_NAME = rs!テーブル名
  FILE_NAME = rs!拡張子付きファイル名

  'インスタンス生成
  Set dtf = dbs.TableDefs(TABLE_NAME)

  'ファイルパスを生成
  FILE_PATH = txtFolderPath & "\" & FILE_NAME

  'ACCDBファイルのファイルパス（FILE_PATH）を接続文字列に指定
  dtf.Connect = ";DATABASE=" & FILE_PATH
  '接続を更新する
  dtf.RefreshLink

  '次のレコードへ移動
  rs.MoveNext
Next i

'接続情報を確認（デバッグプリント）
Debug.print dtf.TableName
Debug.print dtf.Connect

'後始末
rs.Close
Set rs = Nothing
Set dtf = Nothing
Set dbs = Nothing

End Sub

こちらも、拙い書き方のところがあるかと思いますがご容赦ください。
しかもちゃんと検証していません。とりあえずメモ！
後日会社の環境で検証してみます。（コラ！）