上記のフィールドも持つ空のテーブルを作成しておきます。
ID はオートナンバー型の主キーにしておきます。
下記のSQLのクエリを作成します。
SELECT
会社マスター.会社ID,
商品マスター.商品ID,
Choose([商品ID],[商品1],[商品2],[商品3],[商品4]) AS 個数
FROM
T_取込 INNER JOIN 会社マスター ON T_取込.社名 = 会社マスター.社名, 商品マスター
ORDER BY
T_取込.取込ID, 会社マスター.会社ID, 商品マスター.商品ID;
SELECT
会社マスター.社名,
会社マスター.[〒],
会社マスター.住所1,
商品マスター.商品名
FROM
(T_会社別売上 INNER JOIN 会社マスター ON T_会社別売上.会社ID = 会社マスター.会社ID)
INNER JOIN 商品マスター ON T_会社別売上.商品ID = 商品マスター.商品ID
, T_Num
WHERE
T_Num.Num<=[個数]
ORDER BY
T_会社別売上.会社ID, T_会社別売上.商品ID, T_Num.Num;
Set rst = Me.frm注文依頼発注用_一覧Sub.Form.RecordsetClone
rst.Filter = "注文書作成チェック = True AND 発注ナンバー Is NULL"
rst.Sort = "仕入先ID ASC,業務用品ID ASC,単位_注文数"
Set rst = rst.OpenRecordset '←これが必要
rst.MoveFirst
Do Until rst.EOF
'呼び出し例
Private Sub ImportTest1()
Dim strTargetFilePath As String
Dim strTargetSheetName As String
Dim varRet As Variant
strTargetFilePath = "C:\FolderName\BookName.xlsx"
strTargetSheetName = "Sheet1"
varRet = ImportOrderRecords(strTargetFilePath, _
strTargetSheetName)
If IsNull(varRet) = False Then
MsgBox strTargetFilePath & " の " & _
strTargetSheetName & " から " & _
varRet & " 件のレコードを取り込みました。", _
vbInformation, _
"実行完了"
End If
End Sub
Function ImportOrderRecords(SourceBookPath As String, SourceSheetName As String)
On Error GoTo Err_ImportOrderRecords
Const OrderDateCellAddress As String = "B2"
Const OrderDateSuffix As String = "依頼分"
Const HeaderRow As Long = 4
Const KeyColumn As Long = 2
Const DestinationTableName As String = "T_インポートテーブル"
ImportOrderRecords = Null
Dim xlsApp As Object 'Excel.Application
Dim xlsBook As Object 'Excel.Workbook
Dim xlsWorksheet As Object 'Excel.Worksheet
Set xlsApp = CreateObject("Excel.Application")
Set xlsBook = xlsApp.Workbooks.Open(FileName:=SourceBookPath, ReadOnly:=True)
Set xlsWorksheet = xlsBook.Worksheets(SourceSheetName)
Dim varOrderDate As Variant
Dim lngSuffixPostion As Long
With xlsWorksheet.Range(OrderDateCellAddress)
varOrderDate = .Value
lngSuffixPostion = InStrRev(varOrderDate, OrderDateSuffix, -1, vbTextCompare)
If lngSuffixPostion > 0 Then
varOrderDate = Trim(Left(varOrderDate, lngSuffixPostion - 1))
End If
If IsDate(varOrderDate) = True Then
varOrderDate = CDate(varOrderDate)
Else
Debug.Print .Address(False, False) & " セルの値: "
Debug.Print .Value
MsgBox .Address(False, False) & " セルから依頼日を参照できません。", _
vbExclamation, _
"ImportOrderRecords"
Set xlsWorksheet = Nothing
xlsBook.Close False
Set xlsBook = Nothing
xlsApp.Quit
Set xlsApp = Nothing
Exit Function
End If
End With
Dim lngFirstDataRow As Long
Dim lngLastDataRow As Long
With xlsWorksheet
lngFirstDataRow = HeaderRow + 1
lngLastDataRow = .Cells(.Rows.Count, KeyColumn).End(xlUp).Row
If lngFirstDataRow > lngLastDataRow Then
MsgBox "ワークシート[" & .Name & "]からデータ行を参照できません。", _
vbExclamation, _
"データ参照エラー (ImportOrderRecords)"
Set xlsWorksheet = Nothing
xlsBook.Close False
Set xlsBook = Nothing
xlsApp.Quit
Set xlsApp = Nothing
Exit Function
End If
End With
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Set db = CurrentDb
strSQL = "DELETE * FROM [" & DestinationTableName & "]"
Debug.Print strSQL
db.Execute strSQL, dbFailOnError
strSQL = "SELECT * FROM [" & DestinationTableName & "]"
Debug.Print strSQL
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
Dim lngDataRow As Long
Dim lngInsertCount As Long
For lngDataRow = lngFirstDataRow To lngLastDataRow
rs.AddNew
rs![依頼日].Value = varOrderDate
With xlsWorksheet.Cells(lngDataRow, KeyColumn)
rs![社名ID].Value = .Value
rs![商品名1].Value = .Offset(0, 4).Value
rs![商品名2].Value = .Offset(0, 5).Value
rs![商品名3].Value = .Offset(0, 6).Value
rs![商品名4].Value = .Offset(0, 7).Value
End With
rs.Update
lngInsertCount = lngInsertCount + 1
Debug.Print lngInsertCount & " 件目のレコードを取り込みました。"
Next
ImportOrderRecords = lngInsertCount
Exit_ImportOrderRecords:
On Error Resume Next
Set rs = Nothing
Set db = Nothing
Set xlsWorksheet = Nothing
If Not xlsBook Is Nothing Then
xlsBook.Close False
Set xlsBook = Nothing
End If
If Not xlsApp Is Nothing Then
xlsApp.Quit
Set xlsApp = Nothing
End If
Exit Function
Err_ImportOrderRecords:
Dim strErrMsg As String
strErrMsg = Err.Number & ": " & Err.Description
MsgBox strErrMsg, _
vbCritical, _
"実行時エラー (ImportOrderRecords)"
Resume Exit_ImportOrderRecords
End Function
Accessにもあるみたいですね（使ったことはないですが）
https://kabudata-dll.com/ms-access/chart/
自己レスです。
個人的にこれまで考えたことが無かったのですが、こちらの記事が普通に参考になりました。
https://sourcedaddy.com/ms-access/understanding-module-load-demand.html
ちなみに AutoExec したところ、
私が作っていた小さなモジュール30程度は全て「開かれて」立ち上がりました。
巨大なモジュールが多数あれば、Access が選別するのかもしれません。
お世話になっております。
意図した通りの出力結果が出るようになりました！ありがとうございました
何がどのように繋がって出力結果にたどり着いたのか、クエリやテーブルの構造・関数などを確認し勉強させて頂きます！
ありがとうございます！
正規化されたテーブルを作るところからやってみます！
My Car Bomberさんの質問から発生する一連の構築に関する回答は、DBを使ったシステムのスタンダードかつお手本のような内容で素晴らしいですね。
出来上がればDBの速度低下で悩まされることも無く、カスタマイズもし易いシステムになりそうです。
まずは「T_取込」テーブルを正規化されたテーブルに変換します。
正規形のテーブルは記のようになります。
T_会社別売上 (テーブル名は適当ですので実態にあったものに変更してください。)
上記のフィールドも持つ空のテーブルを作成しておきます。
ID はオートナンバー型の主キーにしておきます。
下記のSQLのクエリを作成します。
これを保存してデータシートビューで希望のデータになっていることを確認したら、
追加クエリに変更して追加先を「T_会社別売上」テーブルにします。
この追加クエリを実行するとT_会社別売上に上記のデータ例のようにデータが追加されます。
下記のような数値型フィールドが一つのテーブルを作成します。
予想される最大個数までの連番を入力しておきます。
T_Num
下記のようなSQLのクエリを作成すればレポート出力用にデータになります。
これをレポートのレコードソースに設定すればいいでしょう。
hatena様
ご回答ありがとうございます。
ご指摘の通りやってみましたら、バッチリできました。
Set rst = rst.OpenRecordset で再設定しないといけない事を初めて知ったので勉強させてもらいました。
ありがとうございました！
助かりました。
DAOのRecordsetは、FilterプロパティやSortプロパティを設定したあと、再度開きなおさないと反映されません。
また、Nullは=演算子ではなくIs演算子で判定します。
大変参考になります、コードの内容理解に私では少し時間がかかりそうですが、読み解いてみます！
お返事遅くなりまして申し訳ございません。
こちらでできました。
作成日 > min(作成日)ですか、なるほどです。
どうもありがとうございます。
補足:
事前バインディングではなく実行時バインディングを採用される場合は、Excel.XlDirection 列挙のメンバー定数 xlUp の代わりに値 -4162 を渡すようにして下さい。
といった処理を実行したいなら以上のようなコードになるでしょう。
ただ、前回のスレッドにおいて
とご説明された通りなのであれば、正規化したテーブルに変換し、互いの[商品ID]同士で[商品マスター]と結合できるようにされた方がよいでしょう。
データ集計を行う上でも「発送用のレポート」を出力する上でも、その方が便利なのは明らかです。
エクセルの方は変更できないということであれば、アクセスにインポートしてから、アクセスの方で正規化変換することになります。
Accessではレポートに出力するだけで、出力レイアウトがエクセルの表とほぼ同じということなら必要ないです。
ただ、せっかくアクセスにインポートするならデータベースとして活用したいですよね。
印刷だけなら、エクセルでもできますので。
シートの範囲指定してインポートするなら、TransferSpreadsheet で第6引数でシートや範囲を指定できます。
上記はB列からI列の４行目からデータのある最後までインポートします。
シートの形式を変更したり、シートを手作業で加工して名前を付けたりすることは出来ないという前提でお願いします。
そのため、任意の範囲のみをインポートする方法を模索しています。
正規化というのは、今回でいえば、A列と1～3行目が無ければ正規化できているということになりますか？
エクセルの表のフォーマットがデータベースとしては扱いにくい形になってます。
データベース用語とて「正規化」ができていない状態です。
もし、エクセルの表の形式を変更できるならそれを検討してみてはどうでしょう。
banjoさんの回答も同じ意味だと思います。
もし、エクセルの表のフォーマットは変更不可ということなら、
表範囲に名前をつけることはできないでしょうか。
表範囲をテーブルにできないでしょうか。
(テーブルにすれば自動で名前がつきます。)
そうすれば、インポートするときにその名前を指定してインポートできます。
「初心者なもの」の実際のところがよく分からないので、釈迦に説法だったらすみません。
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で解決ということなので、これにて。