～ kiku tips ～　テーブルリンクあれこれ

スポンサーサイト

上記の広告は１ヶ月以上更新のないブログに表示されています。
新しい記事を書く事で広告が消せます。

--/--/--

TB： -- / CM： --

テーブルリンクあれこれ

忘れないうちに覚書という事で･･･

リンクテーブルであるかの判別は

　　Dim db As DAO.Database
　　Dim tdf As DAO.TableDef

　　Set db = CurrentDb
　　For Each tdf In db.TableDefs
　　　　With tdf
　　　　　　If (Len(.Connect) > 0) Then
　　　　　　　　' リンクテーブルだよ

とか

　　Dim db As DAO.Database
　　Dim tdf As DAO.TableDef

　　Set db = CurrentDb
　　For Each tdf In db.TableDefs
　　　　With tdf
　　　　　　If (.Attributes = DB_ATTACHEDTABLE) Then
　　　　　　　　' リンクテーブルだよ

とか･･･

判別は良いんだけど、リンクを新規に作る、パスを張り替える･･･
対Excel へは･･･、対CSVファイルには･･･

追加として、
SQL内で外部の accdb / Excel / CSV から入力・出力するには････

※ 以下、Connect が空文字列かどうかで判別してます。

リンク状況の表示

　　Dim db As DAO.Database
　　Dim tdf As DAO.TableDef

　　Set db = CurrentDb
　　For Each tdf In db.TableDefs
　　　　With tdf
　　　　　　If (Len(.Connect) > 0) Then
　　　　　　　　Debug.Print .Name, Hex(.Attributes), .Connect
　　　　　　End If
　　　　End With
　　Next
　　Set db = Nothing

リンクの新規作成（対Access）

「E:\hoge\aaa.accdb」内のテーブル「TBL_A」を「T_Tmp」としてリンク

　　Dim db As DAO.Database
　　Dim tdf As DAO.TableDef
　　Const sFile As String = ";DATABASE=E:\hoge\aaa.accdb"

　　On Error Resume Next
　　Set db = CurrentDb
　　db.TableDefs.Delete "T_Tmp"
　　Set tdf = db.CreateTableDef("T_Tmp")
　　With tdf
　　　　.Connect = sFile
　　　　.SourceTableName = "TBL_A"
　　End With
　　db.TableDefs.Append tdf
　　db.TableDefs.Refresh

　　Set tdf = Nothing
　　Set db = Nothing
　　RefreshDatabaseWindow

リンクパス変更

「E:\hoge\aaa.accdb」が「E:\hoge\hoge\aaa.accdb」に
（テーブル名は変更なし）

　　Dim db As DAO.Database
　　Dim tdf As DAO.TableDef
　　Const sFile As String = ";DATABASE=E:\hoge\hoge\aaa.accdb"

　　On Error Resume Next
　　Set db = CurrentDb
　　Set tdf = db.TableDefs("T_Tmp")
　　tdf.Connect = sFile
　　tdf.RefreshLink

　　Set tdf = Nothing
　　Set db = Nothing

リンク検索＆パス変更（共通のパスへ）

同じパスを使ったリンクが複数あった場合、一気に書き換える
（パスは１種類の場合）

例えば、「E:\hoge\hoge\aaa.accdb」を「E:\Access\aaa.accdb」へ

　　Dim db As DAO.Database
　　Dim tdf As DAO.TableDef
　　Dim sAry() As String
　　Dim sS As String
　　Dim i As Integer
　　Const sDB As String = "DATABASE="
　　Const sNewPath As String = "E:\Access"

　　sS = ""
　　Set db = CurrentDb
　　For Each tdf In db.TableDefs
　　　　With tdf
　　　　　　If (.Attributes = DB_ATTACHEDTABLE) Then
　　　　　　　　sAry = Split(.Connect, ";")
　　　　　　　　For i = 0 To UBound(sAry)
　　　　　　　　　　If (InStr(sAry(i), sDB) > 0) Then
　　　　　　　　　　　　If (Len(sS) = 0) Then
　　　　　　　　　　　　　　sS = sDB & sNewPath & Mid(sAry(i), InStrRev(sAry(i), "\"))
　　　　　　　　　　　　End If
　　　　　　　　　　　　sAry(i) = sS
　　　　　　　　　　　　Exit For
　　　　　　　　　　End If
　　　　　　　　Next
　　　　　　　　.Connect = Join(sAry, ";")
　　　　　　　　.RefreshLink
　　　　　　End If
　　　　End With
　　Next
　　Set db = Nothing

Attributes を使ってみた

参照テーブルを変更する

参照先は変わらないけど、参照テーブルだけを変えたい。
「SourceTableName」の書き換えだけではできない様なので、新規と同じ

　　Dim db As DAO.Database
　　Dim tdf As DAO.TableDef
　　Const sFile As String = ";DATABASE=E:\hoge\aaa.accdb"

　　On Error Resume Next
　　Set db = CurrentDb
　　db.TableDefs.Delete "T_Tmp"
　　Set tdf = db.CreateTableDef("T_Tmp")
　　With tdf
　　　　.Connect = sFile
　　　　.SourceTableName = "TBL_C"
　　End With
　　db.TableDefs.Append tdf
　　db.TableDefs.Refresh

　　Set tdf = Nothing
　　Set db = Nothing
　　RefreshDatabaseWindow

CSVファイルのリンク新規

「E:\hoge\ts2_8.csv」をリンクする時（ヘッダなし）

　　Dim db As DAO.Database
　　Dim tdf As DAO.TableDef
　　Const sFile As String = "Text;FMT=Delimited;HDR=NO;IMEX=2;DATABASE=E:\hoge"

　　On Error Resume Next
　　Set db = CurrentDb
　　db.TableDefs.Delete "T_Tmp"
　　Set tdf = db.CreateTableDef("T_Tmp")
　　With tdf
　　　　.Connect = sFile
　　　　.SourceTableName = "ts2_8.csv"
　　End With
　　db.TableDefs.Append tdf
　　db.TableDefs.Refresh

　　Set tdf = Nothing
　　Set db = Nothing
　　RefreshDatabaseWindow

ヘッダがない時フィールド名は、F1,F2,F3････

EXCELファイルのリンク新規

「E:\hoge\T2_1.xls」のシート「Sheet1」の「C4:I6」の場合
（見出しあり）

　　Dim db As DAO.Database
　　Dim tdf As DAO.TableDef
　　Const sFile As String = "Excel 8.0;HDR=YES;IMEX=2;DATABASE=E:\hoge\T2_1.xls"

　　On Error Resume Next
　　Set db = CurrentDb
　　db.TableDefs.Delete "T_Tmp"
　　Set tdf = db.CreateTableDef("T_Tmp")
　　With tdf
　　　　.Connect = sFile
　　　　.SourceTableName = "Sheet1$C4:I6"
　　End With
　　db.TableDefs.Append tdf
　　db.TableDefs.Refresh

　　Set tdf = Nothing
　　Set db = Nothing
　　RefreshDatabaseWindow

単にシート「Sheet1」なら、.SourceTableName = "Sheet1$"

※ EXCEL2007ファイルのリンク新規の場合 sFile に以下を指定

"Excel 12.0 Xml;HDR=YES;IMEX=2;DATABASE=E:\hoge\ts2.xlsm"
"Excel 12.0 Xml;HDR=YES;IMEX=2;DATABASE=E:\hoge\T2_1.xlsx"
"Excel 12.0;HDR=YES;IMEX=2;DATABASE=E:\hoge\T2_1.xlsb"

~~EXCELファイルのリンク新規　その２~~
訂正 11/29
確認していた時には動いたと思っていたのですが、Append する際エラーになってました。
実行時エラー '3264'
フィールドが定義されていないので、テーブル定義またはインデックスを追加できません。

　　Dim db As DAO.Database
　　Dim tdf As DAO.TableDef
　　Dim sCn As String

　　sCn = "Excel 8.0;HDR=YES;IMEX=2;DATABASE=E:\hoge\T2_1.xls"

　　On Error Resume Next
　　Set db = CurrentDb
　　db.TableDefs.Delete "T_Tmp"
　　db.TableDefs.Append db.CreateTableDef("T_Tmp", , "Sheet1$C4:I6", sCn)
　　db.TableDefs.Refresh
　　Set db = Nothing
　　RefreshDatabaseWindow

参考にする/しない等、慎重にしてください（私は責任取れません）

　　Set tdf = db.CreateTableDef("T_Tmp", , "Sheet1$C4:I6", sCn)

として作成した tdf 内の Connect / SourceTableName は空でした。
（2003 / 2007とも）2000 では未確認　　夢を見ていたのかも････

テーブルのインポートもどき

テーブルを作成する

　　Dim sSql As String
　　Const sFile As String = "E:\hoge\aaa.accdb"

　　sSql = "SELECT * INTO TBL_A FROM TBL_A IN '" & sFile & "';"
　　CurrentDb.Execute sSql
　　RefreshDatabaseWindow

最初の「TBL_A」は自分、次の「TBL_A」は相手のテーブル

テーブルのインポートもどき　その２

テーブルを作成する

　　Dim sSql As String

　　sSql = ""
　　sSql = sSql & "SELECT * INTO TBL_A FROM ("
　　sSql = sSql & "SELECT * FROM TBL_A IN 'E:\hoge\wk\aaa.accdb'"
　　sSql = sSql & " UNION ALL "
　　sSql = sSql & "SELECT * FROM TBL_A IN 'E:\hoge\wk\bbb.accdb'"
　　sSql = sSql & ");"
　　CurrentDb.Execute sSql
　　RefreshDatabaseWindow

テーブルのインポートもどき　その３

テーブルに追加する

　　Dim sSql As String
　　Const sFile As String = "E:\hoge\aaa.accdb"

　　sSql = "INSERT INTO TBL_A SELECT * FROM TBL_A IN '" & sFile & "';"
　　CurrentDb.Execute sSql

SQL内でのCSVへの出力

「E:\hoge\ts4.csv」にヘッダありで出力する時
（出力先にそのファイルがあるとエラーになります）

SELECT * INTO [ts4.csv] IN '' 'Text;FMT=Delimited;HDR=YES;IMEX=0;DATABASE=E:\hoge'
FROM TBL_A;

と設定すると

SELECT * INTO (Text;FMT=Delimited;HDR=YES;IMEX=0;DATABASE=E:\hoge) ts4.csv
FROM TBL_A;

に置き換わる。その後の保存ではエラーが出て保存できない（元の形にしないと）後述の追記参照のこと
IMEX は 0 でも 2 でも。
IMEX がないと、schema.ini が作成される。内容は以下

[ts4.csv]
ColNameHeader=True
CharacterSet=932
Format=CSVDelimited
Col1=an Integer
Col2=user Char Width 255
Col3=item Char Width 255

出来上がったCSVファイルの中身は以下のような感じ

"an","user","item"
1,"田中","AAAA"
2,"田中","CCCC"
3,"田中","EEEE"
4,"北野","DDDD"
5,"北野","BBBB"

SQL内でのEXCELへの出力

「E:\hoge\ts4.xls」にヘッダありで出力する時

SELECT * INTO TBL_A IN '' 'Excel 8.0;HDR=YES;IMEX=0;DATABASE=E:\hoge\ts4.xls'
FROM TBL_A;

と設定すると

SELECT * INTO (Excel 8.0;HDR=YES;IMEX=0;DATABASE=E:\hoge\ts4.xls) TBL_A
FROM TBL_A;

に置き換わる。その後の保存ではエラーが出て保存できない（元の形にしないと）後述の追記参照のこと

INTO で指定したテーブル名が、そのままシート名になる

追記 11/27

以下の書き方で、Accessさんに書き換えられることはなくなりました。
上側は CSV ファイルの場合、下側は EXCEL ファイルの場合

SELECT * INTO [ts4.csv] IN 'E:\hoge'[Text;FMT=Delimited;HDR=YES;IMEX=0;]
FROM TBL_A;

SELECT * INTO TBL_A IN 'E:\hoge\ts4.xls'[Excel 8.0;HDR=YES;IMEX=0;]
FROM TBL_A;

ちなみに以下のような書き方をやってみると、前述上記に書き換えられました。

SELECT * INTO [ts4.csv] IN 'E:\hoge' 'Text;FMT=Delimited;HDR=YES;IMEX=0;'
FROM TBL_A;

SELECT * INTO TBL_A IN 'E:\hoge\ts4.xls' 'Excel 8.0;HDR=YES;IMEX=0;'
FROM TBL_A;

この状態でも一応動きました。
クエリとして作成した時、保存後、再度SQLビューで見ると変わっているという事で･･･

ありがとうございました。

※ これは覚えておいた方が良い等、アドバイスをお願いします。

余談）

Access2007 で確認していたのですが、Connect の中に "ACCDB=YES;" が付いてました。
削っても動くみたいなので、上記では書いていなかったです。
"ACCDB=YES;" って何者なんでしょう？？

関連記事

2011/11/27

Category: 注意しよ

TB： 0 / CM： 2

top △

« 帳票フォームで帯表示
 プニュンで割付け »

この記事に対するコメント

ちわっ

MukkuMukuです。
中の人にSQLを書き換えられてしまう件については、

SELECT * INTO TBL_A IN 'E:\hoge\ts4.xls'[Excel 8.0;HDR=YES;IMEX=0;]
FROM TBL_A;

って感じでよさそう。

MukkuMuku #2DdjN05. | URL | 2011/11/27 15:53 * edit *

アドバイスありがとうございました。

> SELECT * INTO TBL_A IN 'E:\hoge\ts4.xls'[Excel 8.0;HDR=YES;IMEX=0;]
> FROM TBL_A;

この形式で、Excel/CSV とも書き換えられることはなくなりました。

ここの IN を、IN句っていうんですよね。
普通の IN 演算子も IN句って呼んでました。

この IN句の書き方を１つで覚えておこうとしていて、

IN '' '・・・・・・・' で覚えてました。

IN 'パス'[・・・・・・・] で覚えるようにしたいと思います。

御迷惑でなければ、今後とも、ご指摘・アドバイス等々よろしくお願いいたします。

kiku #XOUJXkNQ | URL | 2011/11/27 17:02 * edit *

top △

コメントの投稿

top △

トラックバック

トラックバックURL
→http://kikutips.blog13.fc2.com/tb.php/107-8c05e037
この記事にトラックバックする(FC2ブログユーザー)

top △

| h o m e |

日	月	火	水	木	金	土
-	1	2	3	4	5	6
7	8	9	10	11	12	13
14	15	16	17	18	19	20
21	22	23	24	25	26	27
28	29	30	31	-	-	-

～ kiku tips ～

Access VBA が使えたら･･･

スポンサーサイト

テーブルリンクあれこれ

この記事に対するコメント

コメントの投稿

トラックバック

ぶつぶつ

カレンダー

全記事表示リンク

最新記事

カテゴリ

カウンター

～ kiku tips ～

Access VBA が使えたら ･･･

スポンサーサイト nw(&#45;&#45;,&#45;&#45;,&#45;&#45;,&#45;&#45;)

テーブルリンクあれこれ nw(2011,11,27,14)

この記事に対するコメント

コメントの投稿

トラックバック

ぶつぶつ

カレンダー

全記事表示リンク

最新記事

カテゴリ

カウンター

　　　　Access VBA が使えたら･･･

スポンサーサイト

テーブルリンクあれこれ