カテゴリー: VisualBasicApplication

SQL音痴：sortは最後なのね

SQL音痴だな私、情けない

直近売上：current_uriage(10万行)
過去売上：old_uriage(500万行)

先日、初めて作ったAccess + PostgreSQL「仕入用FAX発注」プログラム

これに商品コード＋[1ヶ月][2ヶ月][3ヶ月][半年][1年][2年]
の販売明細ボタンを追加した。

このプログラム、
遅すぎでダメみたい（泣）

私のSQL（＝まず最初、対象レコードを減らす）
１：currentとoldをunion連結したview作成（対象数：510万行）
２：対象レコードが多すぎるので、このviewを売上dateで降順しtop 200000
３：完成view_all_uriage_top200kの20万行に対し、SQL文を動的作成・実行
select * from view_all_uriage_top200k where itemcd=’999999′ and day_uriage > ‘yyyymmdd’)

この実行が毎回20秒以上！

イライラ

友人のSQLプロを呑みに誘い、なんでと聞くと

プロ：それは、当然だね！
sort実行のため全レコードを読む必要が生じる！
20万行の限定は逆効果

私：そうなの？
510万を20万にしたんだよ？

プロ：sort無しの UNION view 作って、whereで商品コードと日付で取ればいい。
両方index項目だから、対象レコードは最小になる

私：本当に？

vbaを書き換えると・・・・

私：あら、本当だ！
一瞬でリストが表れた（衝撃）

私のSQL知識、ダメ過ぎ！

vba本はいっぱい読んだので、何読んでも目新しい情報はもう無い！
でも、最近読み始めたSQL本、知らないことばかり！

改めて、SQL学ばねば・・・・
死ぬまで勉強ですね

2025年5月9日
[初めてのAccess+postgre]遅過ぎ対策２分→２秒へスピードアップ方法

Jet Database Engineプログラムしか作れない私（情けない）

今更で、キャリアアップのためpostgreSQLを勉強中

毎日使ってる自作「メーカー発注用Accessプログラム」

こいつのpostgreSQL版が作りたい。
完成すれば社内の全PCから発注データ参照/発注業務ができる。

きっと簡単だろう！

odbcでリンク張って
vbaは、jet用を少しイジるレベルでは？

２週間くらいで「odbcリンク版」完成！
しかし・・・・

が～ん、使い物にならない！
update文１行が約２分！？
遅・す・ぎ・る！

私は、２行の「DoCmd.RunSQL update文」を多用します。
全データのFlagを初期化(=False)、その後、該当データのみを(=True)
[vb]DoCmd.RunSQL "UPDATE public_tblorder SET Fprint=False"
DoCmd.RunSQL "UPDATE public_tblorder SET Fprint=True where ORDERNO=’" & TargetMaker & "’"
[/vb]
数年分貯まった発注明細、約29,000行
このデータへupdateを実行し処理速度を比較
[vb]"UPDATE public_tblorder SET Fprint=False"[/vb]
内部jet tableなら、1秒以下

これ以降は、知らない世界！
物理的に離れてるpostgreSQLサーバへ接続

odbcリンク接続経由で、postgre TABLEへupdate実行

えぇー、嘘だろ？

１分４６秒！！
超々、ちょう遅い
絶望的に遅い！

jetの100倍以上遅い！

postgresODBC ver.10が絶望的に遅いので、ver.16へアップグレード！

テストし直すと
あれ、１分５５秒にスピードダウン！？
前回テストからレコードが数百増えたのが原因？

とにかく、私のvbaプログラムは使い物になりません！

そもそも、postgreサーバ上で実行すると何秒かかるの？

postgreSQL shellから実行で、2.5秒

pgAdminから実行で、3.3秒

処理時間、ほぼ２分のクライアントPC上のAccessを、

数秒にスピードアップする方法
google様、教えて

google様のお告げ１：パススルークエリーをテスト

パススルークエリで、約5秒

もっと早い方法が無いか、さらに検索
お告げ２：ADO(ActiveX Data Objects)からSQL実行

ADO接続＋con.Execute(sql)でselect文実行スクリプト発見！
ならばupdate文も実行できるのでは？
[vb] Dim con As New ADODB.Connection

con.ConnectionString = "Provider=MSDASQL;" & _
" DSN=toPosgre;" & _
" DATABASE=test;" & _
" SERVER=888.888.888.888;" & _
" PORT=8888;" & _
" UID=taro;" & _
" PWD=urashima" & _
" SSLmode=disable;"
con.Open

Dim sql As String
sql = "UPDATE tblorder SET Fprint=False"

Dim rst As ADODB.Recordset
Set rst = con.Execute(sql)

Set rst = Nothing
con.Close
[/vb]
update文も実行できた！
ADOで、約2秒
すごい、Shellと同じ処理速度！

このADO接続で、vbaを書き直します。

アマチュアとプロの間、高いハードルが有りますね

追記：jet用スクリプトをpostgre用に書き換えました(2分が2秒にスピードアップ)
jet用だと２行
[vb]DoCmd.RunSQL "UPDATE public_tblorder SET Fprint=False"
DoCmd.RunSQL "UPDATE public_tblorder SET Fprint=True where ORDERNO=’" & TargetMaker & "’"
[/vb]
これをpostgre用に書き換えるとこんな感じかな

まずFunctionプロシージャ
[vb]Public Function SQLexec(mySQL As String)

Dim con As New ADODB.Connection

con.ConnectionString = "Provider=MSDASQL;" & _
" DSN=toPostgre;" & _
" DATABASE=test;" & _
" SERVER=888.888.888.888;" & _
" PORT=8888;" & _
" UID=urasima;" & _
" PWD=taro;" & _
" SSLmode=disable;"
con.Open
Dim rst As ADODB.Recordset
Set rst = con.Execute(mySQL)
Set rst = Nothing
con.Close
Set con = Nothing

End Function[/vb] 呼び出すvba
[vb]Dim mySQL As String

mySQL = "UPDATE tblorder SET Fprint=False"
Call SQLexec(mySQL)

mySQL = "UPDATE tblorder SET Fprint=True where ORDERNO=’" & TargetMaker & "’"
Call SQLexec(mySQL)[/vb]
スクリプトが、２行から２０行超に！
これで実行速度２分が２秒にスピードアップしました。

やっぱADO.Execute(mySQL)が、
一番早い！

2025年1月31日
[vba]ms-Access綱渡り開発。新作ediソフト完成かな！

2024年2月1日、お得意先の「edi受発注システム」スタート

その準備に、年明けから仕事の合間で少しずつediソフト開発。

プログラミングに専任できないので、開発時間が絶対的に足りません！

形部流

ざっくりなプロトタイプをつくり
そのままスタート！

遭遇するエラーを、その度に潰しながら
データ送受信を半手作業でこなし数日稼ぐ。
その間に完成度を上げていく！

サービススタートから1週間、ようやく私の新プログラム、使い物になるレベルになりました。

経験上、

テストサーバ＋数行のテストデータでの半月分の開発より
実サーバ＋実データをあつかうパニック下の２日間の方が開発効率100倍上です。

この開発スタイルで、３０本以上作りました。

素人でも本格プログラムが作れるms-Access様
あなたは中小零細企業の救主！

得意先数店が数年ごとに、より良いediサービスを探して変えていく。
その度にプログラムを外注すると、IT投資を回収できた頃に、また新プログラムが必要になる苦しいスパイラル！

ediホスティングサービス登場後は、素人が頑張って自作しないと採算が取れない時代になりました。

頑張って勉強したら何でも作れる
ms-Accessは最強のツールです。

ediホスティングサービス時代、同業他社はどう対応してるんだろう？

私は、疲れました

体力・年齢的にそろそろ限界

2024年2月7日
[vbaエラー]MS-Access mdb(32bit)をWindows10(64bit)で動かすとエラー祭り！

edi出荷検品用タッチパネルWindowrs10(32bit)が突然死。
秋葉で代替機Windows10 pro(64bit) を買ってAccess mdbソフト(32bit)を動かすと

エラー祭り

早く安定させないと
EDI受信・出荷検品が出来ない！

怒られる

エラー１
Declareを、Declare PtrSafeに一括置換すれば解決

エラー２：型違い
rs.RecordCount(64bit)の型は、LongLongなの！？

多用してるこんな感じのScript
[vb] Dim i As Long
i = rs.RecordCount

If i = 0 Then
MsgBox "該当データ無し"
Endif
[/vb] 全該当箇所を、こう書き換えたらエラーが消えた(=減った)
[vb] Dim i As LongLong
i = rs.RecordCount

If i = 0 Then
MsgBox "該当データ無し"
Endif
[/vb]
エラー３：共有フォルダーが見えない！
そんな時は、SMB1.0(Server Message Block v1.0)を有効化
[意外な落とし穴があるかも　SMB1廃止のメリットとデメリットを考える]

エラー４：ロングパス名？ショートパス名？

ショートパスは、DOS時代のファイル名8.3文字用ソフトとの下位互換の仕組み。

vbaから実行するコマンドラインプログラムの一部が動かない！

ある機能は、ショートパス名で実行できた。
でも、一部機能はロングパス名に書き換えないと動かない。

なんで？

とりあえず代替機、完成！

これで、C社EDI取れます

経費削減のため、なんでも社内でやってます

2023年1月28日
[pc対策方法]MS-Officeがエラーでインストールできない！そんな時「Microsoft Support and Recovery Assistant」

ガァーん！

C社専用端末「EDI受注データ送受信＋出荷検品(自作MS-Access)」ソフト用で使っていたタッチパネルWindows PCが突然死！

得意先には、ごめんなさい
今回の納品は無理です

ちょうどこの週末は、5年ぶりの東京旅行
秋葉原で、代替機用中古タブレットPCを買いました。

中国人店長で、スタッフはアラブ系！
お客さんも中東系！
とっても不思議なお店で購入

久しぶりの秋葉原、ずいぶん様子が変わったな～

税抜15,900円を、値切って12,000円で購入。

ヤラレタ！
領収書よく見ると税込15,282円
本体価格13,892円だ！
中国＋アラブに騙された！
脇が甘かった

買ったPCは、キーボード着脱式
富士通の業務用タッチパネルPC
かなり丈夫そう！

早速セットアップ

EDI受送信ソフトのセットアップ
MS-Access Runtimeのインストール

最後、出荷検品ソフト(mdbファイル)をコピーし、実行すると

ガァーん！
ガァーん！
即効クラッシュ

古いAccess32ビットファイル、
Windows10PRO 64ビットOSで、動かないの?

クラッシュ原因がわからないので、MS-Officeを購入
インストールを始めると

ガァガァガァガァーーん
何度やっても
原因不明のエラーで
インストールできない!

怪しい店の怪しい中古マシン

中国＋アラブにヤラレタかも・・・
Windowsに致命的な問題があるの?

いろいろ調べた結果、原因が分かりました。

MS-Officeインストール前に
アンインストールしたMS-ACCESS Runtimeのゴミ(レジストリ？)が残っているのが原因みたい

この不具合の対策ソフトがこれ、本家マイクロソフト社製
「Microsoft Support and Recovery Assistant」

このソフトの「OFFICE完全アンインストール」機能を使うらしい。

低スペックが原因なのか、インストール時間はけっこ長かった

初期画面がこれ
この中の「OfficeとOfficeアプリ」をクリック

Officeをアンインストール

はい

Officeバージョンを選択

砂時計が15分くらいクルクル
[再起動]ボタンを押すと

再起動後も、砂時計が１０分以上クルクル

込み込み１時間かかって
Office完全完璧クリーンアップが完了！

さて、Officeはインストール出来るかな？

わぁ～、インストーラーが動いた！

MS-Accessさえ動けば無敵
零細企業のジャベリン(=最強兵器)が、MS-Access
零細企業のHIMARS(=最強兵器)が、MS-Access
素人でも、ありとあらゆるものが独学で作れます。

2023年1月20日
[vba]MS-Accessは中小企業を救う：中級レベルのプログラムまでなら素人でも作れ、IT経費節約

MS-Accessは中小企業を救う

2022年1月、空き時間はAccessプログラミング
2月1日契約スタート「新EDI受注システム」の開発

空いた時間に少しずつ開発できる

MS-Access「お手軽プログラム環境」
最強です！

これが新作「EDI A社用プログラム」

得意先、各店舗からの受注データは、パスワード付きZIPファイルメール送信を希望。
今回の目玉機能

受信フォルダー内をdir
zipファイルの一覧表作成
パスワード動的生成しながら
一括zip解凍・一括インポート
基幹システムマスター情報を書き込み
最後、基幹システム一括インポート用固定長ファイル作成

うふ、なかなかの秀作
使用開始初日からスムーズな出だしでした。

実務最前線の私がシステム開発
かゆいところに手が届くこだわりシステムが自分で作れちゃう！
それが、The「MS-Accessお手軽開発」

その他もEDIプログラムあれこれ

「EDI B社用プログラム」

「EDI C社用プログラム」

「EDI D社用プログラム」

「EDI E社用プログラム」
タッチパネルWindowsタブレットを使った出荷検品機能付き

EDI受信プログラム、１本どれくらい？
約１００万円として、この十数年で１０個以上作りました。

１０００万円以上
IT経費節約できたはず

ありがとう
MS-Access

最強アプリです

2022年2月11日
[vbaエラー]Accessが動かない！64bitエラー？

PCが壊れたのでOSの再インストールと再セットアップ

あれ、Accessプログラムが動かない！
なんで？

独学Access歴20年以上で、初めてのエラーメッセージ

google様に訪ねると、下記のページを教えてくれました。
[「このプロジェクトのコードは、64ビットシステムで使用するために更新する必要があります。Declareステートメントの確認および更新を行い、次にDeclareステートメントに PtrSafe 属性を設定してください。」が表示されVBAが動かない場合の対処方法]

MS-Officeって、デフォルトで32bit版がインストールされるらしい。
今回、年が明けたので、あえて選んだことが無かった64bit版を選択してみました！？

すると、Accessプログラムが全滅！

vba 32bitとvba 64bit
って微妙に違うみたいです

知ってたら32bitをインストールしたのにな・・・

上記ページに書かれてた対応策を実行

DeclareをDeclare PtrSafeに一括置換

Access(64bit版)でも、動くようになりました。

エラーが起き、そのエラーメッセージをgoogle検索。

それがきっかけで、仕様変更を知るのがアマチュアプログラマーなんです。

死ぬまで勉強、勉強・・・

2022年1月7日
[vba]FileSystemObjectエラーでEDIソフト停止

忙しい朝の出荷時間、自作EDIソフトが止まると焦り倍増

今日は、キツイ日になる予感

自作ソフトの良いところはソフト代ゼロ円
悪いところは、突然エラーで止まっても助けてくれる人がいないこと。

今回のエラーメッセージが

プロシージャの呼び出し、または引数が不正です

エラーを出してるのは「FileSystemObject」
[vb] Dim FSO As New FileSystemObject
Dim st As TextStream
Dim myTEXT As String

Set st = FSO.CreateTextFile("C:\EDI\upload.csv")

st.WriteLine (myTEXT)
[/vb]
「FileSystemObject　プロシージャの呼び出し　引数が不正」でGoogle検索すると

VBAの文字列はUFT-8として扱われるようです。この場合文字列にUTF-8に存在してShift-JISに存在しない文字列を書き出そうとした、かつファイルがShift-JISの場合このエラーが発生するようです

UTF-8で使えるがShift-JISでは使えない文字を使ってる商品名があるのかな？

調べた結果、上の”L”がエラー原因
今日から販売を始めた商品でした。

下が普通の”L”
誰が登録したんだ？

とりあえず商品マスタを普通の”L”に変えたらエラーが消えました！

2021年7月27日
YouTube[ゆっくり動画]AccessクエリーとSQL解説動画を作りました

個人的に、この時期の仕事が１番キツイ

本業が終わった後、得意先から送られてくる「お中元申込ファイル」を元に

売上伝票、仕入一覧
ピッキングリスト、請求書
そして地図

を、夜な夜な１人で作る日々・・・

疲れたよ

データ量が多すぎて手作業では絶対無理！

唯一の方法が、MS-Accessでの専用プログラム開発

MS-Aceessは、私の相棒

で、ここからが本題

ExcelとFileMakerユーザーがネット上で

Access、よく分かんねえ！
使いにく過ぎ
全然ダメ

と、自分の努力不足・理解力不足・技術不足を棚に上げAccessをバカにする「負け犬の遠吠え」的投稿をよく見かけます。

SQLを使わないデータベース開発ソフトって
私の中では、ありえません！

「SQL4大命令文」を使えば簡単明快にデータベースシステムがスラスラ作れるのに、SQLを使わない開発ソフトユーザーが、ガタガタ言ってるの

的外れ
ウザい

Accessは、使いこなせればとてもパワフルなソフトウェア。

特に、専門のIT人材を雇えない中小企業で本領発揮してくれます。

ACCESSの凄さを知ってもらおうとYouTubeで最近流行りの「ゆっくり解説」動画を作り始めました。

前回1作目は、初歩の初歩「概念」みたいな内容で

2ヶ月間で、視聴回数18回
笑っちゃうほど不人気！

2作目の今回は、データベース言語「SQL4大命令文」を解説。
1作目と違い、即実践に使える内容にしています。

ギャグもパワーアップ！

これで目ざすぞ
視聴回数、20回超え！
[ゆっくり解説]MS-Accessの入口(2) 「クエリーの正体」「SQL4大命令文」ざっくり解説

2021年6月30日
[vba]総額表示プライスカード完成

総額表示プライスカード作りでバタバタ

コロナ不況ド真ん中
なんでこんな時に！
延期出来ないのか？

完成したプライスカード800枚！

ライバル卸が使ってる
専門メーカー製、高価なプライスカードシステム
それを買えない（＝買わない）形部商事

私の目が黒いうちは、なんでもMS-Accessで自作

貧社のケチケチプライスカードシステム

市販品のカードに自作Accessプログラム
必要経費は、データ収集の人件費と専用用紙

「エーワン POP REVOLUTION プライスカード棚什器用 40面 10枚」

Amazon価格344円÷400カード
プライスカード0.86円/枚

素人作りですが
それなりの見栄えだと思います

限られた印字スペースに盛りだくさんの項目
VBAでVisible・Hight・FontSizeをコントロール！

vbaで背景色を指定する
[code lang=”vb”]
Me![backbox].BackColor = "16777190" ‘初期値青
[/code]
文字数でフォントサイズを動的変更
[code lang=”vb”]
If Len(Me![商品名規格]) > 26 Then
Me![商品名規格].FontSize = 12
Else
Me![商品名規格].FontSize = 13
End If

If Me![売価税込] >= 1000 Then
Me![売価税込].FontSize = 16
Else
Me![売価税込].FontSize = 18
End If
[/code]
vbaで8桁バーコードと13桁バーコードをコントロール
[code lang=”vb”]
Me![JAN8].Height = 600
Me![JAN13].Height = 600

If myLen = 8 Then
Me![JAN13].Visible = False
Me![JAN8].Visible = True
Else
Me![JAN13].Visible = True
Me![JAN8].Visible = False
End If
[/code]
MS-Access超便利
中小企業の守神

不況を乗り切るには

可能な限りのDIYですよね

2021年3月13日
[vba]プライスカード発行プログラム改造中

空いた時間に「プライスカード」発行プログラムを改造中

完成した「二重価格プライスカード」を得意先に見せると新たな要望が(マジで！)

売価は小数点切上げ

必要に迫られた機能だけを本を斜め読みしながら勉強してきた我流プログラマーの私

切上げプログラム
作ったことないな・・・

取りあえず操作画面に「切上チェックボックス」を追加

さて、切上げってどうするの？

ネットで拾ったスクリプトを参考に作ったプログラムがこれ

切上げプログラム
[code lang=”vb”] Sub MakeTAXonPRICE(myOffPrice As Currency, myTAX As Single)

Dim myOnPrice As Currency

myOnPrice = myOffPrice * myTAX

If Me![opt切上] = True Then
Me![売価2税込] = IIf(myOnPrice = Int(myOnPrice), Int(myOnPrice), Int(myOnPrice + 1))
Else
Me![売価2税込] = Int(myOnPrice)

End If

End Sub
[/code] 税込売価10%ボタン
[code lang=”vb”] Private Sub btn10on_Click()

Dim myPrice As Currency

myPrice = Me![売価]
Call MakeTAXonPRICE(myPrice, 1.1)

End Sub
[/code] 税込売価8%ボタン
[code lang=”vb”] Private Sub btn8on_Click()

Dim myPrice As Currency

myPrice = Me![売価]
Call MakeTAXonPRICE(myPrice, 1.08)

End Sub
[/code] これで一行ずつの計算プログラムが完成

次は、これをベースに数百レコード対象の
「税込売価一括計算プログラム」の開発

操作画面に[売価一括計算]ボタンを追加

一括税込売価計算プログラム（切上/切捨対応）がこれ
取りあえず8%食品用
[code lang=”vb”] Private Sub btn売価一括計算_Click()

Dim cn As ADODB.Connection
Set cn = Application.CurrentProject.Connection
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset

rs.Open "Tプライスカード", cn, adOpenKeyset, adLockOptimistic

Dim myOffPrice As Currency　’外税売価
Dim myOnPrice As Currency　’内税売価
Dim myTax As Single　’消費税率

myTax = 1.08

Do While rs.EOF = False

myOffPrice = rs![売価] myOnPrice = myOffPrice * myTax

Me![txtTSET2] = myOnPrice

If Me![opt切上] = True Then
rs![売価2税込] = IIf(myOnPrice = Int(myOnPrice), Int(myOnPrice), Int(myOnPrice + 1))
Else
rs![売価2税込] = Int(myOnPriceL)
End If
rs.Update

rs.MoveNext
Loop

rs.Close
cn.Close

End Sub
[/code]
普通に動きました

MS-Access
スゲ～便利！
開発、超簡単！

でもリボンインターフェイス大嫌い
開発画面、MS-Access2003の頃に戻してくれないかな・・・
開発が30%早くなるのにな

2021年2月24日
[vba]「流通BMS」開発ね・・・

久しぶりの新規MS-Access VBA開発
流通BMSソフトを１本作りました

ネットで調べた流通BMSのメリット

取引先との調整が簡単
開発・運用費低減
データ項目の追加が容易

このメリット、本当かな？

標準化されてるはずのBMSデータフォーマット
Aチェーンのデータと新しく開発を始めたCチェーンのデータを比較するとなんか違う！

並び順が全然違う！
Aに無い項目がCに有る！
Cに有る項目がAに無い！
AとCで区分コードが違う！
AとCで計算仕様が違う！

同じデータレイアウトを使う流通BMSソフト
１本作れば他チェーンでも使える夢の統一規格

的に想像してたが実際は違った

流通BMS以前と以後のソフト開発、同じじゃん！

ゼロからのコツコツ開発

MS-Accessで４時間ｘ５日＝約２０時間で「受注業務機能」が完成

出来たて新「Cチェーン流通BMSプログラム」がこれ

やっぱMS-Access、便利！
開発、めちゃ楽

外注に出せば２ヶ月２００万円くらいのソフトが仕事の合間利用で作れちゃう！

MS-Access
中小企業の救世主

残念、MS-OFFICEリボンインターフェイスが旧アイコンに戻れば言うこと無いんだけどな．．．．

リボン嫌い！
リボン最悪！
超使い難いぞリボン！

2021年2月12日
[vba]マジ、2021年4月までにプライスカード作り直し！

どうしよう？

突然、営業担当から相談された

2021年3月末までに
得意先のプライスカードを
内税価格に作り変える必要が！

ネットで調べると確かにそう書いてる！
猶予期間が終了し2021年4月1日から総額表示が義務化（困ったぞ）

なんでコロナ禍で販売激減中に
こんなこと強制させるんだ！

２，３年延期すべきでは

誰に言えばいいんだ？
プラカード持って霞が関に行けば
延期してくれるかな？

とりあえず「プライスカード発行」vbaプログラムを改良

「二重価格プライスカード」を作成

これが新しい操作画面

レポートデザインビューがこれ

「税抜価格」印字の為、「内税価格」印字スペースが縮小

苦肉の策で、最大5桁(99,999円)とし、VBAで4桁(1,000円)以上と以下でフォントサイズを変える
[code lang=”vb”] If Me![売価税込] >= 1000 Then
Me![売価税込].FontSize = 16
Else
Me![売価税込].FontSize = 18
End If
[/code]
完成した「二重価格プライスカード」がこれ
商品名全角20文字、価格は最大99,999円

データ収集は、私の自信作Androidアプリ「なんちゃってハンディターミナル」

さて、これで準備完了
後は、人をやり繰りし得意先のプライスカード情報を集めに行かねば・・・

コロナ＋増税で、売上減少と経費増の２重苦

これまでは無料サービス
でも今回、プライスカード作成費の一部だけでも負担していただけないかな？

2021年2月11日
[vba]新仕様bulk insertファイル生成プログラム完成

2020年6月30日「AppyBuilder Termination」直前、駆け込みで行った自作ハンディーターミナルアプリの改造。

[[ai]AppyBuilder termination(2020/7/1)]

変更点は、SQLite versionでbulk insertファイルを切替える

[[ai]SQLite versionでif文の分岐処理をするには(2020/6/11)]
3.8以下なら旧「500行ファイル」
3.9以上なら新「10000行ファイル」

この仕様変更でms-Access製「bulk insert生成プログラム」にも新機能

「10000行ファイル生成」

このプログラミング、結構時間がかかりました。

実は私、独学の我流vbaプログラマー
私のvbaプログラムは素人プログラム

１つのプロシージャに
長々（=だらだら）と色んな機能を書いてます

今回は心を少し入れ替え

3種類のbulk insertファイル生成機能をサブルーチン化

次期SQLite仕様変更で最大数が変わっても、変数を変えるだけで簡単に対応出来るように作り直しました。

見た目もスッキリ

やれば、僕にも出来るじゃん！
[vb] Private Sub btnMAKEbulkFile10k_Click()

call MakeBulkITEM (10000)
call MakeBulkSHOP (10000)
call MakeBulkRIREKI (10000)

End Sub
[/vb]

余談ですが、ずっと素人プログラムしか書けない言い訳は

プログラムを作るのも
改良するのも
操作するのも作者の私だから

本に書いてる「綿密な仕様設計」「プログラムの可読性」より

空いた時間に取り合えず作って即実践投入

を続けた結果。
100個以上作っても初心者みたいな書き方しか出来ません（泣）

永遠の素人

情けない

2020年7月4日
[vba]このaccdbファイルで車が買える！

システム屋に嘘をつかれて半死に・・・

IT経費を減らす為、中小規模のデータベースシステムは僕がMS-Accessで自作。
田舎にＵターンして１２年間、基幹業務システム以外のプログラム開発費はゼロ円！

これ、僕の自慢！

でも２０１９年の秋、軽減税率導入に伴い３つの得意先が同時にシステム更新。
さすがに３つの同時開発は出来ないので２つを担当、残り１つはプロへ外注！

その結果、大混乱に・・・

まぁ、会社は通常営業、苦しんだのは私１人ですが！

依頼してたプログラマーさんが体調不良で納期直前でリタイヤ。
後任プログラマーから

１１月下旬の完成は無理。
得意先のシステム会社にシステム切替を１２月下旬に延期してもらうよう依頼済み。
それでも、別件で忙しいので期限内の完成は無理。
だから、代わりにあなたがやって！

だってさ

まぁ、今使ってる受注システムは私作。
仕様はほぼ同じで送受信データのレイアウトが大きく違うだけ。
１ヶ月あれば余裕で作れそう！

納期数日前での開発放棄。
その尻拭いするのは気が重い。
でも、中型車１台分の見積額がゼロ円になるならと渋々引き受けたのが運の尽き！

ドツボに落ち、寿命が縮まった！

時間を作り月曜午後、火曜午後、木曜午後の半日作業３回（1.5日）でレイアウトが異なる新受注ファイルのインポート機能を開発。

金曜午後から既存システムのプログラムを新データレイアウト用に改造しようかなと思ってたら・・・・

ガーン、金曜に受発注システムが新サーバに切り替わってる！

後任プログラマーに電話すると

１ヶ月の延期は頼んだ。
でも了承はもらえていない！

マジで！
こいつ、本当にプロか？

ぎゃーぎゃー言っても、時間の無駄！

とにかく時間がない！
覚悟を決め超特急ひかり号スピードでプログラミング（昭和の比喩）

インポート機能が完成してたので無理すれば何とかなるかも・・・・

１：ピッキングリストのデータソースを新受注データに変え連結項目の調整・プログラムを書換え、急ぎ印刷。
　　ピッキング（＝出荷作業）は３時間かかるので、その間に次の開発
２：次に受注一覧表フォームのデータソースの切替・調整とプログラム変更。
３：次は物流シールのデータソースの切替・調整とプログラム変更。
　　シールを貼るのにプラス１５分かかるので、その間に次の開発
４：納品書のデータソースの切替・調整とプログラム変更。
５：物流センター提出書類類のデータソースの切替・調整とプログラム変更。
　　トラック積込みにプラス３０分かかるので、その間に次の開発

　　ここで出荷作業終了。会社も終業。

　　残り２つは、僕１人作業なので時間的余裕がある。
　　完成するまで僕だけが家に帰れないだけ！（苦しい）

６：最大の難関、出荷明細アップロードデータエクスポート機能の新規開発

　　テスト無しでのぶっつけ本番
　　エラーになっても向こうのシステム会社に問い合わせ出来ない。
　　なぜって、アップロードテストは３週間前に前任者のプログラムで終わっているから！　

　　５，６度失敗後、無事アップロード成功

７：基幹業務システムへの売上一括登録インポートCSVファイル生成機能の開発。

すべての作業が終わり家に帰ったのは２１時過ぎ（覚悟してたより早く帰れた！）

シンドいよ

８：土曜日午後、間に合わず手動で行った部分のプログラム開発

完成したMS-Accessプログラムが、これ（アイコンですが）
半日５回超（３日作業！）で僕の車1.5倍の見積額のプログラムが完成しました！
まぁ、もともとこの見積額が相場の３倍高いと思うんだけど・・・

本当はあと１日作業が必要

９：納品後に送られてくる受領データのインポート機能とその一覧表リストの開発

これで完成

猛烈なストレス下で神経をすり減らす４日分のプログラミング。

IT経費を抑えるのって身を切る努力が必要ですね！
疲れたよう
長く生きられそうにないな・・・

それにしてもMS-Accessすごい！
なんでも短期間で作れる超便利ソフト。
たまに止まったり壊れたりするけど、でも最強のソフトです。
リボンインターフェイスでなければより良いのに・・・

疲れた
あいつ許せん

2019年12月1日
[vba]2019年お歳暮商戦なのにGoogle static maps apiエラーで地図が出ない！

毎年恒例、契約企業からお歳暮配送依頼EXCELファイルが多数送られてきました。

ファイルはこんな感じ。
X軸が商品リスト、Y軸がお届け先。
交差する値が注文数。
このファイルから売上データ生成・納品伝票発行、仕入手配書・ピッキングリスト、配達先地図印字・・・・・

この大仕事、手作業では膨大過ぎて何人かかっても終われない！

やるのは僕１人、10年間カスタマイズを続けた相棒「お中元お歳暮管理」プログラム(MS-Access)を使います。

プログラムへインポートする為のデータ加工に2時間超かかりますが、準備できれば自慢のプログラムで自動処理！

でも今回・・・・

あれ、地図が作れない！
Google static maps apiを使った住所→緯度経度変換プログラムが動かない。
なんで？

いつもならこんな地図が企業別で都道府県ごとに出来るはずなのに・・・・

あ、そう言えばGoogle Cloud Platformからメールが来てたな・・・

お試し期間終了のお知らせメール
使い続けたければ「アップグレード」してください

アップグレードしたら有料会員か！
また、経費で認めてもらえない利用料を自腹払いすることに・・・・

アップグレード完了！
「アップグレードありがとう」メールが届きました。

その直後、住所→緯度経度変換機能が復活

やっぱ世の中甘くないですね！
Google Cloud Platformの無料利用はここまでです。

これからは有料だ！

ちなみに、無料会員だった先月の請求額がこれ！
驚きの金額!

9円

2019年11月28日
[覚書]vbaでクエリーを作成・削除する方法

納期の迫ったデータベース開発を急に担当することに・・・・（本当は過ぎてるかも）

年末商戦・お歳暮商戦で忙しい上でのAccess VBA開発、んんん～キツイな！

取り急ぎ、複合検索結果をRecordset化したい。
パラメータークエリーだと不安定なので検索の度に、パラメーター直書きクエリーを動的に作りたい！

「vba クエリー作成方法」をGoogle検索

vbaでクエリー作るのって以外と簡単なのね！
[vb] Option Compare Database

Private Sub btnクエリー削除_Click()

Dim myQuery As String
Dim Obj As AccessObject

myQuery = "Qクエリ生成テスト"

For Each Obj In CurrentData.AllQueries
If Obj.Name = myQuery Then
DoCmd.DeleteObject acQuery, myQuery
Exit Sub
End If
Next

End Sub

Private Sub btnクエリ作成_Click()
Dim Qdf As QueryDef
Dim SQL As String
SQL = "SELECT 商品CD,商品名,JAN1 FROM システムM商品"
Set Qdf = CurrentDb.CreateQueryDef("Qクエリ生成テスト", SQL)
Set Qdf = Nothing
End Sub
[/vb] テストフォーム

スクリプトがこれ

クエリが出来ました。
削除ボタンで消すことも出来ます。

関係ないですが、PowerPointみたいな派手なグラデーションボタンが嫌い！
以前の地味なグレーボタンってどう作るの？
データベースの操作画面は地味がいいと思う。

2019年11月27日
[vba]「クエリが破損」エラーでAccess全滅！(officeダウングレードで解決：C2R形式)

今日はブラックマンデーだ！

先週2019年11月13日、

Windows10 2019年秋のupdateを当てたらAccessプログラムが壊れた！
クエリー破損エラーで動かない!

とAccessコミュニティで大騒ぎ！
[2019年11月アップデート導入後「クエリが破損しています。」エラーの発生について]

でも僕のプログラム、13日14日15日16日と4日経っても問題が起きず

僕のVBAプログラムの書き方だと問題起きないのかも・・・・

と高をくくってました。

それが今日2019年11月18日(月)の朝、
5日遅れで「クエリ破損」隕石の直撃！
大パニック

仕入発注プログラム、EDIプログラム、通販管理・・・・・
全てのプログラムが動かない！

クエリ破損
クエリ破損
クエリ破損
クエリ破損
何をしてもクエリ破損！

エラーが起きるのはVBAから更新クエリー(UPDATE文)を実行する行！
時間があれば、その個所をRecordsetでupdateすればエラー回避できそう！

でも、問題個所が多すぎてすぐには直せない！
月曜の朝一、やること山積みなのに何も進まない・・・

時間だけが過ぎていく・・・・（困った）

作戦変更、プログラム修正を諦めwindows updateのアンインストール

ネット情報によると

該当アップデートパッチ
office2016:KB4484113
office2013:KB4484119
office2010:KB4484127
をアンインストールすれば直る

でも、私のWindows10には該当パッチがインストールされていない！

なんで？
なんで？
なんで？

ダメもとで11月13日以降のパッチを全てアンインストールしてもやはりダメ！

あれ、ふと疑問が・・・・

なんで僕のPCには該当パッチが無いの？

その理由をGoogle検索すると

Officeのインストーラーは２種類あるらしい。
・MSI(インストーラ)形式：従来のインストーラー
・C2R(Click to Run)形式：ネット経由のダウンロード用

私はダウンロード版を購入したのでC2R形式

C2R形式にはパッチが無いそうな！
なるほど、だから該当パッチが無いんだ

ここで昼休み。

結局、午前中はすべき仕事の1/4も出来ませんでした

弁当食べながらGoogle検索「Access クエリ　破損　C2R形式」

で、やっと解決策が書かれたページにたどり着きました
[『クエリ’’は破損しています』を回避する方法–『まる901』]

解決方法は、Officeダウングレード

Step1：ダウングレードしたいビルド番号を調べる

下記のページでダウングレードしたいOffice2016のビルド番号を調べる
[Office 365 ProPlus の更新履歴—-microsoft]

問題の最新バージョン：1910（ビルド：12130.20344)リリース日：2019/11/12
ひとつ前のバージョン：1910（ビルド：12130.20272)リリース日：2019/10/30

ターゲットビルドは、1910（ビルド：12130.20272)に決定！

Step2：DOS窓でofficec2rclient.exeを実行

cd(change directory)コマンドでexeディレクトリーへ移動
※pathが通っていれば不要

cd C:\Program Files\Common Files\Microsoft Shared\ClickToRun

オプションにターゲットビルド番号を指定してofficec2rclient.exeを実行

officec2rclient.exe /update user updatetoversion=16.0.12130.20272

実行中の様子

インストールが正常終了

ダウングレード成功！

Accessのバージョンは、無事1910（ビルド：12130.20272)

最後に、問題の最新ビルドに自動更新されないよう「自動更新無効」に設定

今日の午前中は、キツかった

2019年11月18日
[EDI準備]同業他社システムさんと意見交換！衝撃の現実が・・・

得意先の新EDIシステム切替日が決まった！

やばい
早くEDIプログラム、完成させないと怒らえる！

「お盆商戦」「お中元商戦」「猛暑需要」「増税前駆込み需要」「軽減税率対応ソフト動作検証」などなど、忙しすぎて後回しにしてた２件のEDI受注システム開発！

１社は、自社開発を諦め外注（数百万の見積もり、高すぎないか？）

もう１社は、仕事の合間と寝る間を削って開発中（疲れたよ）

言い訳ですが、たかがEDI開発
するこは基本４つ
１：受注データ（テキストファイル）インポート
２：実出荷数入力画面作成
３：実出荷数を印字する納品書レポート作成
４：アップロード用EDI実出荷数データ（テキストファイル）作成
平日営業時間、開発に集中出来るなら営業日10日で出来るボリューム。

でも、それが許されない貧乏会社
開発は、帰宅後か週末(泣)

ようやくプログラムの初期バージョンが完成
EDI出荷データのアップロードテストは90%以上で成功！

後は、最終調整（＝仕上げ）だけ

同業他社のシステムさんと情報交換したくて電話。
その会話で、衝撃の現実を知り愕然！

え、マジで！

同業他社では専門スタッフ数人でEDIプログラムをチーム開発。
テストを繰り返し、後は開始日を待つだけの余裕状態。

ガーーーン、普通の企業って
専門スタッフのチーム開発！

形部商事は、夜な夜な１人でコツコツ開発。
EDI開発チーム名

チーム：ひとり

漫才師「劇団ひとり」みたい・・・
これが大手と中小の違いですね！

まぁ、逆に考えれば、この１０年で開発したEDIシステムは10本超

１人の努力で1500～2000万円くらい
IT経費節約できましたかも・・

でも、何もかも１人でやるのって限界が来る！

それが今年かも

マジ、限界・・・
疲れた

2019年10月12日
[vbaエラー]開始位置32916が有効ではありません。開始位置は32767以内で・・

本業終了後、夜な夜な得意先に導入されるEDIシステムへの対応プログラムを開発。

疲れたよ！

得意先から渡された仕様書を読むと

業界標準の流通BMIシステム

業界標準ってどういう意味かと思ったら、何のことは無い
全業種に対応するために無駄にたくさんの項目が用意されたテーブルを持つシステムでした！

水域・産地名・等級・養殖区分・解凍・部位・カラー名称・・・・

9割以上、加工食品卸業に関係ない項目。

すごくプログラムし難いんですけど、これ！

取り合えずEDIアップロード用「実出荷データ」を用意しcsvエクスポートテスト・・・

すると、見たことのないエラー

フィールドF134の開始位置32916が有効ではありません。
開始位置は32767以内で指定してください。

何これ？

原因究明の為、項目文字数・項目数を変えながら何度もテストしたら・・・・・・

エラー原因、わかりました！

項目数が多すぎてMS-Access君がエクスポート用定義を作れないみたい！

なんだ、そんな原因ならTextStreamを使えばいいじゃん！

select caseで項目ごとの固定値も簡単に指定できる。

流通BMI開発は、TextStreamで楽勝ね！
逆に言えば、項目がバカみたいに多い流通BMI、
TextStreamを知らないと開発出来ません。
[vb] Private Sub btnEDIexp_Click()

Dim i As Integer
Dim FnameS As String

i = 0
FnameS = "C:\katabe\EDIupload.csv"

Dim FSO As New FileSystemObject
Dim f As File
Dim st As TextStream

Set st = FSO.CreateTextFile("C:\katabe\EDIupload.csv")

Dim cn As ADODB.Connection
Set cn = Application.CurrentProject.Connection
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset

rs.Open "select * from QEDIエクスポートソース", cn, adOpenKeyset, adLockOptimistic

Dim myTEXT As String

myTEXT = ""

‘検証用ヘッダー部

For i = 1 To 188

If i = 188 Then
myTEXT = myTEXT & "F" & i
Else
myTEXT = myTEXT & "F" & i & ","
End If
Next i

st.WriteLine (myTEXT)

Do While rs.EOF = False
myTEXT = ""
For i = 0 To 187

Select Case i
Case 0
myTEXT = rs.Fields(i)
Case 4, 8, 9, 13, 26, 39, 45, 98, 100, 111, 123, 132, 139, 142, 145, 153, 161
myTEXT = myTEXT & "," & rs.Fields(i)
Case 5
myTEXT = myTEXT & ",00" ‘固定00
Case 19
myTEXT = myTEXT & ",03" ‘固定03
Case 33
myTEXT = myTEXT & ",02" ‘固定02
Case 66
myTEXT = myTEXT & ",999" ‘固定999
Case 84
myTEXT = myTEXT & ",00" ‘固定00
Case 89
myTEXT = myTEXT & ",01" ‘固定01
Case 133
myTEXT = myTEXT & "," & Date()
Case 155
myTEXT = myTEXT & ",1" ‘固定1
Case Else
myTEXT = myTEXT & ","
End Select

Next i

st.WriteLine (myTEXT)

rs.MoveNext

Loop

rs.Close
cn.Close

st.Close

Call Shell("notepad.exe C:\katabe\EDIupdate.csv", 1)

End Sub
[/vb]
これまでの物件は全てヘッダー・フッターが必要だった為、最初からTextStreamで開発してました。
その為、今回のエラーに遭遇しなかったのかな？

2019年9月13日

カテゴリー: VisualBasicApplication

Step1：ダウングレードしたいビルド番号を調べる

Step2：DOS窓でofficec2rclient.exeを実行