テーブルリンクあれこれ
忘れないうちに覚書という事で・・・
リンクテーブルであるかの判別は
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 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
' リンクテーブルだよ
とか・・・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
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
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
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
リンク検索&パス変更(共通のパスへ)
同じパスを使ったリンクが複数あった場合、一気に書き換える
(パスは1種類の場合)
例えば、「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 を使ってみた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
参照テーブルを変更する
参照先は変わらないけど、参照テーブルだけを変えたい。
「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
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・・・・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
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$"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
※ 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 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"
訂正 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
参考にする/しない等、慎重にしてください(私は責任取れません)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」は相手のテーブルConst sFile As String = "E:\hoge\aaa.accdb"
sSql = "SELECT * INTO TBL_A FROM TBL_A IN '" & sFile & "';"
CurrentDb.Execute sSql
RefreshDatabaseWindow
テーブルのインポートもどき その2
テーブルを作成する
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
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
テーブルのインポートもどき その3
テーブルに追加する
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
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;
と設定するとFROM TBL_A;
SELECT * INTO (Text;FMT=Delimited;HDR=YES;IMEX=0;DATABASE=E:\hoge) ts4.csv
FROM TBL_A;
に置き換わる。その後の保存ではエラーが出て保存できない(元の形にしないと)後述の追記参照のこと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;
と設定するとFROM TBL_A;
SELECT * INTO (Excel 8.0;HDR=YES;IMEX=0;DATABASE=E:\hoge\ts4.xls) TBL_A
FROM 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;
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;
この状態でも一応動きました。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;" って何者なんでしょう??
- 関連記事
-
- Nz の罠 (2013/05/22)
- テーブルリンクあれこれ (2011/11/27)
- 言い訳 (2012/11/03)
- IIF って三項演算子? (2011/06/11)
- フォームの起動 (2012/06/03)
2011/11/27
Category: 注意しよ
« 帳票フォームで帯表示
プニュンで割付け »
この記事に対するコメント
ちわっ
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句の書き方を1つで覚えておこうとしていて、
IN '' '・・・・・・・' で覚えてました。
IN 'パス'[・・・・・・・] で覚えるようにしたいと思います。
御迷惑でなければ、今後とも、ご指摘・アドバイス等々よろしくお願いいたします。
kiku #XOUJXkNQ | URL | 2011/11/27 17:02 * edit *
トラックバック
| h o m e |