ACCESSからSQLServerのデータを取得したい

質問

SQLServerが利用できない環境にデータを持っていくため、以下のようなシステムを考えております。（テーブルのリンクは不可・手動でのデータのエクスポートは不可）

1.ACCESSのフォームに「データベース名」「テーブル名」を入力し、実行ボタン押下
2.ADOでSQLServerに接続
3.指定のテーブルと同じ構造のテーブルをACCESSに作成
4.SQLServerのデータをACCESSにINSERT

ここで質問なのですが、ACCESSは自分のデータベースとSQLServerのデータベースに同時に接続できますか？
同時接続した場合のACCESS/SQLServerの書き分けはどのようになるのでしょうか。

回答No.1

asahina02 · ベストアンサー

普通に同時接続できるかと思います。
ちょっと手元に環境がないので間違いがあるかもしれませんが以下のような感じでしょうか。
ようは各データベース用にそれぞれ変数を用意してあげれば済むことかと。

Dim accessdb As Database
Dim sqldb As ADODB.Connection

Set accessdb = CurrentDb
Set sqldb = New ADODB.Connection
sqldb.open()

それぞれのDBに対して処理する

お礼

アドバイスありがとうございます。なるほど、片方はCurrentDBで接続、片方はADOで接続すればよいのですね。この二つを同時に一つのSQL文で扱うことはできるでしょうか？「Insert into accessDB.ユーザーマスタ Select sqldb.ユーザーマスター」みたいな。

回答No.3

asahina02

#1です。
＞この二つを同時に一つのSQL文で扱うことはできるでしょうか？
これはさすがに無理ではないかと思います。

２のお礼で書かれているようにSQLServerのデータをレコードセットに取り、1レコードずつInsertするしかないと思います。

お礼

回答ありがとうございます。あれから試行錯誤いたしまして、マクロ→データベース変換でODBCドライバを使用することにより、簡単にテーブルデータを取り込めるようになりました。何度もお返事ありがとうございました。

回答No.2

O_cyan

>ACCESSは自分のデータベース・・に接続できますか？
自分のとはカレントDB？ということですか？それとも他のAccessファイルにADOで接続するということですか？

カレントDBからADOでSQLServerに接続すればカレントDB内で処理できますが。
もしくは#1の方のいうように変数を宣言してカレントDBを変数にセットすればいいのでは。
Dim acdb As Database
Set acdb = CurrentDb　のように。

’SQLServerに接続
Dim cnn As New ADODB.Connection
Dim rec As New ADODB.Recordset

' 接続を確立させる。
cnn.Open "Provider=SQLOLEDB;" & _
"Data Source=サーバー名;" & _
"Initial Catalog=サーバ上のデータベース名;", "sa", ""
’SQLを指定してレコードセットを作成する。
rec.Open "select * from テーブル名", cnn, _
adOpenKeyset, adLockOptimistic
他のAccessファイルに接続する場合はProvider=Microsoft.Jet.OLEDB.Jetのバージョン
に変えて接続を確立すれば出来ます。
質問内容と違っていたらゴメンなさい。

お礼

回答ありがとうございます。#1さんのお礼欄にも書いたのですが、一つのSQL文で同時に扱うことはできますでしょうか？それともSQLServerのデータをレコードセットに取り、1レコードずつInsertするしかないでしょうか。