こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

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クエリを実行したら、約3万件くらいヒットします。
EXCELマクロから実行したらACCESSからデータをもってこられません。
なぜレコードが見つかりません、
となるのでしょうか?

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

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

投稿日時 - 2015-06-24 14:13:52

QNo.9000030

暇なときに回答ください

質問者が選んだベストアンサー

原因は、

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

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

Like "15*'



 Like "15%"

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

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

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

rs.Open strSQL, ActiveConnection:=db

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

投稿日時 - 2015-06-26 13:02:33

補足

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

投稿日時 - 2015-06-26 16:31:18

お礼

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

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

投稿日時 - 2015-06-26 16:31:29

このQ&Aは役に立ちましたか?

1人が「このQ&Aが役に立った」と投票しています

回答(6)

ANo.6

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

投稿日時 - 2015-06-26 13:10:32

ANo.4

少し気になりますね

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

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

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

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

投稿日時 - 2015-06-26 12:25:56

補足

回答No.5にて解決できました。

投稿日時 - 2015-06-26 16:27:40

お礼

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

投稿日時 - 2015-06-26 16:27:45

ANo.3

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



rs.Open "TEHAI", db, adOpenStatic

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

投稿日時 - 2015-06-24 21:08:09

補足

rs.Open "TEHAI", db, adOpenStatic

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


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

実施結果

現象変わらずNGです。

投稿日時 - 2015-06-26 11:09:51

お礼

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

投稿日時 - 2015-06-26 11:09:24

ANo.2

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

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

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

strSQL = "select * from クエリ名"

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


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

投稿日時 - 2015-06-24 17:37:20

補足

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

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

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


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


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

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

投稿日時 - 2015-06-26 11:08:07

お礼

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

投稿日時 - 2015-06-26 11:08:14

ANo.1

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

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

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

投稿日時 - 2015-06-24 16:28:47

補足

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

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

実施結果まで

投稿日時 - 2015-06-24 17:04:27

お礼

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

投稿日時 - 2015-06-24 17:04:22

あなたにオススメの質問