ここから本文です

Access VBA(ADO)からストアドプロシージャを実行する方法と呼出されるストアドサン...

ms_********さん

2008/4/1312:06:24

Access VBA(ADO)からストアドプロシージャを実行する方法と呼出されるストアドサンプルをセットで教えてください。

Access初心者です。
SQL Serverの中に会員情報が2,000万レコードあります。

・入金テーブル

[更新列]...[列A]...[列B]...[列C]...[列D]....[列E]
-------------------------------------------
未入金....2008...10.....東京....銀振あり..備考なし
未入金....2008...10.....東京............備考なし
入金済....2007...12.....千葉............備考なし

※会員ID列は省略

月度の上旬、中旬、月末の3回ほどレコード更新を行なっています。

毎月業務イベントやプロモーションが行なわれるので Where文の条件を設定する対象フィールドが
3フィールド~150フィールドと条件が目まぐるしく変わります。
またフィールドは毎月1つか2つ新しいリレーションテーブルに追加になったりします。

その可変条件で20~100万件のレコード更新を行なっています。

下請エンジニアがVB6からSQLサーバ直でストアド実行すると10秒で終わる処理です。
Accessの更新クエリ、SQL Open、Execute、OpenRecordsetは
更新に10分ほど掛かるのでネット上で24H受付のユーザーに対応できません。

来年までに自動化する予定ですがそれまでにストアド実行の委託だけで毎月45万掛かります。

MSの有償サポートでは
VBAからパラメータ付ストアドが実行可能だと聞きました。
また複雑な場合はパラメータ指定せずにADOから仮ストアドを動的作成してキック可能とも聞きました。
しかし今はサポート契約期限切れで会社に稟議を取っている最中です。

条件は 以下の例のように 未入金 → 入金済 にしたいのです。

列A = 2008
列B = 10
列C = 東京
列D = 銀振あり

加えて条件は以下のように変わります。

列A = 2008 だけ(列B、列C、列Dは条件なし) の場合
列A = 2008 AND 列B = 10 (列C、列Dは条件なし) の場合

ストアドと実行するVBAをセットでどのように書けばよいかサンプルを教えて貰えますか?

あとMSの言った通り100個位の可変条件があった場合に備えて
パラメータ無しでストアド用のSQLを直かに書いてテンプストアドを作成し実行する方法も教えてください。

Microsoftに確認を取ったのと
過去に社内バージョンでテーブルを更新を掛けていたVBA社内にありましたので(今は廃棄された)
「Accessではできません」と言う言い訳が出来ません。

申し訳ありませんが業務変更のアドバイスは必要ありませんのでご遠慮願います。
要件のための逆質問をしなければ回答できない方もご遠慮願います。
色々注文をつけて申し訳ありません。

補足確かに私はVB6.0から戻って来たクチですので
chirinuruwowaheさんの普通にSQLコマンドをExecuteする構文は前から死ぬほど知ってますし
ADOのアクセス違反になる事も勿論知ってました。

AccessVBAとVBの間にApplicationに依存するプロパティ以外はさほど違いが無いのが判ってきたのと
ataddprocer さんのやり方は十分ヒントになりましたのでVB風に手組みしてみたいと思います。

閲覧数:
33,418
回答数:
4

違反報告

ベストアンサーに選ばれた回答

ata********さん

2008/4/1602:06:40

> もう一度警告します やめておいてください

うっ、手厳しいですね。 chirinuruwowaheさん。

> 注文をつける時間があれば 内容を詳しく説明するか 勉強してください

多分 質問者さんは質問用の文字数がオーバー寸前じゃないかと思います(笑)
文字数数えたら結構ギリっぽいです。
廻りくどい所はありますがそれほどバカな質問の仕方でもないと思います。

それよりか chirinuruwowaheさんの回答の

> set myCon = CreateObject("ADODB.Connection")
> myCon.Open ("Provider=SQLOLEDB.1;Data Source=server;Password=;User ID=sa;Initial > Catalog=custmer3")
> set myCmd = CreateObject("ADODB.Command")
> myCon.CursorLocation = 3
> myCmd.ActiveConnection = myCon
> myCmd.CommandType = &H0004
> myCmd.CommandText = "直SQL"
> myCmd.Execute

こっちの方が問題です。

> 上記は今後検索する人のために回答しました

とありますがこれでは100%誤りです。

CommandType に &H0004 (要するに4)の指定をした場合は CommandTextに 既存ストアドの名前しか指定できません。
SQL文などのストアド名以外を書いた場合はExecuteが許可されずエラーになります。

おまけにこのサンプルはパラメータの指定も無いですし
動的にストアドを作成して実行する構文も入っていません。

Googleあたりで「ストアト」゙と「ADO」でググった結果を見て
更にADO ヘルプを見て 4 が adCmdStoredProc と書いてあるのを見て
「おーストアドだ」と安心して回答投稿したのでしょうがそうは問屋がおろしません(笑)
ADOでデバッグすれば一発でエラーになると判ります。

chirinuruwowaheさんは普段からテクニカルなmdbの質問にもキッチリとした回答をされている方なので
今回は言葉遣いの割には結構ムチャクチャで間違った見当違いのサンプルなので以外です。

この質問は動的なストアドとADOの連携なのでもう2捻りくらい工夫が必要です。
ただし動的にクリエイトするサンプルは最近はほとんど見かけません。
(ストアド名を呼び出す程度のサンプルは巷に死ぬほど沢山ありますけど)

> 上記は今後検索する人のために回答しました

とせっかく親切心で書いて頂きましたが
誤った内容が今後世に残らないために回答削除した方がよろしいのではないでしょうか?
それも今回の回答は非常に質問者に対してインパクトも強い内容ですし
黙って削除ではなく謝罪もきちんと入れてはどうでしょうか?
それが男気ってもんだと思います。
(女性だったらすみません)
まあ削除せずに知恵袋に燦然と輝く間違い回答ログとして残るのも良いかもしれませんけど(大笑)

ちなみに heartofmine01さんが内容がクリティカル過ぎると言う意見もありますが
Accessのうわべだけしか見てない意見だと思います。

Accessには大きく分けて mdb と adp の2つがありますが
確かに mdb しか運用できないユーザーに取ってストアドなどは未知であり不安領域です。

しかし adp 運用をしているAccessユーザーにとってはこの程度の内容は日常コーディングの部類です。
99年にAccess2000で初めてADO対応になった時は競争するようにネットにADOストアドのサンプルが沢山ありましたし。

別にこれを外部ベンダーに投げなければいけない必然などありません。
mdbのリンクテーブルで丸出しになったレコードをマウス操作やクエリでガッツリ変えられてしまう可能性よりは危険性少ないと思います(笑)

おそらく質問者の方は初心者を装ってますが
回答者の我々よりもよっぽど上級者に近い所にいるのではないでしょうか?
「Access初心者です」とは言っても本当はVB6あたりのプロがたまたまAccessに来ただけと言う可能性もあります。
AccessとVBのローカライズの違いで迷っているだけかもしれません。

とりあえず意見もした手前 回答しますが
要は テキストコマンドで CreateProcedure をExecuteさせて動的に仮ストアドを作り
次に同じADOプロシージャ内でその仮ストアドをExecuteすると言う最低2回Executeを起こす記述になります。

結果くらいは @RESULT などを返すParameterを投げても良いんではないか?と思います。

あと仮ストアド作る前にspストアドプロシージャで同名オブジェクトの存在チェックくらいはして置いて
存在したらdropするか それとも名前がバッティングしないようタイムスタンプを変数的にストアド名内で用いた方が良いと思います。

ここまで書いたので
後は chirinuruwowaheさんがいつものお得意のVBAで書き込んでやってください。
よろしくお願いします。

質問した人からのコメント

2008/4/16 11:13:41

降参 朝からやってできました。
CreateProcedure(←確かにトラップ)、Execute2回のヒントで判断が付きました。

コメント文字数が無くサンプル掲載不可です (;;)

mdbでVB並みに書けるとは感激です。
でもmdbユーザがこんなに意見がキツイと思いませんでした。

ベストアンサー以外の回答

1〜3件/3件中

並び替え:回答日時の
新しい順
|古い順

pro********さん

2008/4/1610:39:51

> 注文をつける時間があれば 内容を詳しく説明するか 勉強してください

わー怖い。(ataddprocerさんのマネ)

> できないなら
> 「Accessではできません」ではなくて 「私にはできません」とちゃんというべきです
> 言い訳ではなく事実として伝える責任があります

回答者の chirinuruwowahe さん。
この質問者の ms_strucquelang さんは最初から 「Accessではできません」と言いたくないので
知恵袋に質問を寄せて来た事を質問の中に書いてあります。

自分のスキルを棚にあげてアプリのせいにする人が多い中で十分立派なのではないでしょうか?
chirinuruwowahe さんの回答履歴を見ましたが
サーバー系の質問に検証もせず回答している方に質問者にトドメを刺す権利などありません。

> 調べて 理解して 検証して 安全策を講じて....
> 調べていない人が 理解可能かわかりませんが 検証はできるのでしょうか

コマンドタイプに4番(&H0004) 使うなんて凡ミスどころか初心者な回答をしている人の割にはよく人に言えますよね?(爆笑)
私も 5~6年前に初めてやった時に adCmdStoredProc すぐ思いついて実行したら即秒殺されたし。
当時の上司のSEからは「それみんな最初に思いつくけど、そんな簡単にストアドって使える訳ないんだよねー(笑)」と言われ凹んでついでにスキル低いのバレて反省した。

> 上記は今後検索する人のために回答しました

誤回答なんかいらないっつーの(笑)
それこそ Google 検索に引っ掛かっちゃうから削除した方が良いとは思いますけど。
でも先に回答締め切られたら消せないから早目にしないとダメですよね?

ataddprocerさんが指摘した通りですが
chirinuruwowahe さんの回答って履歴を見たら
いかにも Google検索しただけのパクリが多くて
JavaScript やHTML みたいにローカルPCで再現可能なもの以外は
ほとんど自分で検証せずにどこかのURLのコピーで済ましてる事多いみたいですね?

おそらくサーバー立ててないんで面倒なんでしょ?
Excelと VBA、Accessと VBA、JavaScriptとHTMLとレンタルサーバ利用の回答にかたよっているのはそのせいなんでしょう。

ataddprocerさんもパクられるのが嫌だから
今回はサンプルコーディング出さずにギリギリで理論での説明に終わっているフシもあるし。

自分は今 .Netの仕事してるけど競合書き込み防止のトランザクション部分などでADOがたまに必要になって当然ストアドを動的に書くサンプルなど手持ちで持っています。

でもそのサンプルってADO系の奥義に近いし
ちょっとした更新クエリで数分掛かるAccessのオペさん達に格差をつける
根幹の部分と言うかエンジニアの生命線みたいなもんだから
こんなYahoo!知恵袋なんかでは出てこないんじゃないかな?普通。
逆に出されたらつまんないエンジニアのあたしが困る(笑)

でも上手なのはataddprocerさんの説明って chirinuruwowahe さんにGoogられないようにトラップあるしねー (笑)
CreateProcedure なんか検索しても回答出ねえっつーの。みたいな?☆・・・・・(笑)
ataddprocerさん、ちょっと卑怯な感じがして性格的には好きです。

参考までに動的にやる以外の方法としては
テーブルの全フィールドを Loop して動的にConnectionオブジェクト上のParameter を作成して
Executeするしかないと思います。

ストアド側では全部のフィールド列用のパラメータを事前に配置し Case文で制御するしかないか?と。

ですからテーブルにフィールド追加された時には常にストアド内のパラメータ宣言部を変更しなければならず
仮ストアドを動的にクリエイトしてそれを Executeするのが一番かと思います。

勿論コマンドタイプは 4番ではなく1番でね☆(爆笑)

以上オバサンのたわ言でした。
さって仕事しよっ♪

chi********さん

2008/4/1514:33:08

http://www6.plala.or.jp/MilkHouse/ado/contents403/contents40301.htm...
http://www.d3.dion.ne.jp/~pashica/kobetu/k20070620.html
http://www.sqlpassj.org/bunkakai/begin/series/s05/0505.aspx
http://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q1012937025

set myCon = CreateObject("ADODB.Connection")
myCon.Open ("Provider=SQLOLEDB.1;Data Source=server;Password=;User ID=sa;Initial Catalog=custmer3")
set myCmd = CreateObject("ADODB.Command")
myCon.CursorLocation = 3
myCmd.ActiveConnection = myCon
myCmd.CommandType = &H0004
myCmd.CommandText = "直SQL"
myCmd.Execute

注文をつける時間があれば 内容を詳しく説明するか 勉強してください

できないなら
「Accessではできません」ではなくて 「私にはできません」とちゃんというべきです
言い訳ではなく事実として伝える責任があります

忠告しますが やめておくことをすすめます 後悔することになります
理解せずに結果だけを得ようとすると 大きなミスをすることになります
調べて 理解して 検証して 安全策を講じて....
調べていない人が 理解可能かわかりませんが 検証はできるのでしょうか
安全にすすめるためにはどうすべきかわかっているでしょうか

もう一度警告します やめておいてください



上記は今後検索する人のために回答しました
SQL Server
ストアドプロシージャ
ADO
Access
VBA

hea********さん

2008/4/1508:59:21

ご意志に沿わない返答であることは承知の上ですが...
膨大なデータ数、処理時間に制限、変数の数が可変、など条件が複雑かつクリティカル過ぎます。
少なくともACCESS初心者を自認する方が取り組むレベルの問題ではありません。
仮に数千万件のデータがあらぬ内容に書き換わった場合、外注費用など比較にならないくらいの損失を生じます。そのような事態となってもここでは誰も責任を取れません。
綿密なテスト含め、社内の識者か業務を理解している外注さんなど然るべき業者に開発依頼するのが吉です。

みんなで作る知恵袋 悩みや疑問、なんでも気軽にきいちゃおう!

Q&Aをキーワードで検索:

Yahoo! JAPANは、回答に記載された内容の信ぴょう性、正確性を保証しておりません。
お客様自身の責任と判断で、ご利用ください。
本文はここまでです このページの先頭へ

「追加する」ボタンを押してください。

閉じる

※知恵コレクションに追加された質問は選択されたID/ニックネームのMy知恵袋で確認できます。

不適切な投稿でないことを報告しました。

閉じる