'tn: テーブル名
'd1: 開始時刻
'Sc: 抽出条件
Function kousin1(ByVal tn As String, ByVal d1 As String, ByVal Sc As String, ByRef adoCn As ADODB.Connection)
Dim rs As New ADODB.Recordset
rs.Open "SELECT * FROM " & tn & Sc, adoCn
If rs.EOF Then
MsgBox "対象レコードが存在しませんでした。"
Else
Dim dt As Date
dt = CDate(d1)
If IsNull(rs!休憩開始) Then
rs!休憩開始 = dt
Else
rs1!休憩開始2 = dt
End If
rs.Update
End If
rs.Close
End Function
休憩時間1が、Nullか空の場合は、
SET [休憩開始] = #2023/12/05 17:00:00#
にして
strSQL = "UPDATE 出勤データ SET [休憩開始] = #2023/12/05 17:00:00# WHERE 月日 = #2023/12/05# and jan = '1234567891123'
を実行
休憩時間1に、データがあった場合は、
SET [休憩開始2] = #2023/12/05 17:00:00#
にして
strSQL = "UPDATE 出勤データ SET [休憩開始2] = #2023/12/05 17:00:00# WHERE 月日 = #2023/12/05# and jan = '1234567891123'
を実行
タイムカードなら、Recordsetで十分です。
簡単なので、むしろ有りだと思います。
他の用途のために、可能な限りシートに展開しないで、処理しようと思っているだけです。
Functionで配列を使用すると、いきなり重くなったりするので、
データを格納するというのも、どんなものなのか？
Pythonあたりに、ダイブしてみるのも有りかもです。
まだ、見られているなら、ずすやんさんも言われてますが、
現状のエクセルシートのデータ例
Accessのテーブル構成、データ例
を提示していただければ、
速度の改善のアドバイスがつきやすいでしょう。
高速化の方法はいろいろありますが、データ処理によって適切な方法は異なりますので。
単純にRecordsetは遅いということはないです。
処理によってはRecordsetの方が速い場合もありますし、クエリする方か早い場合もあります。
場合によっては、エクセル側でデータベースとして扱いやすいように変換してから、という場合もあります。
すでに確認されていないかもしれませんが気になるので記載しておきます。
質問される場合は、入力と出力を明確に記載すれば、希望の回答がつきやすいです。
みなさん、回答に慣れておられるのでうまくされていますが、通常は突然「2023/12/15」とかが出てくると「？」となってしまいます。
あと、SQLやVBA にはそれぞれ得意の処理があると思います。
やろうとされている処理はおそらく？SQLでやるとVBAで実現する場合の何倍も複雑になると感じます。
そういったプログラムは概ね間違いやすく処理時間も長くなりやすいです。
タイムカードの打刻を記録するとのことなので、レコード件数は1個だと思っていたのですが、
大量のレコードを更新する案件でしたか。
だとすると質問のSQLではすべて同じ時刻での更新になってしまうのでタイムカードのデータとしてはおかしな気がしますが。
もし、エクセルのデータをループで更新しようとしているなら、
レコードセットで１件ずつ更新するのと、
クエリで１件ずつ更新するのでは、
速度的には大差ないか、逆にクエリの方が遅くなります。
エクセルの大量のデータでクエリ(SQL)で更新する場合、高速化するなら、
エクセルのデータをAccessにリンクテーブル等で取り込んで、テーブル同志を連結して、
一気に更新するのが高速です。
そうですよ
更新したいものを更新したいんですよね？更新するかしないか判断しないとそれはできませんよ
普通は更新したいデータだけ選んで更新するものです
更新パターンが複数あるなら複数回クエリを発行すればいいです
それは分かっているのですが、Recordsetで開きたくないのです。
Recordsetで開くと、データが多い場合はストレスを感じます。
いきなりデータが多いと難しいので、少ないデータでTRY中でした。
EXCELL VBAだけで、ACCESSを使っていなかった時は、
ストレスを通り越して動かなくなりました。
データはACCESSに登録してSQLでに移行して処理はできますが、
ストレスを感じるようになり、靴に小石が入った状態が続いています。
小石を取り除くために、可能な限りEXCELLを利用しない方法を探していたのです。
VBAかCを覚えろいう事になりますが、それは場違いな質問になってしまうので、
Recordsetで開いて処理するということで、解決とさせていただきます。
ありがとうございました。
SQLでやろうとするので複雑になるのです。
Recordsetを開いてそれを更新すれば簡単です。
よく理解していないので、間違っていたら申し訳ありません。
WHERE Not 休憩時間1 Is Null
WHERE 休憩時間1 != ''
だと、更新するかしないかの判断になると思いますが、勘違いでしょうか？
休憩時間2 = Nz([休憩時間1]),[休憩時間2])
だと、
休憩時間1が空だったら、休憩時間2の時間という事になって、
いづれにしても、休憩時間2のフィールドが更新されるような気がしますが
これも、勘違いでしょうか？
休憩時間1が、Nullか空の場合は、
SET [休憩開始] = #2023/12/05 17:00:00#
にして
strSQL = "UPDATE 出勤データ SET [休憩開始] = #2023/12/05 17:00:00# WHERE 月日 = #2023/12/05# and jan = '1234567891123'
を実行
休憩時間1に、データがあった場合は、
SET [休憩開始2] = #2023/12/05 17:00:00#
にして
strSQL = "UPDATE 出勤データ SET [休憩開始2] = #2023/12/05 17:00:00# WHERE 月日 = #2023/12/05# and jan = '1234567891123'
を実行
adoCn.Execute strSQL, lRecordAffected 'SQLを実行してレコードを更新
という感じの処理を行いたいのです。
一度、シートに展開してしまえば、造作もないことですが、
可能な限り、Excellのセルには触れたくないと感じ始めたので、こだわっています。
7桁の郵便番号として、半角数字7文字であることを確認する計算式を考えてみました
基本的には更新するデータとして抽出しないだけでしょう
複数のフィールドを同時に更新するのでレコードとして省きたくないのであれば、更新しない=元の値で更新するとすれば同じになるので
とかですかね
コードで記述するなら
Recordsetオブジェクトに取り込んでレコードをループ処理で処理したほうがいいと思いますがなるほど、クエリにバグありですか。こまったもんですねぇ
ちょっとクエリ上ではどうにもならなさそうな感じだったのでACCESSのクエリでワイルドカード比較はNG、代替手法を使うしかないって感じになりそう
質問に使った言葉が間違っていました。
カラムではなくフィールドでした。
今回のデータ量であれば、いかようにでも処理はできます。
ここで質問させていただいたのは、2023/12/15の休憩時間1のフィールドにデータが無かったら(NULLを含む)更新しないで、休憩時間2のフィールドにデータを登録する方法が知りたかったのです。
2023/12/15の休憩時間1のフィールドのデータの有無を調べるSQLを探してみたのですが、見つかりませんでした。フィールドが空だったら更新するが、フィールドにデータが入っていたら更新しないというのは、何かと利用する機会があるような気がするのです。
全角文字を含む場合も抽出したいということですね。
例えば 000０000 とか。
その場合は、下記のような抽出条件にします。
これなら、長音符は全角なので、長音符を含むものも抽出されます。
hirotonさんも確認されてますが、VBAなら問題なさそうなので下記で紹介しているLike演算子代替ユーザー定義関数 LikeB を使えば長音府も抽出できますね。
クエリで大文字/小文字、全角/半角を区別して文字列比較をしたい - hatena chips
長音符ですか。
こちらのサンプルのクエリでも再現できました。
これはバグっぽいですね。
って調べてみたら私のプログでも紹介してましたね。
すっかり忘れてました(-_-;)
長音「ー」を含むデータをクエリであいまい抽出すると - hatena chips
データ的な問題以外の問題があるんですかね？
上からそれぞれ、2行目の文字のコピペ、1行目の文字のコピペ、半角ハイフンです
hirotonのテストでは全て同じ結果になりました
※この掲示板に記述された文字のコピペです。実際のデータと同じとは限りません
入力データと完成させたいデータの図がいまいち見えないので予想でしか無いですが。
タイムカードであればそれほどデータ量が無いと思うので、一度テーブルに全部展開して、その後処理すればいいんではないでしょうか。
どのカラムが休憩開始のカラムだとかも決め打ちで分かるだろうし。
すみません、000－000は抽出されます。
000ー000が抽出されません。（長音符）
SQLには問題はなさそうてすね。
こちらのサンプルでは問題なく抽出されます。
ちょっと原因が想像できないです。
データは縦に持つので、そんな事はやりません。正規化して下さい。
週を跨ぎ返事が遅くなりすみません。
こちらSQL文です。
これはおそらく全角文字以外がふくまれているかを判定したかったのでしょう。ただし、この式ではその目的を達成してません。
話がややこしくなるので、まずはこの条件は削除しておいて、
Not Like "#######"の抽出条件の方が解決してから取り組みましょうか。私の提示したシンプルなサンプルのテーブルにそのデータを追加して試してみてください。
こちらのサンプルでは抽出されました。
問題のクエリに他に抽出条件はありませんか。
わからないようならSQLビューにして表示されるSQL文をコピーしてここに貼り付けてください。
hatena さんに責任を負わせたいのではないのでご安心を（笑）。
私もいろいろ試した上で、そのようにしたいと自己責任で判断しました。
同じサンプルを作成したところ、Hatenaさんと同じ実行結果になりました。
他に何か抽出条件が設定されているのではないかと思い探してみたところ、
上記が見つかりました。
いつ書いたのか、なぜ書いたのか、どんな意味なのか全く思い出せませんでしたが、こちらを消したところ、000-000や000&000は抽出されるようになりました。
しかし、今度は消す前は抽出されていた000－000が抽出されなくなってしまいました。
7桁の半角数字以外のレコードを抽出するという目的がまだ達成できていません。
ちょっと考えが進んだので、書き残させて下さい。
試した感じだと、この両方が自分更新可・他者更新不可で動くようです。
Set rs = db.OpenRecordset("table1", dbOpenTable, dbReadOnly + dbFailOnError)
Set rs = db.OpenRecordset("table1", dbOpenTable, dbFailOnError, dbReadOnly)
判断できるのは、
Access のほうで、「ロールバックを想定してるんならおまえが操作するんだよな？」ということで
切り替えてくれるらしいこと。
一応 LockEditにdbReadOnlyが受け付けられるパターンがあること……です。
ただし、（飽くまでも私の環境では）何か動作が不安定な感じがあるので、
MS の公式外、保証外、裏技、昔はできたけど既に見捨てられた何かなのかもしれません。
このへんのせいで、日本語でも英語でも解説が散らばったのかもしれません。
「かもしれない」が続きましたが、改めて言って、
hatenaさんが書いて下さったところが本道なのだと思います。
私もそちらに従わせていただきます。
いえ、全然、正統ではないです。
WEB上の情報と、自分の少ない経験のみですので。
結局、最終的には自分で試してみるしかないですね。
あら、読み落としてました。
ちょっと意味不明な注意書きですね。
ちなみにこんなことをする必要はありませんが、間違えてたまたま dbReadOnly + dbFailOnError にしたところ、自分は更新できる・他のユーザは更新できないという振る舞いもしました。このへんも完全に謎ですが、正統と思われる hatena さんの書いて下さった感じで使いたいと思います。
>>hatena様、情報を整理して紹介して下さってありがとうございます。
hatenaさんがおっしゃれば心強いです！ 私もやっと安心です。
それが、まさに Database.OpenRecordset メソッド (DAO) のページにあるようで……
https://learn.microsoft.com/ja-jp/office/client-developer/access/desktop-database-reference/database-openrecordset-method-dao
ただ、検索していると昔は入れられた節があり？？？、修正漏れなのかもしれません。
「間違った(？)解説」は、海外の出版物でもちょくちょく出会い、本当に戸惑いました。
ともあれ本当にありがとうございました！
気になって検索してみたら、間違った(？)解説してあるサイトが結構ありますね。
このような場合はまずはMSの公式を参照して確認するのがいいです。
ただ、公式もたまに間違っているときがありますので、実際にテストして確認するということも必要になります。
dbReadOnly はOptionsだけだと思いますが。(下記参照)
Options引数
RecordsetOptionEnum 列挙 (DAO) | Microsoft Learn
LockEdit引数
LockTypeEnum 列挙 (DAO) | Microsoft Learn
上記のMSの公式のリンク先の解説では、
dbReadOnly Recordset を読み取り専用として開きます。
となってます。開いたRecordsetが読み取り専用になるということだと読み取れます。他のユーザーが更新できないとは書かれてません。
他のユーザーがレコードを変更できないようにするなら、下記ではないでしょうか。
dbDenyWrite 他のユーザーが Recordset のレコードを変更できないようにします。
私は下記のように理解しています。もし、試した結果が異なる場合は指摘してください。
フィールドの書式プロパティもなにも設定されてませんか。
こちらで作成したサンプルでは正しい結果になってますね。
上記の画像のようなサンプルを新規作成した場合はどうなりますか。
定型入力は何も設定されておりませんでした。
レポート出力の際は7つテキストボックスを作成し、それぞれ=Mid([〒],1,1)～=Mid([〒],7,1)という風にしています。ですので郵便番号フィールドは、更新クエリでハイフンを""に置換し、7桁数字の書式にしています。
000-000や000&000は、7桁数字以外が抽出されているかどうかの検証中に抽出条件から外れていたので例えとして提示致しました。
"7桁の数字"以外が正確に抽出されるためにはどのような抽出条件を設定すれば良いか、というのが本題です。
Not Like "#######"
この抽出条件で一見問題ないように見えて、例のようなデータが抽出条件に引っかからなかったので、原因と対策を知りたいです。
そのフィールドに定型入力が設定されてませんか。
もし、されているならその設定を教えてください。
ちなみに郵便番号フィールドなら、000-0000 という書式になるはずですが、
000-000 というのはどのような事情でしょうか。
000-000が、本来抽出されなければならないはずが抽出されませんでした。
000&000も、本来抽出されなければならないはずが抽出されませんでした。（別サンプル）
ちなみに、0から始まる郵便番号が正しく表示されるよう、郵便番号フィールドは短いテキスト型になっています。
「抽出条件をすり抜けてしまいました」とは、具体的にどうなったのでしょうか。
「000-000」というデータが表示されたのか、表示されなかったのか、どちらでしょうか。
Not Like "#######"なら表示されるのが正常です。こちらでサンプルを作成しましたが、表示されました。
回答ありがとうございました。
ご指導の通りの方法を検討します。
おふたりとも大変分かりやすい説明です、ありがとうございました。
MOS合格できそうです。
人間がコードを記述するときに楽をするための仕様ですね
VBAはコードを記述する際に、高頻度でその用途で使われるモノは省略しても良いとして、様々なモノが省略可能なように作られました
つまり、
Me.も.Valueも[]で囲むのも、省略した場合、コードが実行されるタイミングで記述されているとみなしてコードが実行されていますこれらは、省略したとき、意図しない解釈がされてしまう場合には明記する必要があります
例えば「1」という名前のテキストボックスを作成します。これをコード上で記述する場合、単に「
1」と記述すると、コードの解釈としては数値の「1」を記述したと解釈されますこの記述は常に
a=2ですこの記述は「
aに「1という名前のコントロール」の値に「1」を加えた値を代入する」となりますレコードセットを取得してから重複を削除するのは難しいので、
サブフォームのレコードソースのクエリから、SQLでフィルターと同じ抽出条件を設定して、DISTINCTで重複を排除したレコードセットを取得すると考え方になります。