VB2010,Access2000と接続し、UPDATE文を実行する構文なのですが、
Try
Dim CnnS As New OleDbConnection(ACstr & " Data Source=" & DataMdb
& ";" & ACPassS)
Dim SQLCm As OleDbCommand = CnnS.CreateCommand
CnnS.Open()
For I= 1 to 100
SQLCm.CommandText = "UPDATE 文"
SQLCm.ExecuteNonQuery()
Next
CnnS.Close()
Catch ex As Exception
MessageBox.Show(ex.Message, "Access接続エラー")
Cursor.Current = Cursors.Default 'カーソル通常
Exit Sub
End Try
うまくアップデートできない事があります。
恐らく、For 〜 Next の中で書き込む前に次のUPDATE文が読み込まれて
更新できないときがあるのではと思っています。
（デバッグで1行ずつF8キーで送っていくと、うまくいきましたので）
Accessに連続で何度も書き込む際、何かよい方法はないでしょうか？
よろしくお願いいたします。
mdb に対して書き込みを行う場合には、
「明示的にトランザクションを作成する」べきです。
理由はこちら。
[Jet Engineのキャッシュとその制御]
http://www.canalian.com/workshop/access/JetCache.html
ADO.NET でのトランザクションは、BeginTransaction メソッドで
OleDbTransaction クラスを得て、最後にその Commit メソッドを
呼ぶという形になります。
あるいは、他の接続が無いなら「排他モードで開く」という手もあります。
同じファイルを同時に開けなくなるので、あまり使うことは無いかも
知れませんが、一応こんな感じ。
Const adModeShareExclusive As Integer = 12
Dim ob As New OleDbConnectionStringBuilder()
ob.Provider = "Microsoft.ACE.OLEDB.12.0"
ob.DataSource = filePath
ob("Mode") = adModeShareExclusive
ob("Jet OLEDB:Database Password") = password
Dim conStr As String = ob.ConnectionString
Dim conn As New OleDbConnection(conStr)
conn.Open()
以下のようにトランザクションを明示したのですが、
やはり特定のレコードが毎回更新されません。
何か問題がありますでしょうか？
Dim CnnS As New OleDbConnection(アクセス接続文字列)
Dim SQLCm As OleDbCommand = CnnS.CreateCommand
CnnS.Open()
Dim trans As OleDb.OleDbTransaction = CnnS.BeginTransaction()
SQLCm.Transaction = trans
Try
For I= 1 to 100
SQLCm.CommandText = "UPDATE 文"
SQLCm.ExecuteNonQuery()
Next
trans.Commit()
CnnS.Close()
Catch ex As Exception
trans.Rollback()
MessageBox.Show(ex.Message, "Access接続エラー")
Cursor.Current = Cursors.Default 'カーソル通常
Exit Sub
End Try
ExecuteNonQuery()のタイミングでトランザクションを発行したらどうですか？
編集 削除異種DB間クエリ(ODBCリンクテーブル等)を
使っているわけではないのですよね。
> For I= 1 to 100
> SQLCm.CommandText = "UPDATE 文"
パラメータクエリを使った方が良いですよ。
> やはり特定のレコードが毎回更新されません。
原因箇所を絞り込むため、新規に空のmdbを用意しなおして、
そこに今回と同じ構造のテーブルを用意しておき、その中に
「特定のレコードだけ」を追加してみてください。
そうした最低限の環境に UPDATE した場合にも、
今回の現象が再現されるのでしょうか?
> 何か問題がありますでしょうか？
ステップ実行では問題ないということで、原因自体は
先に紹介した URL の内容っぽいのですけれどね。
再現性についての確認ですが、今回の現象では
常に同じレコードが更新されないのでしょうか?
もし、常に特定のレコードのみが更新されない)のであれば、
それぞれのクエリの実行結果に問題が無いかを確認するため、
100 回の更新処理それぞれについて、ExecuteNonQuery メソッドの
戻り値を取得していき、更新予定の件数と同じ値が返されるのか
記録を取ってみると、何か分かるかもしれません。
＞パラメータクエリを使った方が良いですよ。
今回は100回ループ固定ですが、実際は状況に応じてもっと多くなる場合があります。SQL文のパラメータの文字数制限に引っかかってしまうので使用していません。
＞そうした最低限の環境に UPDATE した場合にも、
今回の現象が再現されるのでしょうか?
再現されました。そのレコード自体に問題があるのでしょうか？
ちなみにUPDATEするフィールドはメモ型ですが、それは関係ないですか？
>＞そうした最低限の環境に UPDATE した場合にも、
>今回の現象が再現されるのでしょうか?
>再現されました。
引用符「>」は、相手の発言行すべてに付けましょう。
で、その再現したというのは、更新されないという件だけでなく、
ステップ実行すると正常に書き込まれる、という点までも
再現したということでしょうか。
また、その時の ExecuteNonQuery の戻り値はどうなっていましたか?
> もっと多くなる場合があります。SQL文のパラメータの文字数制限に
…どういう意味でしょうか?
ループ回数が増えると文字数制限にひっかるのですか?
> そのレコード自体に問題があるのでしょうか？
こちらには再現できる環境が無いので、原因までは分かりません。
機密情報等を取り除いた最低限の検証データとソースを、
SkyDrive 等にアップロードできないでしょうか。
更新失敗の要因として良く見かけるのは、
・そもそも、コミットし忘れていた。
・開発用DBと本番用DBが別ファイルで、両者を見間違えていた。
・mdbをソリューションの一部として配置していたため、
ビルド時に更新前の mdb が再配置されてしまっていた。
・本来であれば更新されるべきデータだと思い込んでいたが、
実はデータに予定外の不可視文字が入っていたので、
WHERE での更新対象から外れてしまっていた。
・パラメータ化した際、ADO.NET が日付時刻型がを日付型に
誤ってマッピングされてしまう問題に引っ掛かり、時刻情報が
欠落することで、検索条件に合致しなくなっていた。
・mdb の照合順序によっては、文字列比較(LIKE 演算子、= 演算子など)で
特定の文字が無視されるため、それによって抽出されなかった。
・フィールド名に予約語が含まれていたため、フィールド名を
角括弧([〜])で囲まないと更新されなかった。
などの問題が思い当たりますが、今回のようにステップ実行で結果が
変わるというと、やはり、先の遅延書き込みの問題ぐらいしか
想像できませんでした。
> ちなみにUPDATEするフィールドはメモ型ですが、それは関係ないですか？
WHEREや副問い合わせで使うのではなく、SET 句に使うだけであれば
問題ないと思います。
メモ型として特殊な設定があるとすれば、
・4KB 以下の西欧言語文字をシングルバイトで保存するための
“[Unicode 圧縮/Unicode Compression]プロパティ”。
・編集履歴を保持させる“[追加のみ/Append Only]プロパティ”。
(履歴は ColumnHistory メソッドで取得。)
ぐらいですが、今回の件とは関係なさそうですし。
＞また、その時の ExecuteNonQuery の戻り値はどうなっていましたか?
更新できないレコードの分だけ0になっていました。
＞ループ回数が増えると文字数制限にひっかるのですか?
ループで１件ずつ処理するよりWHERE句で一括で更新したほうがよいという意味と思ったので、WHERE以降の条件A or B or C or・・・・と1000件分のSQL文で処理しようとした場合Access2000では、WHERE以降のWHERE 、OR または HAVING 節での AND の数の上限が40という記事がありました。
文字数制限というのはちょっと表現が違いました。
http://www.mahoutsukaino.com/ac/ac2000/ac2000/sonota/sonota02.htm
アップロードの件はもう少し調査してみてから考えてみます。
毎回丁寧な回答ありがとうございます。
>＞また、その時の ExecuteNonQuery の戻り値はどうなっていましたか?
> 更新できないレコードの分だけ0になっていました。
今回は、ステップ実行しても 0 になったということですか?
だとしたら、その時点のデータもしくは条件文のいずれかに問題があるように
思います。0 件更新時の SQL 文を、そのまま Microsoft Access 上から
実行した場合は、何件更新されるのでしょうか?
> ループで１件ずつ処理するより
そもそも何件ずつ更新する処理なのかは、質問文からは読み取れなかったりします。(^_^;)
どんな SQL を発行しているのかすら分からないのですし。
いずれにせよ、パラメーター化しようとしまいと、
ループ回数は 1000 回のままとなるはずですし、
むしろ SQL 全体としては短くなるはず。
> WHERE句で一括で更新したほうがよいという意味と思ったので、
パラメータクエリーを推奨したのは、
・パラメータ化することで、SQL Injection 問題も誘発せずに済む。
・パラメータの型を明示できるため、型変換問題を生じにくい。
というメリットがあるためです。
繰り返し回数が多い場合においては、指定する値が違うだけで
SQL 自体は同じ構文と言う状態なら、パラメータ化した方が
処理効率が良いはずです。
逆に、VB 側で動的に WHERE 条件を作らざるを得ないような
複雑な処理の場合は、パラメーター化しにくいのですけれどね。
> WHERE以降の条件A or B or C or・・・・と1000件分の
> SQL文で処理しようとした場合
A〜Z だと 26 条件になってしまうというツッコミはさておき。
個々の A や B や C がどんな処理なのか分からないので、
そのやり方が適切かどうかは分かりませんが、SQL があまりに
長文になるという状況は好ましく無いでしょうね。
＞0 件更新時の SQL 文を、そのまま Microsoft Access 上から
＞実行した場合は、何件更新されるのでしょうか?
この場合は1件更新されます。
なので条件文には問題がないと思います。ステップ実行では更新されますし。
それでは、試していないのですが、以下のようにExecuteNonQuery()のタイミングでトランザクションを発行したらどうですか？
Dim CnnS As New OleDbConnection(アクセス接続文字列)
Dim SQLCm As OleDbCommand = CnnS.CreateCommand
CnnS.Open()
Try
For I= 1 to 100
Dim trans As OleDb.OleDbTransaction = CnnS.BeginTransaction()
SQLCm.Transaction = trans
SQLCm.CommandText = "UPDATE 文"
SQLCm.ExecuteNonQuery()
trans.Commit()
Next
Catch ex As Exception
trans.Rollback()
MessageBox.Show(ex.Message, "Access接続エラー")
Cursor.Current = Cursors.Default 'カーソル通常
Exit Sub
Finally
CnnS.Close()
End Try
ExecuteNonQuery()のタイミングでトランザクションを発行してみましたが、
結果は同じく更新されませんでした。
> ステップ実行では更新されますし。
ANSI-89 SQL モードと ANSI-92 SQL モードの違いというわけでも無さそうですね。
では、JET 4.0 Service Pack 8 (あるいはそれ以降の Hotfix)を
適用してみるというのはどうでしょうか。
(自動アップデート等で適用済みとは思いますが、念のため)
[ACC2000: Records Newly Inserted into Access Database Are Not Immediately Available When Reselecting]
http://support.microsoft.com/kb/245676/
→ This problem was corrected in Microsoft Jet 4.0 Service Pack 5 (SP5).
本来であれば、接続文字列に、
Jet OLEDB:Flush Transaction Timeout=5;
Jet OLEDB:Implicit Commit Sync=True;
Jet OLEDB:User Commit Sync=True;
Jet OLEDB:Transaction Commit Mode=1;
などを加えておきたいところなのですが、試してみた限りでは、
この設定は ADODB では使えるものの、ADO.NET では使えないようで。
http://www.canalian.com/workshop/access/JetCache.html
http://msdn.microsoft.com/en-us/library/windows/desktop/ms681754.aspx
遅延書き込みの問題を解決できないようであれば、不本意ですが、
DAO の利用を検討した方が良いかも知れません。
あとは、更新後に JRO.JetEngineClass.ResetCache メソッドを
呼び出すぐらいでしょうか。
更新できないレコードのとき、ExecuteNonQuery の戻り値が0になりますので
0のときだけ
System.Threading.Thread.Sleepで数秒中断し、再度ExecuteNonQueryを実行することにより、とりあえず更新はできました。
何か強引であんまり納得はしてないですが、DAOの利用となると、システム内でADO.NETを多数使用しているので、大幅な変更となるので避けたいのでとりあえず、解決ということにします。
いろいろとありがとうございました。