スポンサーサイト 


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

--/--/--

Category: スポンサー広告

TB: --  /  CM: --

top △

テーブルリンクあれこれ 


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

リンクテーブルであるかの判別は
  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

 

リンク検索&パス変更(共通のパスへ)

同じパスを使ったリンクが複数あった場合、一気に書き換える
(パスは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 を使ってみた
 

参照テーブルを変更する

参照先は変わらないけど、参照テーブルだけを変えたい。
「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ファイルのリンク新規 その2
訂正 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」は相手のテーブル
 

テーブルのインポートもどき その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

 

テーブルのインポートもどき その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

 

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句の書き方を1つで覚えておこうとしていて、

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

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


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

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

top △

コメントの投稿

Secret

top △

トラックバック

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

top △


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