Access起動中にエクセルからADOでデータの更新ができない

質問

こんばんは。原因がわからないので質問させてください。

SQLサーバーのデータをAccessのmdbへリンクテーブルしています。
（SQLサーバー　→　Access）
全て一つのパソコンで行なっています。（LANには接続していません）

Accessのmdbファイルを起動している時に
ExcelからSQLサーバーのAccessにリンクしている上記のテーブルへ
ADOを使ってデータを追加しています。
（Excel　→　Access）　

しかし、「rs.Update」の部分で
　「ODBC--リンク　テーブル'テーブル1'での更新に失敗しました。」
となってしまいます。

なので、一度mdbファイルを落として、コードを再開すると通るので
ADOでExcel　→　Accessにデータを追加し終わってから再度mdbファイルを立ち上げています。

また同じ状態（mdbファイルを起動）で
Excel　→　SQLサーバー　へ更新クエリをしようとすると「時間切れになりました」となります。
（こちらもmdbファイルを落とすと再開できます。）

いちいちmdbファイルを落とすのめんどうなのですが
回避する方法はありますか？

cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & "C:\Users\Documents.test.mdb"
rs.Open "テーブル", cn, adOpenStatic, adLockPessimistic
rs("フィールド") = True
rs.Update '保存
rs.Close: Set rs = Nothing

というコードを書いています。
Accessを落とせば正常に動きます。

ご教授よろしくお願い致します。

回答No.1

DexMachina · ベストアンサー

私自身は、Access単体(ユーザーは複数ですが)でしか
使用していないのですが(汗)

> Accessを落とせば正常に動きます。

提示されたコードを見る限り、そのテーブルを「排他」で
開いているようです。
そのため、mdbとExcelからの同時アクセスが制限されて
いるものと思います。
(当該テーブルが他のプログラムで開かれていると、排他
　展開ができないため、エラーとなる、と：
　例え操作している人間が一人で、単一PC端末であっても、
　複数の経路での接続は、「排他」の制限対象になります)

ですので、排他モードの指定を共有モードに変更すれば、
解決するのではないかと思います。

具体的には、
【現在】
rs.Open "テーブル", cn, adOpenStatic, adLockPessimistic
【修正】
rs.Open "テーブル", cn, adOpenStatic, adLockOptimistic
になります。

お礼

ありがとうございます。
adLockPessimisticをadLockOptimisticへ変えたら
（Excel　→　Access）　をできるようになりました。

回答No.2

DexMachina

No.1です。

> （Excel　→　Access）　をできるようになりました。

・・・すみません、すっかり遅くなってしまいましたが
> Excel　→　SQLサーバー　へ更新クエリをしようとすると
> 「時間切れになりました」となります。
の方が未解決ということでしょうか(汗)

だとしたら、・・・SQL Serverは使っていないので、そちらは
ちょっとわかりかねますが、Access側で確認しておくべき点
だけ、挙げてみます。

【Access2003以前】
1)Accessで当該mdbを起動
2)メニューで「ツール(T)→オプション(O)」を選択
3)『オプション』ダイアログで『詳細』タブを選択
4)以下の設定を変更
　『既定の開くモード』　：　「共有モード(S)」
　『既定のレコード ロック』　：　「編集済みレコード(D)」
　『レコード レベルでロックして開く(O)』　：　オン
5)Okボタンをクリックしてダイアログを閉じる
6)念のためAccessを一旦終了して、動作をテスト

以上です。
・・・参考になるかどうか・・・(汗)

お礼

どうもどうもすいません。

うーん
おっしゃるとおりにやってみましたがうまくできませんでした。
でも参考になりました。
ご回答ありがとうございます。