none
Excelデータベースアクセスにて書込みできない RRS feed

  • 質問

  • お世話になっております。

    表題の通り、書込みを行うと異常が発生します。

        Set con = CreateObject("ADODB.Connection")
        Set res = CreateObject("ADODB.Recordset")
        con.Provider = "Microsoft.ACE.OLEDB.12.0"
    
        con.Properties("Extended Properties") = "Excel 12.0;HDR=YES;IMEX=1;ReadOnly=False"
        con.Mode = 3
        con.Open ThisWorkbook.Path & DataPath
        
        query = "update [" & ShtOrderData & "$] set 状態=1 where id=1"
        
        res.Open query, con, 1, 1

    上記を実行すると【更新可能なクエリであることが必要です。】と表示されます。

    試したこと:

    ・ReadOnlyを付与
    ・Mode=3を指定
    ・データを別ファイルに分け、アクセス許可を付与

    というか、そもそもDBを読取り専用で開く意味、、

    2019年4月1日 1:40

回答

  • あっと、先のレスで提示したページは誤った定数値が記述されていましたね、済みません。代わりにこちらなどを参照してください。

    http://yasusigi.net/iasdocs/aspdocs/ref/comp/daprop04.htm

    編集後とかいうのも言葉がおかしくて、行を直ちにロックする(他者が更新することがあり得なくなる)か、Updateした時に初めてロックする(行の値を調べてから更新するまでの間に他者が更新することがあり得る)か、という感じです。

    排他相手がいないなら別にどれでも機能的には問題ない(パフォーマンスの差はあれ)ですが、adLockReadOnlyだけは明らかに機能的に問題があるのでそれ以外を指定することになるでしょう(そしてLockTypeを指定しない場合はadLockReadOnlyが既定値のようですね)。

    さて、改めて私の方で検証してみたところ、以下のようなコードで更新できました。

    ' xlsxpathに更新対象のxlsxパスを格納しておく Const adOpenKeyset = 1 Const adLockOptimistis = 3 Const adCmdText = 1 Dim con, rs, query Set con = CreateObject("ADODB.Connection") Set rs = CreateObject("ADODB.Recordset") con.Provider = "Microsoft.ACE.OLEDB.12.0" con.Properties("Extended Properties") = "Excel 12.0;HDR=YES" con.Open xlsxpath query = "UPDATE [Sheet1$] SET age = 18 WHERE id = 1" rs.Open query, con, adOpenKeyset, adLockOptimistis, adCmdText ' なお直接SQL投げるならRecordset使わずcon.Execute queryだけでも。 con.Close Set rs = Nothing Set con = Nothing

    どうも、Extended Propertiesの中にIMEX=1が含まれているとUPDATEに失敗するようです。

    ただ、IMEX=1を指定しない場合列の型の扱いが面倒になるという話も聞くので、それで問題ないか検証する必要がありそうです。

    • 回答としてマーク コーベル 2019年4月1日 4:48
    2019年4月1日 4:26

すべての返信

  • ADODB.RecordSetのOpenメソッドの第4引数はLockTypeで、1は定数adLockReadOnlyを指すので、そりゃ更新できないんじゃないかな、と。
    2019年4月1日 2:29
  • Hongliangさん
    ご回答ありがとうございます。

    res.Open query, con
    
    con.Execute (query)

    共に試しましたが、同様に【更新可能なクエリであることが必要です。】
    と出力されています(TT)

    2019年4月1日 2:37
  • 「よく分からないから指定しない」ではなくて、「どんな値があるか」を調べる癖をつけた方がよろしいかと思います。

    http://accessvba.pc-users.net/ado/open_record.html

    競合する処理がないなら読み取り専用でなければOptimisticでもPessimisticでもどちらでもいいかな。

    2019年4月1日 2:47
  • 参考HPありがとうございます。

    引数の指定ですが、調査しております。

    参考資料ですが

    adLockPessimistic 1 レコード単位で排他的ロックを行います。編集直後にレコードをロックします。

    元の指定値は、こちらで【編集後にロック】とあります。
    また、こちらは排他制御(自身以外が操作できなくする)という認識ですが違いますでしょうか?
    DBファイルはExcelファイルでありローカルに保存されています。

    全ての引数を設定していますが、書けません。

    本件は、私だけが書込めていないのでしょうか。

    2019年4月1日 3:23
  • あっと、先のレスで提示したページは誤った定数値が記述されていましたね、済みません。代わりにこちらなどを参照してください。

    http://yasusigi.net/iasdocs/aspdocs/ref/comp/daprop04.htm

    編集後とかいうのも言葉がおかしくて、行を直ちにロックする(他者が更新することがあり得なくなる)か、Updateした時に初めてロックする(行の値を調べてから更新するまでの間に他者が更新することがあり得る)か、という感じです。

    排他相手がいないなら別にどれでも機能的には問題ない(パフォーマンスの差はあれ)ですが、adLockReadOnlyだけは明らかに機能的に問題があるのでそれ以外を指定することになるでしょう(そしてLockTypeを指定しない場合はadLockReadOnlyが既定値のようですね)。

    さて、改めて私の方で検証してみたところ、以下のようなコードで更新できました。

    ' xlsxpathに更新対象のxlsxパスを格納しておく Const adOpenKeyset = 1 Const adLockOptimistis = 3 Const adCmdText = 1 Dim con, rs, query Set con = CreateObject("ADODB.Connection") Set rs = CreateObject("ADODB.Recordset") con.Provider = "Microsoft.ACE.OLEDB.12.0" con.Properties("Extended Properties") = "Excel 12.0;HDR=YES" con.Open xlsxpath query = "UPDATE [Sheet1$] SET age = 18 WHERE id = 1" rs.Open query, con, adOpenKeyset, adLockOptimistis, adCmdText ' なお直接SQL投げるならRecordset使わずcon.Execute queryだけでも。 con.Close Set rs = Nothing Set con = Nothing

    どうも、Extended Propertiesの中にIMEX=1が含まれているとUPDATEに失敗するようです。

    ただ、IMEX=1を指定しない場合列の型の扱いが面倒になるという話も聞くので、それで問題ないか検証する必要がありそうです。

    • 回答としてマーク コーベル 2019年4月1日 4:48
    2019年4月1日 4:26
  • Hongliangさん
    ご回答ありがとうございます。

    原因は、IMEXでしたか
    本来は私が見付けられれば良かったのですが、調査しサンプルコードまで提示いただいて
    本当にありがとうございました。

    https://stackoverflow.com/questions/11341535/what-is-the-default-value-of-imex-in-oledb/28569981
    私は英語は苦手ですが、IMEX:1はReadOnlyと書いてあるようですね。

    ありがとうございました!


    2019年4月1日 4:44