Excel VBA質問箱 IV

当質問箱は、有志のボランティア精神のおかげで成り立っています。
問題が解決したら、必ずお礼をしましょうね。
本サイトの基本方針をまとめました。こちら をご一読ください。

投稿種別の選択が必要です。ご注意ください。
迷惑投稿防止のため、URLの入力を制限しています。ご了承ください。


5859 / 13646 ツリー ←次へ | 前へ→

【48394】ExcelでAccessクエリ抽出 もとあし 07/4/16(月) 10:46 質問[未読]
【48396】Re:ExcelでAccessクエリ抽出 neptune 07/4/16(月) 11:50 回答[未読]
【48397】Re:ExcelでAccessクエリ抽出 ウッシ 07/4/16(月) 11:51 発言[未読]
【48403】Re:ExcelでAccessクエリ抽出 もとあし 07/4/16(月) 14:19 お礼[未読]
【48425】Re:ExcelでAccessクエリ抽出 ウッシ 07/4/17(火) 9:05 発言[未読]
【48428】Re:ExcelでAccessクエリ抽出 neptune 07/4/17(火) 11:45 発言[未読]
【48431】Re:ExcelでAccessクエリ抽出 もとあし 07/4/17(火) 12:07 お礼[未読]
【48532】Re:ExcelでAccessクエリ抽出 neptune 07/4/20(金) 22:43 発言[未読]
【48417】Re:ExcelでAccessクエリ抽出 neptune 07/4/16(月) 22:48 発言[未読]

【48394】ExcelでAccessクエリ抽出
質問  もとあし  - 07/4/16(月) 10:46 -

引用なし
パスワード
   いつもお世話になっています。
調べたり、試したのですが、分からないので質問させてください。

ADOでAccess(97)に接続して、エクセルシート上のテキストボックスの値をもとに
パラメータークエリを実行する、という方法を行おうとしています。
ですが、SQL句へどのように記述すればいいのか、分かりません。

下記のコードは、もともとはあるサイトで、クエリにパラメータークエリの為に
MsgBoxで代用するというものでした。

自分で少し、代えてみたのですが、次のエラーがでます。
「クエリ式'fldBBB=Worksheets("Sheet1").txtSNInput.Text'の'.'、'!'、または
'()'の使い方が正しくありません。」

他にも書き方を変えてみたりしたのですが、いずれにおいてもエラーがでます。
何が悪いのか、教えてください。

よろしくお願いします。

Sub ADO_Find()

Dim cn As ADODB.Connection
Dim cmd As New ADODB.Command
Dim rs As ADODB.Recordset
Dim param As ADODB.Parameter
Dim mySQL As String
Dim strcriteria As String
Dim strback As String


strSNTXT = "Worksheets(""Sheet1"").txtSNInput.Text"
strSN = Worksheets("Sheet1").txtSNInput.Text

mySQL = "SELECT * FROM tbl1 WHERE fldA =" & strSNTXT

Set cn = New ADODB.Connection
cn.ConnectionString = "provider=Microsoft.Jet.OLEDB.4.0;" _
            & "Data Source=K:xxx\db1.mdb"
cn.Open

Set cmd.ActiveConnection = cn

With cmd
  .CommandText = mySQL
  .CommandType = adCmdText
  .Prepared = True
End With
  
Set param = New ADODB.Parameter

Set param = cmd.CreateParameter("fldA", adVarChar, adParamInput, 20)
cmd.Parameters.Append param
strback = Trim(strSN)
cmd.Parameters("fldA") = strback

Set rs = New ADODB.Recordset
Set rs = cmd.Execute        ← ここでエラーがでます。

Do Until rs.EOF
  Debug.Print rs!fldA, rs!fldB, rs!fldC
  rs.MoveNext
Loop

Set cmd = Nothing
rs.Close: Set rs = Nothing
cn.Close: Set cn = Nothing

End Sub

【48396】Re:ExcelでAccessクエリ抽出
回答  neptune  - 07/4/16(月) 11:50 -

引用なし
パスワード
   こんにちは
>何が悪いのか、教えてください。

>strSNTXT = "Worksheets(""Sheet1"").txtSNInput.Text"
「エクセルシート上のテキストボックスの値をもとに」したいのなら
これまずいのでは?これでは右辺のTextデータになりますから。

>mySQL = "SELECT * FROM tbl1 WHERE fldA =" & strSNTXT
Textデータなら「'」でくくらなければいけません。
データ型を明確にしましょう。

そして、SQL文が自分の思うとおりになっているかどうかを確認する事は
必須です。・・・しましたか?

>Set cn = New ADODB.Connection
>cn.ConnectionString = "provider=Microsoft.Jet.OLEDB.4.0;" _
>            & "Data Source=K:xxx\db1.mdb"
私の記憶では↑のConnectionStringでは正常に接続できなかったような
・・・・正常に接続できてますか?

Access97形式ならDAOを使用した方が、何かと簡単と思いますよ。

【48397】Re:ExcelでAccessクエリ抽出
発言  ウッシ  - 07/4/16(月) 11:51 -

引用なし
パスワード
   こんにちは

ちょっと書き方違っちゃいますけど・・・

Sub ADO_Find()

Dim cn As ADODB.Connection
Dim cmd As New ADODB.Command
Dim rs As ADODB.Recordset
Dim mySQL As String
Dim strcriteria As String
Dim strback As String
Dim strSNTXT As String
Dim strSN As String

strSNTXT = "txtSNInput"
strSN = Worksheets("Sheet1").txtSNInput.Text
strback = Trim(strSN)

mySQL = "SELECT * FROM tbl1 WHERE fldA=" & strSNTXT

Set cn = New ADODB.Connection
cn.Open "provider=Microsoft.Jet.OLEDB.4.0;" _
            & "Data Source=K:\db1.mdb"
    
With cmd
  .ActiveConnection = cn
  .CommandText = mySQL
  .CommandType = adCmdText
  .Prepared = True
  .Parameters(0).Value = strback
End With

Set rs = New ADODB.Recordset
Set rs = cmd.Execute        '← ここでエラーがでます。

Do Until rs.EOF
  Debug.Print rs!fldA, rs!fldB, rs!fldC
  rs.MoveNext
Loop

Set cmd = Nothing
rs.Close: Set rs = Nothing
cn.Close: Set cn = Nothing

End Sub

【48403】Re:ExcelでAccessクエリ抽出
お礼  もとあし  - 07/4/16(月) 14:19 -

引用なし
パスワード
   neptune さん、
ウッシ さん
こんにちは。
回答ありがとうございます。

ウッシさんのコードを使わせていただきましたら、問題なく動きました。
一番の問題は
strSNTXT = "Worksheets(""Sheet1"").txtSNInput.Text"
で、これをウッシさんの通り、
>strSNTXT = "txtSNInput"
へ変えたら残りも問題なく動作しました。

ですが、それで解決にせず、自分の中でスッキリさせたいので、
申し訳ありませんが、お教えください。

neptuneさん
>>strSNTXT = "Worksheets(""Sheet1"").txtSNInput.Text"
>「エクセルシート上のテキストボックスの値をもとに」したいのなら
>これまずいのでは?これでは右辺のTextデータになりますから。
すみません。何がまずいのかよく意味がわかりませんでした。
またウッシさんの通り「Worksheets(""Sheet1"")」を抜き、
>strSNTXT = "txtSNInput.Text"
にしましたら、問題なく動作しましたが、なぜWorksheets(""Sheet1"")ダメ
なのか分かりません。エラーが出ても、私の端末のヘルプにはADOのヘルプは
入っていないようで、参照できません。もし、シート名も書くとしたら
どういった記述がいいのでしょうか?

また
>Textデータなら「'」でくくらなければいけません。
>データ型を明確にしましょう。
ごめんなさい!これもどこを「'」でくくらないといけないのか、わかりませんでした。
手元にありますSQL本には確かに文字列定数には「'」で、と書かれていました。
ですが、この場合の定数はどれになるのか、わかりません。

>そして、SQL文が自分の思うとおりになっているかどうかを確認する事は
>必須です。・・・しましたか?
SQLも実際のAccessでクエリを作成しSQLを見てみたり、SQL本を見てみたのですが、
どうしてもstrSNTXTやstrSNの記述の仕方が分かりませんでした。
(strSNTXTやstrSNと2つ用意しないといけないのはつきとめたのですが・・・。)

最後にもう1つ教えてください。
ウッシさんのにはCreateParameterメソッドが使用されていません。
この場合、なくてもいいのなら、どんなときに必要となるのでしょうか。
ADOやDAOに関しての良いWebをお教えください。

まだまだ未熟者がADOやDAOの違いもよく分かっていないのに、
(いろいろWebや本など目を通したのですが、それでも明確な相違点がわかりません。)
SQLすら満足に扱えていないのに、こういったものを作るほうが大それているのですが、
少しずつでも前進していきたいと思っています。

ながながとすみません。
基本的には解決しておりますので、もし気が向いたらで結構なので、
教えてください。
よろしくお願いします。
ありがとうございました。

【48417】Re:ExcelでAccessクエリ抽出
発言  neptune  - 07/4/16(月) 22:48 -

引用なし
パスワード
   ▼もとあし さん:

>すみません。何がまずいのかよく意味がわかりませんでした。
>またウッシさんの通り「Worksheets(""Sheet1"")」を抜き、
>>strSNTXT = "txtSNInput.Text"
>にしましたら、問題なく動作しましたが、なぜWorksheets(""Sheet1"")ダメ
>なのか分かりません。エラーが出ても、私の端末のヘルプにはADOのヘルプは
>入っていないようで、参照できません。もし、シート名も書くとしたら
>どういった記述がいいのでしょうか?
>strSNTXT = "txtSNInput"
>strSN = Worksheets("Sheet1").txtSNInput.Text
この辺りが私には理解できないのでなんともいえません。
"txtSNInput"と言う文字列データがDBに入っているのですか????
それともダミー?なら何で???こんな事?
知識不足でわかりません。

>また
>>Textデータなら「'」でくくらなければいけません。
>>データ型を明確にしましょう。
詳細な説明をするほど詳しくないので解説を読んで下さい。
SQL Server 2005 Books Online 
クエリの基礎
//msdn2.microsoft.com/ja-jp/library/ms190659.aspx
比較検索条件
//msdn2.microsoft.com/ja-jp/library/ms191529.aspx
SQL ServerはADOで使用できるSQLの標準に準拠していると思いますのでこのあたりはほぼ同じと思います。


>最後にもう1つ教えてください。
>ウッシさんのにはCreateParameterメソッドが使用されていません。
>この場合、なくてもいいのなら、どんなときに必要となるのでしょうか。
本来プログラム内でSQLを指定する今回のような使い方では必要と思います。
でも、ウッシさんのやり方でもOKなんですね。知りませんでした。
ただ、UPされた程度でパラメータクエリのような形にする程のものでもないような・・・
mdbにパラメータクエリが用意されている場合は必要ないとは思います。
私はパラメータの定義(データ型など)をわかり易くするため、ほとんどの場合、使用します。
ちなみにMSDE、SQLServerなどのストアドプロシージャでは、入力用、出力用などの目的でも使います。

詳しくは
//msdn.microsoft.com/library/ja/default.asp?url=/library/ja/jpado260/htm/mdobjparameter.asp
の「解説」の部分を読んで下さい。

後で改造するときは、CreateParameterを使用していると、debugなどの時、
条件やデータ型がわかり易く(忘れるんですよね)、その訂正時もわかりやすいです。

>ADOやDAOに関しての良いWebをお教えください。
ここは良いですよ。サンプルが豊富です。
//homepage2.nifty.com/inform/vbdb/


>少しずつでも前進していきたいと思っています。
頑張って下さいませ^ ^
まったくこれから勉強されるなら、ADOだけでも良いかもしれません。
ただ、ADOもDAOも構文自体は似たようなもので(慣れたせいかもしれませんが)
DAOはAccessの為のJETエンジンを操作するためのものですから、
Accessを操作するにはDAOを覚えても損はしないかもしれませんね?
(AccessがいつまでJetかはわかりませんけど、Jetを使用しているバージョン
のAccessを使用している限りは使えますから。)

ところで最初にも書きましたが
>cn.ConnectionString = "provider=Microsoft.Jet.OLEDB.4.0;" _
>            & "Data Source=K:xxx\db1.mdb"
で接続できるんですね。以前はこれだけではだめだった記憶があるんですが。。。
これだけで接続できるとは知りませんでした。

【48425】Re:ExcelでAccessクエリ抽出
発言  ウッシ  - 07/4/17(火) 9:05 -

引用なし
パスワード
   ▼もとあし さん:
>neptune さん、
こんにちは。

>strSNTXT = Trim("'Worksheets(""Sheet1"").txtSNInput.Text'")
とすれば大丈夫だと思いますよ。
でも何故シート名を付けるのでしょうか?
neptune さんのレスのように単なる初期値みたいなものですよね?

今回のケースではパラメータを使う必要も無いと思います。
このマクロを実行する時点で、
「Worksheets("Sheet1").txtSNInput.Text」は入力されているのでしょうから
初めから、mySQL文にセットして抽出すればいいと思います。

>ウッシさんのにはCreateParameterメソッドが使用されていません。
>この場合、なくてもいいのなら、どんなときに必要となるのでしょうか。
というか、CreateParameterメソッドを使った書き方でうまく抽出出来なかったので
提示したコードにしました。

下記コードはエラーにはなりませんけど、なにも抽出出来ませんでした。

  Dim cn    As New ADODB.Connection
  Dim cmd   As New ADODB.Command
  Dim rs    As New ADODB.Recordset
  Dim param  As New ADODB.Parameter
  Dim mySQL  As String
  Dim strSNTXT As String
  Dim strmsg  As String

  strSNTXT = Trim("'Worksheets(""Sheet1"").txtSNInput.Text'")
  mySQL = "SELECT * FROM tbl1 WHERE fldA=" & strSNTXT
  cn.ConnectionString = "provider=Microsoft.Jet.OLEDB.4.0;" _
            & "Data Source=K:\db1.mdb"
  cn.Open
  Set cmd.ActiveConnection = cn

  With cmd
    .CommandText = mySQL
    .CommandType = adCmdText
    .Prepared = True
  End With

  Set param = cmd.CreateParameter("パラメータ", adVarChar, adParamInput, 20)
  cmd.Parameters.Append param
  strmsg = Trim("'" & Worksheets("Sheet1").txtSNInput.Text & "'")
  cmd.Parameters("パラメータ") = strmsg
  
  Set rs = cmd.Execute

  Do Until rs.EOF
    Debug.Print rs!fldA, rs!fldB, rs!fldC
    rs.MoveNext
  Loop
  
  Set cmd = Nothing
  rs.Close: Set rs = Nothing
  cn.Close: Set cn = Nothing

どこかおかしいでしょうか?

【48428】Re:ExcelでAccessクエリ抽出
発言  neptune  - 07/4/17(火) 11:45 -

引用なし
パスワード
   ▼ウッシ さん:
>▼もとあし さん:
こんにちは

ソース内でSQLを書いて、それをパラメータクエリとして扱うことは
したことがないので手持ちのmdb(Access2000)で検証してみました。

で、結果ですが、CommandTextを パラメータクエリとして指定してないので
cmdはパラメータクエリとしては扱ってないと思います。
 従って、
>Set param = cmd.CreateParameter("パラメータ", adVarChar, adParamInput, 20)
以降のparamに関する記述は無意味みたいです。ローカルウィンドウで
cmdの各プロパティを見てみればわかります。

私としてはこのような使い方はしたことないし、するつもりもないので
こういう記述の良し悪しは別としてですが、そんなもんかなという所です。
むしろ頻繁にこういった処理をするならDBの設計としてパラメータクエリ、
ストアドプロシージャなどを利用すべきと思いますので。

キチンとパラメータクエリとしてDBに登録し、その後cmdをそのパラメータクエリ
を扱うものとして設定して扱えば問題ないとは思います。

そういうことでお願いしますm(_ _)m

【48431】Re:ExcelでAccessクエリ抽出
お礼  もとあし  - 07/4/17(火) 12:07 -

引用なし
パスワード
   neptune さん
ウッシ さん
こんにちは。

お二人とも、私の質問に丁寧に答えてくださり、本当にありがとうございます。
今、回答を見させていただいた所なので、回答の中身や
提供いただいたWebサイトをちょっと時間はかかりそうですが、
自分の中に噛み砕いて行きたいと思います。


neptune さん
>>Set param = cmd.CreateParameter("パラメータ", adVarChar, adParamInput, 20)
>以降のparamに関する記述は無意味みたいです。ローカルウィンドウで
>cmdの各プロパティを見てみればわかります。
無意味なのですか。。。
これもいただいた回答をもとに考えて見ます。

ウッシさん、
neptuneさん、
本当にありがとうございました。

【48532】Re:ExcelでAccessクエリ抽出
発言  neptune  - 07/4/20(金) 22:43 -

引用なし
パスワード
   ▼もとあし さん:
ウッシ さん
こんにちは。

もうとっくに終わってますが、今日自分の事で、発見しましたので報告
>neptune さん
>>>Set param = cmd.CreateParameter("パラメータ", adVarChar, adParamInput, 20)
>>以降のparamに関する記述は無意味みたいです。ローカルウィンドウで
>>cmdの各プロパティを見てみればわかります。
の件ですが、ADO.netでは文法は少し違いますが同じようなことが可能でした。
VB6でのADOについては以前調べてませんが。

しかし、SQLを直接投げるのと処理速度は変わりませんでした。
コードを書く手間も、ものすごく長くなるとパラメータを使った方が
可読性が良くなるでしょうが、そうでない場合は変わらないようです。

5859 / 13646 ツリー ←次へ | 前へ→
ページ:  ┃  記事番号:
2610219
(SS)C-BOARD v3.8 is Free