ODBC使用時の正しい記述方法

質問

データベース初心者です。
ODBCの正しい記述方法についてご教示願います。
以下のソースでデータベース間のテーブルコピーを試したところができるものとできないものがあります。
テーブルはすべて作成済み、Windows認証です。
　環境　OS:WidowsXp、Access2003、Excel2007、SQL ServerExpress2008

(1)SQL Server ⇒ SQL Server
　記述の方法に問題があるようなメッセージです。
　ドライバやテーブルの指定が同様な記述で(4)や(5)がOKなのに(1)ではNGです。DRIVER指定ができないようです。
エラーメッセージ
"[Microsoft][ODBC SQL Server Driver][SQL Server]オブジェクト名 'odbc;DRIVER=SQL Server;Server=Server1\\SQLEXPRESS2;Database=TEST1;Integrated Security=SSPI;.TABLE1' が無効です。"

(2)は仕様上出来ないのでしょうか？
エラーメッセージ
"[Microsoft][ODBC Microsoft Access Driver] ODBC を使用して、外部 Microsoft Jet データベース エンジンのテーブルや組み込み可能な ISAM データベースのテーブルのインポート、エクスポート、またはリンクを行うことはできません。"

実用上はOLEを使用しているので問題はありませんが、向学のためによろしくお願いします。

/////////////////////////// ソース /////////////////////////////////////
(1)SQL Server ⇒ SQL Server 動作NG
Sub sqlsvr2sqlsvr()
Dim cn As New ADODB.Connection
Dim strSQL As String

cn.Open "DRIVER={SQL Server};Server=Server1\SQLEXPRESS1;Database=TEST1"
strSQL = "INSERT INTO [odbc;DRIVER=SQL Server;" & _
"Server=Server1\SQLEXPRESS2;Database=TEST1;" & _
"Integrated Security=SSPI;].TABLE1 " & _
"SELECT * FROM TABLE1"

cn.Execute strSQL
cn.Close
Set cn = Nothing
End Sub

(2)Excel ⇒ Access　動作NG
Sub Access2Excel()
Dim cn As New ADODB.Connection
Dim strSQL As String

cn.Open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=C:\TEST1.MDB"
strSQL = "INSERT INTO [DRIVER={Microsoft Excel Driver (*.xls)};" & _
"DBQ=C:\TestBook.xls;ReadOnly=False].[Sheet1$] " & _
"SELECT * FROM TABLE1"

cn.Execute strSQL
cn.Close
Set cn = Nothing
End Sub

(3)Excel ⇒ Excel 動作OK
Sub Excel2Excel()
Dim cn As New ADODB.Connection
Dim strSQL As String

cn.Open "odbc;DRIVER={Microsoft Excel Driver (*.xls)};DBQ=C:\TestBook.xls;ReadOnly=False;"
strSQL = "INSERT INTO [C:\TestBook2.xls].[Sheet1$]" & _
"SELECT * FROM [Sheet1$]"
cn.Execute strSQL
cn.Close
Set cn = Nothing
End Sub

(4)Access ⇒ SQL Server 動作OK
Sub access2sqlsvr()
Dim cn As New ADODB.Connection
Dim strSQL As String

cn.Open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=C:\TEST0.MDB"
strSQL = "INSERT INTO [odbc;DRIVER=SQL Server;" & _
"Server=Server1\SQLEXPRESS1;Database=TEST1;" & _
"Integrated Security=SSPI;].TABLE1 " & _
"SELECT * FROM TABLE1"

cn.Execute strSQL
cn.Close
Set cn = Nothing
End Sub

(5)Excel ⇒ SQL Server 動作OK
Sub excel2sqlsvr()
Dim cn As New ADODB.Connection
Dim strSQL As String

cn.Open "DRIVER={Microsoft Excel Driver (*.xls)};DBQ=C:\TestBook.xls;ReadOnly=False;"
strSQL = "INSERT INTO [odbc;DRIVER=SQL Server;" & _
"Server=Server1\SQLEXPRESS1;Database=TEST1;" & _
"Integrated Security=SSPI;].TABLE1 " & _
"SELECT * FROM [Sheet1$]"
cn.Execute strSQL
cn.Close
Set cn = Nothing
End Sub

(6)Access ⇒ Access 動作OK
Sub Access2Access()
Dim cn As New ADODB.Connection
Dim strSQL As String

cn.Open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=C:\TEST0.MDB"
strSQL = "INSERT INTO T_写真台帳1 SELECT * FROM [C:TEST1.MDB].T_写真台帳"
cn.Execute strSQL
cn.Close
Set cn = Nothing
End Sub

回答No.1

jamshid6

(1)ができない理由は明確です。

(1)だけクエリがSQL Serverで実行されますが、SQL Serverがインスタンス外のリソースにアクセスする方法は、
・リンクサーバ
・アドホックリモートクエリ
の２種類しか認められていないからです。

したがって、
strSQL = "INSERT INTO OPENROWSET('MSDASQL','DRIVER={SQL Server};" & _
"Server=Server1\SQLEXPRESS2;Database=TEST1;" & _
"Integrated Security=SSPI;','SELECT * FROM TABLE1') " & _
"SELECT * FROM TABLE1"

であれば通るでしょうが、今のクエリでは通りません。
（これにしても"Ad Hoc Distributed Queries"を有効にしないと通りませんが）

お礼

>SQL Serverがインスタンス外のリソースにアクセスする方法は、
>・リンクサーバ
>・アドホックリモートクエリ
>の２種類しか認められていないからです。

なるほどそういうことですか。仕様上できないのですね。
OPENROWSETを用いる方法は既に確認済みなので、Ad Hoc Distributed Queriesを用いないでできないものかと試していたところです。

補足

回答のお礼と補足を間違えてしまいました。
どうもありがとうございました。