EXCEL vbaからACCESSのクエリを開く

質問

EXCEL2010

WEBを参照してEXCEL　VBAでACCESSのクエリを開くマクロを流用しました。

下記がマクロの内容です。

Private Sub Import()
Dim db As ADODB.Connection
Dim rs As ADODB.Recordset

'ACCDBファイルに接続します
Set db = New ADODB.Connection
db.Provider = "Microsoft.Ace.OLEDB.12.0"
db.Open "C:\work\TEHAI.accdb"

'レコードセットを開きます
Set rs = New ADODB.Recordset
'Accessのクエリを開く
rs.Open Source:="TEHAI", ActiveConnection:=db

If rs.EOF Then
MsgBox "抽出した結果、レコードが見つかりません。"
Else
' レコードをシートへ貼り付ける
Range("A1").CopyFromRecordset rs
End If

rs.Close
Set rs = Nothing
Set db = Nothing

End Sub

これを実行すると、
抽出した結果、レコードが見つかりません。
が表示されます。

ACCESS単体でTEHAIクエリを実行したら、約３万件くらいヒットします。
EXCELマクロから実行したらACCESSからデータをもってこられません。
なぜレコードが見つかりません、
となるのでしょうか？

マクロのどこがおかしいのか、教えていただきたく。

ちなみに、もともとの内容から変更したのは
db.Open "C:\work\TEHAI.accdb"
rs.Open Source:="TEHAI",
の２ヶ所だけです。

回答No.5

piroin654 · ベストアンサー

原因は、

＞回答：クエリは
＞Like "15*"
＞のひとつだけです。

です。Accessではワイルドカードは「*」ですが、
Access以外でADOを使う場合は、「%」を使います。
したがって、Accessのクエリで、

Like "15*'

を

　Like "15%"

のようにするか、あるいはExcelの側で、たとえば、

strSQL = "select * from テーブル名 where (テーブル名.番号) like "15%""

のようにして、これをレコードソースとして、

rs.Open strSQL, ActiveConnection:=db

として、データをExcelに取り込みます。このときはAccessは
手直しをする必要はありませんが(たぶん)。

お礼

何度も回答いただきありがとうございます。
助かりました。

＊と％、気をつけたいと思います。
同じ様な間違いの参考になればと思います。

補足

Like "15%"
で解決できました。
access単独では開けていたので？？？でした。

回答No.6

piroin654

書き忘れましたが、No5で
　strSQL
は変数宣言をしておいてください。
Dim strSQL as String
です。

回答No.4

freetaka

少し気になりますね

If rs.EOF Then
MsgBox "抽出した結果、レコードが見つかりません。"
Else
' レコードをシートへ貼り付ける
Range("A1").CopyFromRecordset rs
End If

の部分をif文だけコメントアウトし

MsgBox rs.RecordCount
Range("A1").CopyFromRecordset rs

のみにした場合は
実行時エラーが発生したりしないなら
レコードのカウントが「-1」にもかかわらず
シートに結果が表示されたりはしませんか？
またはカウントが「-1」で表示されないかですが
アクセス側でクエリを実行した場合に
正しくデータ検索できるのであれば
エクセル側で実行した場合に実行できない理由が
データ側またはクエリ内にあるかもしれませんね
そうなるとクエリやテーブル構造も公開していただかないと
ＶＢＡだけで解決は難しいかもしれません
というか回答が難しいです

お礼

回答ありがとうございます。

補足

回答Ｎｏ．５にて解決できました。

回答No.3

freetaka

rs.Open Source:="TEHAI", ActiveConnection:=db

↓

rs.Open "TEHAI", db, adOpenStatic

ではどうでしょう？
もしだめであれば件数の多いデータベースであれば
データベースの最適化修復を実施すると
稀に実行できなかったものが
実行できることもあります
そちらも試してみてください

お礼

回答ありがとうございます。

補足

rs.Open "TEHAI", db, adOpenStatic

実施結果
現象変わらずＮＧです。

もしだめであれば件数の多いデータベースであれば
データベースの最適化修復を実施すると
稀に実行できなかったものが
実行できることもあります
そちらも試してみてください

実施結果

現象変わらずＮＧです。

回答No.2

piroin654

No1です。
クエリは、普通の選択クエリですか？
何か、抽出条件とかSQL文が複雑な構文に
なっているとか、というものでもありませんか？

どうしても、Excelから取り出せないとなると、
Accessから送り出したほうがいいかもしれませんが、それでは
仕事が遅れますかね。

あるいは、いっそのことAccessのクエリのSQL文を
Excelに書き込む、つまり

strSQL = "select * from クエリ名"

これを、AccessのクエリのSQL文に置き換える
ということです。

もう一つは、MS QueryをExcelで起動してAccessから
取り出す、つまりBookの「データ」→「外部データの取り込み」
→「新しいデータベース、クエリ」からAccesのクエリ、あるいは
テーブルを指定してデータを確保するという方法ですが。

お礼

回答ありがとうございます。

補足

クエリは、普通の選択クエリですか？
何か、抽出条件とかSQL文が複雑な構文に
なっているとか、というものでもありませんか？

回答：クエリは
Like "15*"
のひとつだけです。

参考
accessを使っているのは、あるサーバのview機能を使用して、テーブルの情報（Ａ）をaccessに持ってくるからです。
Ａは約１３０万件くらいあり、excelに持ってこれないので、
クエリを使用して、TEHAIクエリというのを作成しています。
これを実行すると約３万件くらいになっています。

strSQL = "select * from クエリ名"
は、VBAで実施しましたが、現象かわらずです。

もう一つは、MS QueryをExcelで起動してAccessから
取り出す、つまりBookの「データ」→「外部データの取り込み」
→「新しいデータベース、クエリ」からAccesのクエリ、あるいは
テーブルを指定してデータを確保するという方法ですが。

実施結果：
この方法でaccessデータをexcelに持ってくることが出来ました(#^.^#)
これでやってみます。
当初の内容が解決できていないので、もうしばらく情報待ちさせていただきます。

回答No.1

piroin654

コードには問題はないと思いますが。

クエリの名前は大丈夫ですか？
テーブルを指定した場合はどうなりますか？

Excel側からSQL文でレコードをとってみたら
どうなりますか？たとえば以下のように。
クエリの名前は実際に合わせてください。
あるいは、クエリをテーブルに置き換えてみてください。

Dim db As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strSQL as String

strSQL = "select * from クエリ名"

'ACCDBファイルに接続します
Set db = New ADODB.Connection
db.Provider = "Microsoft.Ace.OLEDB.12.0"
db.Open "C:\work\TEHAI.accdb

'レコードセットを開きます
Set rs = New ADODB.Recordset
rs.Open strSQL, ActiveConnection:=db

Range("A1").CopyFromRecordset rs

rs.Close
Set rs = Nothing
Set db = Nothing

お礼

回答ありがとうございます。

補足

クエリの名前、大丈夫です。
テーブルだとexcelにデータ持ってくることが出来ます。

strSQL = "select * from クエリ名"
を加えて実施しても現象同じでexcelにデータ持ってくることが出来ません。

実施結果まで