'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'
を実行
「初心者なもの」の実際のところがよく分からないので、釈迦に説法だったらすみません。
ExcelやAccessは集計が得意です。しかし、集計後のデータから集計前を復元することは困難または不可能です。
My Car Bomberさんの例であれば、
今あるもの → 今欲しいもの への加工は苦手（機能を自分で作らないといけない）ですが、
今欲しいもの → 今あるもの への加工は得意（機能が用意されている）です。
My Car Bomberさんが例えば
依頼ID 依頼日 取引先ID 商品ID
などと入力するようにすれば、「今あるもの」がすぐに得られるだけでなく、
さまざまな区間（日、月、年他任意の区間）で集計がすぐに得られると思いますが、
切り替える価値がありそうか検討してみて下さい。
（依頼日を Date や CTRL + ; で入力できるし、他でも支援ができます）
初心者なもので仰っていることがあまり理解できず申し訳ありません。
詳しく説明を頂けるとありがたいです！
横ですが、これは運用を変えられないのでしょうか。
「今欲しがってるもの」→ExcelのピボットテーブルやAccessのクロス集計クエリ→「今あるもの」
なので、「今欲しがってるもの」をベースに変更できないのでしょうか。
メリットとして、商品を柔軟に増やせるなんてのもありますが。
他の方々が疑問として提示されている内容や、アドバイスの内容と、それに対するタークンさんのコメントがかなり食い違っている感があります。
再度何に対してアドバイスされているのかをしっかり理解してみてください。
Excelでのファイルの出力方法は、「メニューのファイルから保存」するだけではありません。例えばExcelでVBAを使用することでCSVを出力できますが、これも試されましたか？
他、「届書作成プログラム」で出力したファイルに上書きする方法ではどうでしょうか？
Excelから出力したCSVファイルの16進コードや文字コードが「届書作成プログラム」で出力したファイルと比較して変わっていないかなどを確認するのも良いかもしれません。
※ちなみに「EXCELL」ではなく「Excel」です（「l」が多い）
届書作成プログラムのフォーマットが特殊なのでしょう。
OFFICEが利用できないアプリは、バグに近い仕様だと思います。
文字コードの仕様など、基礎的なことは確認済みです。
届書作成プログラムで吐き出したファイルを、
EXCELLに取り込んで、別名保存でCSVにした段階で、読み込めないファイルになるので、
原因は分からないが、EXCELL使ったらダメなんだなという判断は、正しいと思います。
もちろん、コードで処理しようが、手作業で別名で保存にしようが同じです。
テキストにした時点で、属性は無くなるはずなので、何か他の事が原因なのでしょう。
一番懸念しているのは、ACCESSのデータベースを使った時点でNGみたいなことが起きたら、
コスト的に自動化は放棄するしかないという事です。
すごく心配です。
いつ、電子申請が義務化されてもおかしくないので、
何とかしてほしいですが、行政に利用者の声など届きません。
概ねそんな感じのレイアウトで、A列も空白になっています。
いままでExcel上で整形してから取込むといったことしかしてこなかったので、どうすれば自動で4行目のフィールドからとりこめるのか分かりません。
hirotonは「届書作成プログラム」に携わった人間ではありません。それが何を言っているのかもわかりません
「CSV」がなんなのか学習してください。そのうえで「CSV出力に必要なもの」は質問者が提示してください
hirotonはこの場で誰かの文句に変わって仕事をするつもりなどありません
いろいろな話がごっちゃになってます。
最初の質問の「タイムカードデータの入力」はRecordsetで解決ということですよね。
そこから、派生的に下記のような流れになってます。
処理の内容しだい。Recordsetの方が速い場合もあるし、SQLの方が速い場合もあります。
シートのセルを一つずつアクセスするような処理にするとたしかに重くなります。
パワークエリを使う、配列で処理する、Dictionaryオブジェクトを使う・・・など高速化の手段はいくらでもあります。
どのような処理か不明ですが、使い方が悪いのだと思います。
正しく使えばたいていの処理は劇的にはやくなります。
おそらくそのアプリがCSVの標準から外れたものなのか、CSVへの変換設定に間違いがあるのか、・・・
Excelの欠点ではなく、そのアプリの欠陥ですよね。
どちらにしてもいいまでの流れからまったく関係ない話です。
エクセルVBAで作ったということですか？
なぜ、このような結論になるのか分かりません。
エクセルVBAでも「100項目以上のヘーダーが無いCSVファイル」は可能です。
あるいはヘッダー付きで出力してあとからヘッダーのみ削除してもいいですね。
エクセルのCSVファイルの出力は、CSVとしては標準的なものだと思いますので、
届書作成プログラムのフォーマットが特殊なのでは。
だとしたらVBAでテキスト出力プログラムを作成することは可能ですので、特殊な書式に合わせて出力することも可能です。
そのCSVの仕様、出力サンプル、元となるエクセルデータなどを提示されたら回答はつくと思いますよ。
EXCELLをこきおろしているわけではありません。
届書作成プログラムでは、
EXCELLからCSVファイルに変換したファイルは読み込めないと言っているのです。
これは、上席のサポートの人も、認めている事です。
「届書作成プログラムにUPでるCSVの作り方」をご存じのようなので、ぜひともご教授ください。
労働厚生省も、喜ぶはずです。
>hatena様
私もそういう実装だと思っていたので、
ヘルプや検索してすぐに見つかった記事……例えばADOを解説した（だいぶ古い）記事で
こういうのを見つけて もやもや しました。「的」を使ってこの解説です。
Windows 2000 - Development Guide for Business Applications
https://www.itmedia.co.jp/help/howto/win/win2000/0007special/complus_vb/chap1/05.html
引用した MS Learn の一つは「適用先: Access 2013」と明記があるのですが、
「（バックエンドの）DBによって実装が違う」のかよく分からないのが困り物です。
昨日は rs.Supports(adResync) が True でも Resync が機能しなくて悩みましたし……
PostgreSQL でも入れて比較すればいいじゃん、と言われればそうなのですが……
ともあれ、hatena さんもそういうご見解であれば安心です。
ありがとうございました。
公式の解説はなんかわかりにくいですね
排他ロック、共有ロックとはいってなくて排他的ロック、共有的ロックと濁してます。
定数のOptimistic、Pessimisticは楽観的、悲観的という意味なので、楽観ロックと悲観ロックの違いで意味を調べた方がいいように思います。
排他制御 悲観ロック 楽韓ロック - Google 検索
上記のリンク先をいろいろ見たけど、悲観ロックに関して読み取りまで禁止するかどうかは、ぶれがあるようです。DBによって実装が違うのでしょうか。
質問のMSの公式のリンク先の説明では
「レコードごとのペシミスティック ロックを示します。」
「レコード単位の排他的ロックを指定します。」
とレコード単位でのロックとしてます。
だとするとレコード単位で読み取りを許可しない(編集中のレコードのみ表示されない)という実装は考えにくいので、他ユーザーのレコードの読み取りは許可するが、更新はできない、という実装かと推測されます。
私はこのような理解でおりますが、どうでしょうか。
参照元のExcelワークシートが上記のようなレイアウトになっている、
ということなのであれば、少なくとも Access のテーブルへの取込は
オートメーションによって行うことになるでしょう。
Excelワークシートからデータを取り込んだテーブルを使用して
レポートへの出力を行う処理については、それとはまた別の問題です。
わたしが構築する場合は、という想定で記載してみます。
他に良い方法はいくらでもあると思うので、あくまで参考までに。
※Excelからインポートした後
既存のテーブル：
T-INPORT：インポートしたテーブル
M-社名：社名マスター
M-商品：商品マスター
作るテーブル：
T-購入履歴：インポートしたテーブルから購入された情報を抽出するテーブル
項目：「ID」「社名ID」「商品名ID」
作るView：
V-発送用レポート：発送用のレポートのレコードソース
発送用のレポート：
発送レポート
1.まずT-INPORTから1行、読みます。
2.T-INPORTの「社名」とM-社名を比較し、社名マスターのIDを取得します。これは変数に一時保持します。
3.T-INPORTの「りんご」の個数をもとに「りんごの商品ID」「社名のID」「個数」を関数に渡します
※上記の「関数」とは渡された引数をもとに「T-購入履歴」に情報を書き込む機能です
中では「個数」の回数分、「T-購入履歴」に書き込みが発生します。
4.T-INPORTの「梨」の個数をもとに「梨の商品ID」「個数」「社名のID」を関数に渡します
※以下「ぶどう」「みかん」同じ
5.「4.」が終わったら、「T-購入履歴」と「M-社名」「M-商品」をViewで連結した「V-発送用レポート」に中身が発生していると思いますので、これをもとに「発送レポート」を出力します。
以上です。
これだと品目が増えるとプログラムの修正が必要なので、かなり力技です。
が、とりあえずなんとかなるかと思います。
Excelファイル自体は加工を行わず、インポート時に必要なフィールドだけを取り込み、それを加工してレコードソースを作成したいです。
1. Excel取込フォームの取込ボタンのクリック時イベントで↑のExcelファイルを選択
2. ExcelファイルからID、りんご、梨、ぶどう、みかんのフィールドをインポートしてT_取込テーブルに追加（注文日はExcelファイル2行目の日付を入れる）
T_取込テーブルとマスターで↑のようなレコードを作り、ラベルシール出力用レポートのレコードソースにしたいです。
できるだけクエリのみで解決したいのか、VBAを使用してもよいかによって
提示される解決方法は異なります。
大元のデータがExcelワークシート上のテーブルなのであれば、
先に Power Query によってそのような形に変換されたテーブルを
Access データベースにインポートする、という選択肢もあるでしょう。
ちなみに 1 の投稿は、ヘルプの「このメソッドは、静的または前方専用のカーソルを使用していて、基になるデータベースの変更を確認する場合に役立ちます。」直後の「読み取り専用ではない Recordset オブジェクトに対してのみ使用できます。」の意味が解らなかった、というものです。
う～ん
すみません自己レスです。
rs.open の引数を網羅テストしたら、事情が解りました。
rs.Supports(adResync)は、何故かただの飾りでした。
rs.resync は、JET（ACE）であっても、特定のパターン内で使えるようです。
コピペバッファが古かったです。作成日のところは「作成日 >」です。
これでどうでしょうか？ 簡単に動作試験しましたが、DELETEがあるので 慎 重 に（！）検討して下さい。
DELETE * FROM 参照1 AS OT
WHERE 作成日 = (
SELECT MIN(作成日) FROM 参照1 AS IT
WHERE OT.会社ID=IT.会社ID AND OT.組織ID=IT.組織ID AND OT.職員ID=IT.職員ID
)
Resync メソッド (ADO) | Microsoft Learn
https://learn.microsoft.com/ja-jp/office/client-developer/access/desktop-database-reference/resync-method-ado
>CursorLocation プロパティを adUseClient に設定した場合には、 Resync は、読み取り専用ではない Recordset オブジェクトに対してのみ使用できます。
という分かりにくい文章もあったので
With cn
.CursorLocation = adUseServer
.Open "Provider=Microsoft.ACE.OLEDB.16.0;Data Source=（略）"
End With
のパターンも試しましたが、今度は False → 同じエラーでした。
本当に分かりません……
EXCEL（ECXEL VBA）もそんなに遅いモノじゃありません
「簡単に出来るがクッソ遅い処理」は簡単にできます。とにかく簡単にいろんなことができるソフトなので。速さが欲しいならそれなりに仕様を勉強する必要があります
こういうのとか
EXCELからCSVを出力する方法もいろいろあります。「UPできないCSVの作り方」しか知らないだけですね
そもそも、CSVがなんたるか？を学べば、ただCSVと言っただけで常に互換が可能だという考えも知識不足だということがわかります。EXCELを問題にするのはお門違いでしょう
何をもってEXCELをこき下ろしてるのかわかりませんが、まぁ、Pythonが都合がよさそうだということであればそれでいいんじゃないですかね
あと、ここはACCESSの掲示板なので、EXCELでどうのこうの言いたいのなら別の場所を探したほうがいいと思いますよ
ありがとうございます。
EXCELLのシートを利用しなければ、ほかの言語との処理速度に大差はないはずなので、
データを格納する方法を覚えつつ、申告対策としてPythonあたりを始めてみます。
実は、社会保険の電子申請する届出書作成アプリは、
EXCELLをCSVに変換したファイルだとUPできないという、
ほぼ不良品に近い欠点があるのです。
何十億もかけて始めた労働厚生省のアプリです。
100項目以上のヘーダーが無いCSVファイルを作るプログラムを3日かけて作ったあげく、
サポートダイアルの責任者から、使い物にならないという回答をいただきました。
直接、テキストに出力するしかないのです。
正直、怒りが収まりませんでした。
しかし、この問題を解決するには、
タイムカードを他の言語で作るしか無いので、
両方始めてみようと思います。
あっ、別の話になっていたのですね。
配列は高速化のための有用なツールです。
ツールの使い方を間違っているのかも。
今回はRecordsetで解決ということなので、これにて。
タイムカードなら、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のテストでは全て同じ結果になりました
※この掲示板に記述された文字のコピペです。実際のデータと同じとは限りません
入力データと完成させたいデータの図がいまいち見えないので予想でしか無いですが。
タイムカードであればそれほどデータ量が無いと思うので、一度テーブルに全部展開して、その後処理すればいいんではないでしょうか。
どのカラムが休憩開始のカラムだとかも決め打ちで分かるだろうし。