外部ＭＤＢのリンクテーブルの削除とデータ参照

みなさんのお力を貸してください。
正直、ＶＢＡの知識はありません。

アクセスファイル　①と②があります。
②にはリンクテーブルがあります。
①のフォームボタンを実行しいたら、②のリンクテーブルを貼り直しできるようにしたいのです。
過去の履歴等を参考にしましたが、うまくいきませんでした。
ご指導をお願いします。
※訳があって、①と②は一緒のアクセスファイルにすることはできません。

Option Compare Database
Option Explicit
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Public Function Make_Link_Table()
str = Application.CurrentProject.Path
Set app = CreateObject("Access.application")
app.OpenCurrentDatabase (str & "\a.mdb")

With dbs　　　　　　　　　　　　　　　
For Each tdf In .TableDefs
If tdf.Name = "dbo_Ｔマスタ" Then
.TableDefs.Delete tdf.Name
End If
Next tdf
End With

app.DoCmd.TransferDatabase acLink, "ODBC", "ODBC;DSN=" & DSN_Kangen & ";DATABASE=" & DSN_Kangen, acTable, "Ｔマスタ", "dbo_Ｔマスタ"

app.CloseCurrentDatabase

MsgBox ("リンクテーブル切替処理終了！ ")

End Function

質問者からの補足コメント

• 実行すると、　以下の所でエラーになります。
  これをコメントアウトすると、処理は正常に終了します。
  
  しかし、リンク切替前の「dbo_Ｔマスタ」があるため、新たに作成されたテーブルは「dbo_Ｔマスタ１」という名称で作成されてしまいました。
  ※．リンク先の変更ボタンをクリックする毎に「dbo_Ｔマスタ２」、「dbo_Ｔマスタ３」という感じでテーブルが作成れますので、やはり、以下の記述（既存のリンクテーブルを削除する）が必要と思いました。
  
  With dbs　　　　　　　　　　　　　　　
  For Each tdf In .TableDefs
  If tdf.Name = "dbo_Ｔマスタ" Then
  .TableDefs.Delete tdf.Name
  End If
  Next tdf
  End With

    補足日時：2017/06/07 14:54

No.1 回答者： ShowMeHow 回答日時： 2017/06/06 19:11

貼り直しというのは、①というアクセスファイル上のフォームから、⓶というアクセスファイル上にすでに作られているリンクテーブルのリンク先を変えたいということでしょうか？

そうだとしたら、あまりよいアイディアではないと思うのですが、なぜそのようなことをしたいのか、すこし具体的に説明いただけたら、代替案等を提案できるかもしれません。

この回答へのお礼

ShowMeHowさん、ご返答ありがとうございます♪
宜しくお願いします　!(^^)!

貼り直しは、ShowMeHowさんのとおりです。

ＳＱＬサーバに３種類のデータベースがあります。
この３種類はデータの世代（前月データ、前々月データ、前々々月データ）です。

この３種類のデータソース名は次のとおりです。

データソース名　　　　　補足
　Kangen 　　　 ←　前月データ
　Kangen1　　　←　前々月データ
　Kangen2　　　←　前々々月データ

また、「Kangen 」、「Kangen1」、「Kangen2」に格納されているテーブルですが、
同じ名前のテーブル名称で作成しています。
ちなみにテーブル名称は「dbo_Ｔマスタ」です。

①のアクセスファイルのフォーム画面に「前月」「前々月」「前々々月」の３つのボタンがあります。
②のアクセスファイルに「Kangen」に対するリンクテーブルがあります。
①のアクセスファイルのフォーム「前々月」ボタンを押した場合、②のアクセスファイルのリンクテーブルの参照先が「Kangen1」になるようにしたいです。

どうしても、都合上、①と②のアクセスファイルを別にする必要があります。

以上

お礼日時：2017/06/07 08:28

No.2 回答者： ShowMeHow 回答日時： 2017/06/07 15:28

まあ、テーブルを削除したいなら、

Set dbs = ws.OpenDatabase(str & "\ｘｘｘ.mdb")
などとする必要があると思います。
strには⓶のフルパスｘｘｘは⓶のmdb名称が必要です。


余談ですが、
私がアクセスを触っていたのは、ずいぶん昔のことで、
リンクテーブルの操作系もADOXで作ったものがいくつかありました。
ところが、最近の環境ではうまく動かず、、、
まあ、64bitとか環境に依存するところが多いということでは、
私の環境で作り上げても、他の環境で動くという保証はありませんので、
今回は作るのを断念しました。
まあ、こういうことが多いので「アクセスは、、、」ってなるのかもしれません。
最終的に何がされたいのかにもよりますが、本来はＳＱＬを主体に動くものを作ったほうが、
良いのではないかと思います。　（リンクテーブルの構造をいじるのではなく、
ＳＱＬ内で参照するテーブルを変更するほうが、変更等に対応しやすいのかなと思います。）

この回答へのお礼

ShowMeHowさん、ご返答ありがとうございます。
ShowMeHowさんに教えて頂いた構文を追加しましたが、
変数が定義されていませんのメッセージが出力されました。
dbsの変数の定義の方法が悪いのでしょうか？
また、Set dbs = ws.OpenDatabase(str & "\ｘｘｘ.mdb")の追加した位置に問題ありませんか？

Option Compare Database
Option Explicit

Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim DSN_Name As String
Dim DSN_Monthly As String
Dim DSN_Kakushu As String
Dim DSN_Kangen As String


Option Compare Database
Option Explicit
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Public Function Make_Link_Table()
str = Application.CurrentProject.Path
Set app = CreateObject("Access.application")
app.OpenCurrentDatabase (str & "\a.mdb")

Set dbs = ws.OpenDatabase(str & "\a.mdb")
↑
　　　　　　　　追加


With dbs　　　　　　　　　　　　　　　
For Each tdf In .TableDefs
If tdf.Name = "dbo_Ｔマスタ" Then
.TableDefs.Delete tdf.Name
End If
Next tdf
End With

app.DoCmd.TransferDatabase acLink, "ODBC", "ODBC;DSN=" & DSN_Kangen & ";DATABASE=" & DSN_Kangen, acTable, "Ｔマスタ", "dbo_Ｔマスタ"

お礼日時：2017/06/08 10:17

No.3 回答者： ShowMeHow 回答日時： 2017/06/08 10:49

すみません、dao使ったことないので、よくわかっていませんが、

WSがいらなかったみたいです。

私の環境(ACC2007)では、

Dim dbs As DAO.Database

Set dbs = OpenDatabase("C:\....\XXX.mdb")
dbs.Execute "DROP TABLE dbo_Ｔマスタ;"

のような形で、リンクテーブルの削除ができました。
個人的には、ＳＱＬで処理するのが好きなのでそのようにやっていますが、、、
他の方法でも可能だと思います。

この回答へのお礼

できました。
ShowMeHowさん、ありがとうございます♡

もう１つだけ教えてください。

リンクテーブルを切替えた後、切替えてリンクテーブルのデータ基準日を出力したいです。
以下の構文を、今回の構文に追加したのですが、うまくいきません。
ご指導お願いします♪

sSQL = ""
sSQL = sSQL & " SELECT"
sSQL = sSQL & " [作成基準日]"
sSQL = sSQL & " FROM [dbo_Ｔマスタ];"
Set DB = CurrentDb
Set rstDAO = DB.OpenRecordset(sSQL, dbOpenDynaset)
If Not (rstDAO.EOF) Then
KIJYUNBI(1) = rstDAO![作成基準日]
End If
rstDAO.Close

'確認メッセージ
MsgBox ("現在のリンク先テーブルの基準日は次のとおりです。" & vbNewLine & _
"" & vbNewLine & _
"　・顧客基本　＝" & KIJYUNBI(1) & vbNewLine & _
"" & vbNewLine & _
"　　　※ データが１件も無いテーブルは基準日が表示されません。")

'クローズ処理
Set rstDAO = Nothing
DB.Close: Set DB = Nothing

お礼日時：2017/06/08 12:33

No.4 回答者： ShowMeHow 回答日時： 2017/06/08 15:27

テーブル名やフィールド名は[]でくくらなくてもいいような気はするんですけど、、、

それが問題なのかは、はっきりとはわかりません。

どの行でどのようなエラーが出るのかを教えていただくことは可能でしょうか？

この回答へのお礼

ShowMeHowさん、こんばんは♪

ＮＯ３の「この回答へのお礼 」を実行しますと、「dbo_Ｔマスタ」が存在しません。というエラーが出力されます。②のアクセスファイルを確認しましたが、dbo_Ｔマスタは存在しました。
恐らくですが、構文が悪くて、①のアクセスファイルを参照しにいっているのではないかな～と思います（確かに、①のアクセスファイルには「dbo_Ｔマスタ」存在しません）

お礼日時：2017/06/08 18:33

No.5 回答者： ShowMeHow 回答日時： 2017/06/08 20:07

"C:\....\XXX.mdb"

は削除したいリンクテーブルがあるｍｄｂファイルをフルパスで記載してください。

この回答へのお礼

リンクテーブルの削除について、できました。
ShowMeHowさん、ありがとうございます♡

もう１つだけ教えてください。

リンクテーブルを切替えた後、切替えたリンクテーブルのデータ基準日を出力したいです。
データ基準日は、リンクテーブルの中に項目があります。

例にならって、以下の構文を、今回の構文に追加したのですが、うまくいきません。
ご指導お願いします♪


～省略

app.DoCmd.TransferDatabase acLink, "ODBC", "ODBC;DSN=" & DSN_Kangen & ";DATABASE=" & DSN_Kangen, acTable, "Ｔマスタ", "dbo_Ｔマスタ"
app.CloseCurrentDatabase
MsgBox ("リンクテーブル切替処理終了！ ")

sSQL = ""
sSQL = sSQL & " SELECT"
sSQL = sSQL & " [データ基準日]"
sSQL = sSQL & " FROM [dbo_Ｔマスタ];"
Set DB = CurrentDb
Set rstDAO = DB.OpenRecordset(sSQL, dbOpenDynaset)
If Not (rstDAO.EOF) Then
KIJYUNBI(1) = rstDAO![作成基準日]
End If
rstDAO.Close

MsgBox ("現在のリンク先テーブルの基準日は次のとおりです。" & vbNewLine & _
"" & vbNewLine & _
"　・顧客基本　＝" & KIJYUNBI(1) & vbNewLine & _
"" & vbNewLine & _
"　　　※ データが１件も無いテーブルは基準日が表示されません。")

Set rstDAO = Nothing
DB.Close: Set DB = Nothing

app.CloseCurrentDatabase

End Function

お礼日時：2017/06/09 08:26

No.6 回答者： ShowMeHow 回答日時： 2017/06/09 08:52

＞Set DB = CurrentDb

これは、コードを書いているmdb(自分自身)を参照するための指示です。
テーブルが存在するmdbを参照するためには、前のように、

Set DB = OpenDatabase("C:\....\XXX.mdb")
フルパスで記載するとよいと思います。

（うまくいかない時は、途中で止まってエラーが出るなら、どこで止まってどういうエラーが出るのか、、、
何も起きない場合は、何も出力されないなど詳細を記載していただけると答えにたどり着くのがはやくなると思います。）

この回答へのお礼

ShowMeHowさん、こんにちは♪
親切丁寧にありがとうございます♡

Set DB = OpenDatabase("C:\....\XXX.mdb")を追加しました。
実行したら、Set rstDAO = DB.OpenRecordset(sSQL, dbOpenDynaset)でエラーになってしまいました。
ご指導お願いします。


～省略

app.DoCmd.TransferDatabase acLink, "ODBC", "ODBC;DSN=" & DSN_Kangen & ";DATABASE=" & DSN_Kangen, acTable, "Ｔマスタ", "dbo_Ｔマスタ"
app.CloseCurrentDatabase
MsgBox ("リンクテーブル切替処理終了！ ")

sSQL = ""
sSQL = sSQL & " SELECT"
sSQL = sSQL & " [データ基準日]"
sSQL = sSQL & " FROM [dbo_Ｔマスタ];"

Set DB = OpenDatabase("C:\....\XXX.mdb")
　　　　　　　　↑
　　　　　　　追加

Set rstDAO = DB.OpenRecordset(sSQL, dbOpenDynaset)
　　　　　　　　↑
　　　　　　　ここでエラー

If Not (rstDAO.EOF) Then
KIJYUNBI(1) = rstDAO![データ基準日]
End If
rstDAO.Close

MsgBox ("現在のリンク先テーブルの基準日は次のとおりです。" & vbNewLine & _
"" & vbNewLine & _
"　・顧客基本　＝" & KIJYUNBI(1) & vbNewLine & _
"" & vbNewLine & _
"　　　※ データが１件も無いテーブルは基準日が表示されません。")

Set rstDAO = Nothing
DB.Close: Set DB = Nothing

app.CloseCurrentDatabase

End Function

お礼日時：2017/06/09 10:11

No.7 回答者： ShowMeHow 回答日時： 2017/06/09 11:19

うまく説明できていなくて、申し訳ありません。

　現時点で、何ができていて何ができていないのかが私にもわからなくなってきました。

一回整理しましょう

ＳＱＬサーバーのリンクテーブルをつくるmdbのパスとファイル名、拡張子を　strLinkMDBなどの変数に格納してください。

DIM strLinkMDB as string
strLinkMDB = "C:\フォルダ名(\サブフォルダ名・・・）\ファイル名.mdb"

こんな感じで、できます。　フォルダ名、サブフォルダ名、ファイル名は正しいものを入れてください。
リンクテーブルの名前も変数に入れておきましょうか？

DIM strLinkTableName as string
strLinkTableName = "dbo_Ｔマスタ"

①　まずは、リンクテーブルが存在する場合に削除する部分を書きましょう。

dim i as Variant
dim db as dao.database
dim strSQL as string
set db as OpenDatabase strLinkMDB
for each i in db.tabledefs
if i.name = strLinkTableName then
strSQL = "DROP TABLE " & strLinkTableName & ";"
db.execute strSQL
exit for
end if
next i

ここで、一回実行をしてみて、リンクテーブルdbo_Ｔマスタが対象のmdbから削除されていることを確認しましょう。

⓶　この次にリンクテーブルを作る部分を書きましょう。　私の環境では、ご提示のコードは動きませんでした。　どのように実現できるのかがまだはっきりとはわかっていません。　コードを実行して、リンクテーブルができることを確認してください。　

Set app = CreateObject("Access.application")
app.OpenCurrentDatabase (strLINKMDB)
app.DoCmd.TransferDatabase acLink, "ODBC", "ODBC;DSN=" & DSN_Kangen & ";DATABASE=" & DSN_Kangen, acTable, "Ｔマスタ", "dbo_Ｔマスタ"

ここまで、ちゃんとできているのであれば、次の課題（データの取得）に取り組みましょう。

この回答へのお礼

ShowMeHowさん、こんにちは♪
リンクテーブルの削除⇒リンクテーブルの貼り直し　までは問題なくできました。
次の課題をお願いします♪

お礼日時：2017/06/09 12:28

No.8 ベストアンサー 回答者： ShowMeHow 回答日時： 2017/06/09 13:10

リンクテーブルが所定の場所に作られていれば、さっきの続きに、、、

dim RST as dao.recordset
strSQL = "SELECT * FROM " & strLinkTableName & ";"
set rst = openrecordset(strSQL)
If Not rst.EOF Then
kijunbi(1) = rst!作成基準日
Else
kijunbi(1) =""
end if
rst.close: set rst = nothing
db.close: set db = nothing

でよいはずです。　（私はこれでできました。）できない場合は所定の場所に「dbo_Ｔマスタ」というリンクテーブルがあり、そのテーブルに「作成基準日」というフィールドがあることをご確認ください。

この回答へのお礼

できました♪
今まで、親切・丁寧にありがとうございました。
とても、助かりました♡

お礼日時：2017/06/09 14:55