ExcelからAccessのクエリを呼び出したい

質問

約20,000レコードほどのデータを一気にExcelのVBA上の選択SQLでEditとUpdateで処理しようと思ったのですが、一気に処理できるレコード数に制約があるようでエラーになりました。

Access内のクエリだとOKみたいですがExcelからAccessのクエリを呼び出す方法がわかりません。

どうしたらいいのでしょうか？

よろしくお願いします。

バージョンは2002です。

回答No.6

imogasi

もう既回答で解決しましたか。
ーーー
質問文章表現で不思議におもう箇所があります。
●この質問はVBA（エクセルの）質問ですか
操作でも、データー外部データの取り込みークエリの編集で
クエリ（SQL）が使えるし、SQL文コード（ステートメント）の直接加工できるので、どちらの質問か迷って良くわからない。
回答補足を読むとVBAらしい。
＞ExcelからAccessのクエリを呼び出したい
クエリとはSQL機能のクエリ機能ことですか。テーブルから派生したクエリデータのこと。
データはアクセスのテーブル・クエリのデータですか、エクセルですか
＞ExcelのVBA上の選択SQLでEditとUpdateで処理しようと思ったのですが
エクセルVBAでもできるのでしょうが、なぜエクセルでそこまでやるのか。
「EditとUpdate」はSQLステートメントではなく、ADO・DAOの中で出てくる言葉なんですが、EditとUpdateを編集・更新という意味で一般的に言っているのですか。
＞ExcelからAccessのクエリを呼び出す方法がわかりません。
マクロの記録をとれば、操作で実行された、SQL文ほかがコードで出ます。
ーーー
どうもユーザー関数がうまく走らないというのが質問内容らしいが、それなら、質問を改めて、出したら。
その際はエラー原因の質問なので、関係しそうなコードぐらい出して
質問すべきです。
＞処理できるレコード数に制約があるようでエラー
これも２万件では、見当違いと思うが、断定的に言ってますが、自分の意見を振り回さず、質問したら。
思いついた・頭にある言葉をちりばめるのは、質問の焦点がぼやけると思います。
以上質問する際のご参考にしてください。

補足

>マクロの記録をとれば、操作で実行された、SQL文ほかがコードで出ます。

更新クエリも取れるんですかね？

回答No.5

nicotinism

肝心なことを忘れてました (^_^;)
ご質問者さんのオリジナルのモジュールを提示できませんか？

Accessの関数をExcelへ持ってきて
Excel内だけで完結させた方が良いような気がします。

補足

Public Function Get_Url2(BaseUrl As String, Param As String, Key As String) As String
Dim pUrl As String

pUrl = Replace(BaseUrl, "&ap=Param", "&ap=" & UrlEncodeEUC(Param & pkey))
Get_Url2 = pUrl

End Function

UrlEncodeEUCはこれです。

http://www.geocities.co.jp/SilkRoad/4511/vb/urlenc.htm

何をしようとしているかというと、UrlのフィールドがあってそこにUrlエンコードしたパラメータをくっつけたいのです。

まぁ、テーブルに保存しなくてもいいのですが・・

また、アップデートしたデータは元データとは別のフィールドに保存しようとしています。

なのでrs!フィールド8 = Replace(rs!フィールド8, "区", "○")でなくて
rs!フィールド9 = Replace(rs!フィールド8, "区", "○")って感じでしょうか・・・

回答No.4

nicotinism

ご質問者のオリジナルのモジュールは提示できませんか？
当方で１２万件のレコードセットを回しましたがエラーにはなりませんが？
Sub test()
'要参照設定Microsoft DAO 3.x Object Library
Dim db As DAO.Database
Dim rs As DAO.Recordset
'フィールド8
Set db = dbengine.OpenDatabase("g:\zipxp.mdb")
Set rs = db.OpenRecordset("select * from ken_all", dbOpenDynaset, dbDenyWrite)
BeginTrans
Do Until rs.EOF
rs.Edit
rs!フィールド8 = Replace(rs!フィールド8, "区", "○")
rs.Update
rs.MoveNext
Loop
CommitTrans
rs.Close: Set rs = Nothing
db.Close: Set db = Nothing
End Sub

追伸
Access同士（mdb から 別のmdb中の関数自作呼び出し）なら
参照設定を行って出来るのですがExcelからAccessはさすがに無理のようです。
方法が有るのかもしれませんが・・

補足

ADOでやっているのですがやっぱり同じエラーですね。

8940レコード目で止まりました。

回答No.3

zap35

＃０２です。＃０１さんの補足にあるSQLエラーは見直していただくとして…

共有ロック数の上限は規定値では9,500程度のようですから、補足にあるようにレジストリ（MaxLocksPerFile）を直すか、
　DAO.DBEngine.SetOption dbmaxlocksperfile,25000
のようにSetoptionメソッドを記述する必要があります。

これらの手段が難しいのであれば、9,500件以下になるようにsqlの条件を見直すしかないと思います。

回答No.2

zap35

その件数の制約については分かりませんが、COMMIT命令は適時入れていますか？

通常DBのレコードを一括で更新するときに件数でエラーとなる場合は、ROLLBACKできるレコード件数が制約事項となるケースがほとんどだと思います。

例えば１０件処理したら、１回COMMITを発行ようにすれば、今のEXCEL VBAでも処理できるかもしれませんよ。

直接の回答にならなくてすみません。

補足

『ファイルの共有ロック数が制限を超えています』のエラーですね。レジストリを触ると解決できるようなのですが、ちょっと触れないもので・・・。

commitとrollbackですが使ったことがないのです。サンプルがあるのですがちょっと、意味合いが違っているような感じでした。

何かいいのをご紹介願えないでしょうか？

回答No.1

nicotinism

こんな風かも？
Sub test()
'要参照設定Microsoft DAO 3.x Object Library
Dim db As DAO.Database
Set db = dbengine.OpenDatabase("mdbへのフルパス")
db.Execute "アクションクエリ", dbFailOnError
db.Close: Set db = Nothing
End Sub

お礼

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

自作関数をSQLの中に入れているのですが『式に未定義関数があります。』で止まりました。

そもそも選択SQLで1レコードずつ処理しようとしたのはこのエラーのためだったのですが、Excelから直接、Access内のクエリーを呼び出してもダメだったんですね。。。