ACCESSでORACLEのサーバからデータを取得

質問

ACCESSでORACLEのサーバからデータを取得しようと試みましたがうまくいきません。どこを改善したらよろしいでしょうか？どなたかご教示ください。
クライアント：WINDOWS7 ACCESS2010 サーバ：WIN-SV2003 ORACLE10G

以下のエラーがVBAで取得しようとするとでてきてしまいます。
「[Microsoft][ODBC driver for Oracle][Oracle]ORA-00923:FROMキーワードが指定の位置にありません。」
SQL部分の空白は見直しました。

Private Sub コマンド610_Click()
Dim adoCON As Object
Dim rs As Object
Dim oraclebox() As Variant
Dim rdsu As Long

On Error GoTo Err_Han
'データベースに接続する'
Set adoCON = CreateObject("ADODB.Connection")

adoCON.Open "Driver={Microsoft ODBC for Oracle};" & _
"CONNECTSTRING=*****; UID=abcd; PWD=1234;"

sqlstr = "SELECT J.KTN_COD,J.SBN,J.GYOSYA_COD,G.GYOSYA_MEI" _
& "FROM JITU_SHARYOU J ,GYOSYA G" _
& "WHERE J.GYOSYA_COD = G.GYOSYA_COD"

Set rs = adoCON.Execute(sqlstr)

rs.MoveFirst
'レコード数カウント'
Do Until rs.EOF
rdsu = rdsu + 1
rs.MoveNext
Loop

'配列を再定義'
ReDim oraclebox(rdsu, 4)
i = 1
rs.MoveFirst
Do Until rs.EOF
oraclebox(i, 1) = rs!KTN_COD
oraclebox(i, 2) = rs!SBN
oraclebox(i, 3) = rs!GYOSYA_COD
oraclebox(i, 4) = rs!GYOSYA_MEI
i = i + 1
rs.MoveNext
Loop
rs.Close
Set rs = Nothing

'警告ダイアログをオフ'
DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE FROM SAMPLE_T"

'Oracleデータを書き込み'
For i = 1 To rdsu
DoCmd.RunSQL "INSERT INTO SAMPLE_T(KTN_COD,SBN,GYOSYA_COD,GYOSYA_MEI)" _
& "VALUES('" & oraclebox(i, 1) & "','" & oraclebox(i, 2) & "','" & oraclebox(i, 3) & "'," & oraclebox(i, 4) & ")"
Next i
'警告ダイアログをオン'
DoCmd.SetWarnings True

Exit Sub
Err_Han:
' エラー処理'
MsgBox (Err.Description)
End Sub

＊単一のテーブルであればDoCmd.TransferDatabase acImpor等を使うのですが、場合によっては今後データ量が多かったりより複数のテーブルを扱うことになるので。

回答No.2

30246kiku · ベストアンサー

＃１です

> While(Not rs.EOF)
> 　　rsTo.AddNew
> 　　rsTo!SBN = rs!SBN
> 　　・・・
> 　　rsTo.Update
> 　　rs.MoveNext
> Wend

ここのところの rsTo は、AddNew / Update しかしていないので
DAO / ADO 区別ありません。

Dim rsTo As DAO.Recordset

と記述して、メニューの「デバッグ」→「コンパイル」してみて
エラーにならなければ、DAO が使えます。（以下雰囲気）

Set rs = adoCON.Execute(sqlstr)

CurrentDb.Execute "DELETE FROM SAMPLE_T;"
Set rsTo = CurrentDb.OpenRecordset("SAMPLE_T")
While (Not rs.EOF)
　　rsTo.AddNew
　　rsTo!SBN = rs!SBN
　　・・・
　　rsTo.Update
　　rs.MoveNext
Wend
rs.Close
rsTo.Close

また、

Dim rsTo As New ADODB.Recordset

でコンパイルエラーでなければ ADO が使えます（以下雰囲気）

Set rs = adoCON.Execute(sqlstr)

CurrentProject.Connection.Execute "DELETE FROM SAMPLE_T;"
rsTo.Open "SAMPLE_T", CurrentProject.Connection, adOpenForwardOnly, adLockOptimistic
While (Not rs.EOF)
　　rsTo.AddNew
　　rsTo!SBN = rs!SBN
　　・・・
　　rsTo.Update
　　rs.MoveNext
Wend
rs.Close
rsTo.Close

※ コンパイルエラーでも、使いたいのであれば、参照設定で追加すれば良いと思います

※ 参照設定しなくても、質問者さんの様に（チョッと修正）

Dim cn As Object
Dim rsTO As Object
Set cn = CurrentProject.Connection
Set rsTO = CreateObject("ADODB.Recordset")
rsTO.Open "SAMPLE_T", cn, adOpenForwardOnly, adLockOptimistic　' ★

Object 宣言しておいて、CreateObject("ADODB.Recordset") することでも同じなのですが
adOpenForwardOnly　とか　adLockOptimistic　の様な定数部分の記述で苦労すると思います。

Dim rsTO As ADODB.Recordset

としておけば、

Set rsTO = CreateObject("ADODB.Recordset")
↓部分は、
Set rsTO = New ADODB.Recordset

で良いですし、

Dim rsTO As New ADODB.Recordset

としておけば、Set rsTO =　・・・　自体不要です

お礼

ありがとうございます、助かりました。おかげさまで実現できました。御礼申し上げます。

回答No.1

30246kiku

ORACLE との連携部分はわかりませんが

> sqlstr = "SELECT J.KTN_COD,J.SBN,J.GYOSYA_COD,G.GYOSYA_MEI" _
> & "FROM JITU_SHARYOU J ,GYOSYA G" _
> & "WHERE J.GYOSYA_COD = G.GYOSYA_COD"

FROM と WHERE 前にスペースが無いため・・・ ではないでしょうか。

余計なお世話で

rs （Recordset）のメソッドに、GetRows があります。
GetRows を使えば、簡単に配列データが作れます。

Dim v As Variant
・・・
v = rs.GetRows

v は、v(列,行) の二次元配列に

なお、レコード数がかなり多いのであれば、配列に展開するのではなく
SAMPLE_T 用の Recordset rsTo を開いておいて

While(Not rs.EOF)
　　rsTo.AddNew
　　rsTo!SBN = rs!SBN
　　・・・
　　rsTo.Update
　　rs.MoveNext
Wend

とかすれば・・・

お礼

できました、ありがとうございます。普段SQLエディタで抽出していたので気づきませんでした。
ちなみに「SAMPLE_T 用の Recordset rsTo を開いておいて・・・」というやりかたをもう少し
詳しく教えて頂いてもよろしいでしょうか？

while文の前に

Dim rsTO As Object
Set cn = CreateObject("ADODB.Connection")
Set rsTO = CreateObject("ADODB.Recordset")
rsTO.Open "SAMPLE_T", cn....

としたのですが、「この操作を実行するために接続を使用できません。
このコンテキストで閉じているかあるいは無効です。」と出てきて
うまくいきませんでした。access2010を使用していますが、DAOの
参照設定が必要になるのでしょうか？