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 & ")" '更新クエリ
この部分が重要なんですね
挑戦してみたいと思います。
ありがとうございました。

回答No.1

Masa2072

ご希望の回答ではないかもしれませんが参考になれば。

ExcelのSheet1のA列にID,B列に処理種別、C列にフィールド名、D列にデータをそれぞれ入れたリストがあります。
100 | D | 　　　　　 |
201 | E | フィールド2 | ABC
　・
　・
　・
　こんな感じで・・、B列の処理種別がDなら削除、EならC列の指定フィールドをD列のデータで変更するマクロが以下のサンプルです。
※ ACCESSのファイルはC:\SAMPLE.MDB、テーブル名はTABLE1
Sub　Sample()
　　Dim Cnn As New ADODB.Connection
　　Dim strSQL As String
　　Dim Siji As Object
　　Dim GYOU As Long
　　Dim ID As String
　　Dim SYORI As String
　　Dim FiledName As String
　　Dim NewData As String

　　'ACCESSデータベースへのADO接続
　　Cnn.ConnectionString = "Driver={Microsoft Access Driver (*.mdb)};DBQ=C:\sample.mdb"
　　Cnn.Open

　　For Each Siji In Sheets("Sheet1").Range("A:A")
　　　　If Siji.Value = "" Then
　　　　　　'A（ID)が空なら終了
　　　　　　Cnn.Close
　　　　　　Set Cnn = Nothing
　　　　　　Set Siji = Nothing
　　　　　　Exit Sub
　　　　End If

　　　　ID = Siji.Offset(0, 0)
　　　　SYORI = Siji.Offset(0, 1)
　　　　FiledName = Siji.Offset(0, 2)
　　　　NewData = Siji.Offset(0, 3)

　　　　Select Case SYORI
　　　　Case "D"　　'レコードの削除
　　　　　　strSQL = "DELETE * FROM TABLE1 WHERE ID = '" & ID & "'"
　　　　Case "E"　　'レコードの編集
　　　　　　strSQL = "UPDATE TABLE1 SET " & FiledName & " = '" & NewData & "' WHERE ID = '" & ID & "'"
　　　　Case Else
　　　　　　'D、E以外の場合はSQL文を発行しない
　　　　　　strSQL = ""
　　　　End Select

　　　　If strSQL <> "" Then
　　　　　　Cnn.Execute (strSQL)　　'SQL文の実行
　　　　End If
　　Next

　　Cnn.Close
　　Set Cnn = Nothing
　　Set Siji = Nothing
End Sub

お礼

ありがとうございます
ID行を探しにいくのではなく、指定して削除、または編集ですね。
　　　　Case "D"　　'レコードの削除
　　　　　　strSQL = "DELETE * FROM TABLE1 WHERE ID = '" & ID & "'"
　　　　Case "E"　　'レコードの編集
　　　　　　strSQL = "UPDATE TABLE1 SET " & FiledName & " = '" & NewData & "' WHERE ID = '" & ID & "'"
この部分を使ってチャレンジしたいと思います。