ExcelのVBAでAccess操作です

質問

タイトルの操作で、エクセルシートのセルのコード番号をアクセスの
データベースのIDのフィールドから検索し、アクセスのその行を削除または、修正をしようとしています。
アクセスのIDのフィールドには重複するものは存在しません。
現在For～Nextで一行づつ見る方法まできましたが、レコード数が増えた場合かなりの時間がかかります。

エクセルのVBAの記述で
TROW＝検索範囲.Find(検索文字列,Lookat:=xlWhole).Row
に換わる記述はどういうものがあるのでしょうか？

エクセルのVBAで処理をしたいのですがよろしくお願いします。
また、For～Nextの記述の場合、変更しようとするとLockedがYesになっているため変更できない旨のメッセージが出ます。

アクセスのテーブルにロックがかかっている意味だと思うのですが、
Unprotectのような記述もあれば教えていただきたいと思います。
アクセスは全く初めてで壁にぶち当たっています。
どうぞ宜しくお願いします。

回答No.2

fumufumu_2006 · ベストアンサー

テーブル1で、IDが1,3,5,7,9のデータを削除するクエリは
DELETE FROM テーブル1 WHERE ID In (1,3,5,7,9)
テーブル1で、IDが1,3,5,7,9のデータのフィールド1を'ABC'に更新するクエリは
UPDATE テーブル1 SET フィールド1 = 'ABC' WHERE ID In (1,3,5,7,9)
A列のA1から行方向に対象IDがある場合(空白行まで）は、
Range(Range("A1"), Range("A1").End(xlDown))
が対象IDの範囲です。
この範囲の値をカンマ区切りの文字列にするには
Join(Application.Transpose(Range(Range("A1"), Range("A1").End(xlDown))),",")
で得られます。（Excel2000の場合は、Application.Transposeの最大範囲は5461個）
ということで、A1から行方向に対象IDを用意してください。
更新クエリか削除クエリのどちらかを有効にしてください。
[ツール][参照設定]で[Microsoft ActiveX Data Objects 2.? Library]にチェックをしてください。

Sub sample()
Dim cnn As New ADODB.Connection
Dim id As String
Dim sql As String
id = Join(Application.Transpose(Range(Range("A1"), Range("A1").End(xlDown))), ",")
'sql = "DELETE FROM テーブル1 WHERE ID In (" & id & ")" '削除クエリ
sql = "UPDATE テーブル1 SET フィールド1 = 'ABC' WHERE ID In (" & id & ")" '更新クエリ
cnn.Provider = "Microsoft.Jet.OLEDB.4.0"
cnn.Open "c:\db1.mdb"
cnn.Execute sql
cnn.Close
Set cnn = Nothing
End Sub

お礼

ありがとうございます。
今までエクセルのシートでデータベースを組んでいましたが、
アクセスへの接続がこんなにもスムーズだと初めて知り感激しております。
'sql = "DELETE FROM テーブル1 WHERE ID In (" & id & ")" '削除クエリ
sql = "UPDATE テーブル1 SET フィールド1 = 'ABC' WHERE ID In (" & id & ")" '更新クエリ
この部分が重要なんですね
挑戦してみたいと思います。
ありがとうございました。