スモールビジネスのIT日誌

• (2009-03-01 12:13:59)

クエリを生成するDAOオブジェクト「QueryDef」

-------------------------------

「日付を変更するVB」ではPCの日付を強引に戻してバックデイトの処理をするケース。難点は日付の戻し忘れ。処理中に電話やインターラプトが入って作業に戻ってくるとPCの日付を変更したことを忘れていて災難に合うことがある。そのためPCに日付は操作せず、データベースでバックデイト日付のデータ抽出する方式も用意した。

VBAのInputBox関数で目的の日付データを入力。それを日付パラメーターとしてSQL文を生成、バーチャルなクエリテーブルを展開。それをエクスポート(それはそのまま会計ソフトのインポート用データとなる)。

会計ソフトインポート用のデータとしては会計ソフトの規定するフォーマットに整形する必要があるが、それは秀丸マクロで行う。まずは目的の日付の会計データをエクスポートするまでの自動化を目指す。

テンポラリーなクエリを生成するために、はじめて「QueryDef」というDAOオブジェクトを使用した。Microsoft社の周到な準備に今回もひどく感動した。しかし、QueryDefはいったい何なのかよくわからない。Defが何の略かも調べられなかった。今後おいおいわかってくるかも。

Dim DB As DAO.Database

Dim qdf As DAO.QueryDef

Set DB = CurrentDb

strDate = InputBox("例[月日]:02/20")

strSQL = "SELECT [受注テーブル].受信日時, [受注テーブル].請求額 FROM" & vbCrLf & _

"受注テーブル WHERE ((([受注テーブル].受信日時)" & vbCrLf & _

"Like '2009/" & strDate & "*');"

On Error GoTo ERROR_Sub

'-------------------------------

'会計ソフトに落とすための一時的なクエリ生成

'-------------------------------

Set qdf = DB.CreateQueryDef("会計日次処理バックデイト", strSQL)

'-------------------------------

'生成された会計ソフトインポート用データのエクスポート

'-------------------------------

DoCmd.TransferText acExportDelim, "", "会計日次処理バックデイト", "C:\会計日次処理バックデイト.txt", False, ""

'-------------------------------

'生成されたクエリは次回の本プログラム起動時に残っている。

'同名のクエリが存在すると3012おいうエラーになるので、それを削除

'-------------------------------

ERROR_Sub:

If Err.Number = 3012 Then '3012はすでに同名のクエリが存在する場合

DB.QueryDefs.Delete "会計日次処理バックデイト"

Resume

End If

qdf.Close: Set qdf = Nothing

DB.Close: Set DB = Nothing

• (2009-03-01 11:46:55)

(備忘録) Illustrator に移動

• (2009-02-25 17:41:25)

DOSコマンドを叩くだけのVBでも恩恵は多い

-------------------------------

PCの日付を変えてバックデートでデイリーの売上集計(日次売上処理)をすることがある。日次の集計だから毎日、業務の終わりにすることが商売の本筋ではあるが、翌日キャンセルや決済未完了や配送先不明瞭で出荷しなかったりと多少の例外が発生する。

日次で売上を閉めると数日後、経理情報の修正や訂正作業が発生する。最終的な不良債権は別として初期のトラブルや例外処理でペンディング処理になるものも注文後48時間程度でかなりの確率で状況がフィックスする。

そんなかんだで日次集計を数日後に行うようにした。数日後に日次集計を行うデメリットは「当日」前提でプログラムを書いているので、日にち入力型のプログラムに変更するか、PCの日付をマニュアルで戻すかである。とりえあず集計プログラムは変更せずにPCに日付を戻す運用でやる。

DOS窓で「date」コマンドを打ち込んでいたが、コマンドラインの打ち込み負担や日付の戻し忘れが発生。自分がやるならまだしも経理スタッフに任せるには大変。VBでワンクリックでバックデートになるプログラムを制作した。

中身はDOSのコマンドを発行するだけのものだが役立つ。案外時間がかかったのが正しい日時に戻すプログラム。「桜時計」などntp系のフリーソフトが多数インターネットで無償公開されているので楽勝ではないかと予想していた。

しかし、ntpサーバを利用して時刻合わせを行う単発のコマンドラインはないようだ。unix系ならあるのだろうか？しかしあるにせよそれは使えない。簡単なようで実はntpのプロトコルを理解した上でそれなりのネットワークプログラミングが必要なようだ。私には無理だった。

vectorでコマンドラインから時刻合わせをしてくれるプログラムを見つけた。下記のプログラムを利用させてもらうことにした。

「超シンプル時刻合わせ」

setntptime

作成した日付変更プログラム(DOSコマンドを叩くだけのVB)：

・指定日にバックデート

Shiteibi = Text1.Text

Shell ("cmd.exe /c date " & Shiteibi)

・日時合わせ

Shell ("cmd.exe /c C:\setntptime.exe")

• (2009-02-22 10:19:12)

クエリデータの更新ではまる。

-------------------------------

数日前「ACCESSから直接メール送信」で新しく作成したクエリでははじめメールの送信結果の書き込みができなかった。調べていくとどうもクエリの「演算フィールド」で結合したテーブルのデータは更新できないようだ。「演算フィールドはテーブル間結合の結合子として使用することはできない」ということだろうか。

演算フィールドそのものがデータ更新を禁止される理由は理解できる。更新できれば演算に使用したオリジナルのデータとの整合性がとれなくなる。これはアプリケーションで禁止した方が安全。しかし、演算フィールドで結合されたテーブルのうち、データ整合性には問題ないフィールドもあるので全フィールド禁止という理由がわからずいろいろ考えてみた。結論は出なかった。

時間ばかり食うのでおそらく「テーブル間結合の結合子として演算フィールドを使ってはならない」と解釈した方が早い。苦肉の策として演算フィールドを止めて演算結果を「更新クエリ」でオリジナルテーブルに再書き込みを行う。つまり、クエリ内の演算で生成された仮想テーブル内のダイナミック・データをいったんオリジナルのリアルテーブル内でフィックスさせてしまう。こうしてなんとか更新は可能となった。

ワンアクション多くなるところが若干美しくないがやむ得ない。

-------------------------------

後日談:Microsoft社のWEBで下記の記述を見つける：

"フォームやデータ アクセス ページではデータを更新できます。フォームの [レコードセット] プロパティを [ダイナセット (矛盾を許す)] に設定すると、クエリに基づいてデータを更新できます。"

フォームのレコードセットのプロパティを[ダイナセット]から[ダイナセット(矛盾を許す)] にすると動くようになった。嬉しいが、「矛盾を許」していいのか、どうなのか？理論的に考えて矛盾は起きないのだから「許す」でよいの判断している。

• (2009-02-20 19:23:08)

ExcelやACCESSから直接メールを送信することで仕事の効率化とミスの軽減を目指す

-------------------------------

●紙の「領収書」よりメールによる通知を望む声が多くなっている。

コンビニ決済に関しては支払い時にコンビニにて半券の領収書が返ってくる。領収書があるので入金確認通知メール」は発行していなかった。ところが「メールでも入金確認の通知がほしい」という要望が多くなった。メールユーザーは増加の一方。使い方もより生活密着型になってきている。日常生活の活動記録をメールに依存するユーザーも多く、紙切れ「領収書」より、「デジタル」での記録の方が好まれるようになったのだろうか？紙切れは紛失するし、検索ができないことが理由ではなかろうか。

●ACCESSからダイレクトに自動送信で作業負担軽減。失敗やミスも軽減。

当社では受注管理はACCESSで行っている。

「このACCESSから直接、入金確認通知メールを自動発行することは可能だろうか？」

が今日のテーマ。調べるとACCESSには「SendObject」というメソッドが準備されているではないか。これはACCESSから直接インターネット上のメールサーバーを呼び出し、データをそのサーバに渡すという作業をやってくれるプログラム。至極便利でありがたい。

SendObjectメソッドはおそらくExcelにはなかったように思う。というのは商品の発送に関してはヤマト運輸送り状発行ソフトB2から管理番号をExcelにエクスポートし、Excelからダイレクトに「商品発送通知メール」を自動発行している。そのプログラム作成時に調べたことがあるが、SendObjectメソッド相当のものは発見できなかった。

それでExcelからの「商品発送通知メール」は有名な「Basp21」を利用させてもらっている。SendObjectオブジェクト(メソッドと呼ぶ方が適切か？)の存在はうれしかったが、ExcelとVBAコードをなるべく統一するために今回は「Basp21」を使用することにした。SendObjectは後日試したい。

●「Basp21」でACCESSからメール自動発行プログラムを組む。

当社の場合、コンビニ決済の決済データはネットからCSVデータで一括ダウンロード。これを一発インポートでテーブルに取り込みクエリに展開。受注テーブルと内部結合してメールアドレスやご注文日を抽出する。

下記コード内のstrTEXT.txtは挨拶などが書かれたテキストファイル。これもVBで自動読み込みを行う。テキストファイルの読み込みに関してはFileSystemObjectオブジェクト(メソッドとオブジェクトの違いは私には不明)という便利なプログラムが準備されている。感謝したい。下記がコードの骨格部分の抜粋。

'オブジェクト(Basp21、FileSystemObjecなど)を割り当て：

Set objBsp = CreateObject("Basp21")

Set FSO = CreateObject("Scripting.FileSystemObject")

Set objTs = FSO.OpenTextFile("C:\strTEXT.txt")

'挨拶文の全文読み込み：

Do While Not objTs.AtEndOfStream

strTEXT = objTs.ReadAll

Loop

'データベースとその中のクエリ(コンビニ決済データと受注データのダイナセット)を割り当て：

Dim DB As Database

Dim RS As Recordset

Set DB = CurrentDb

Set RS = DB.OpenRecordset("○○クエリ", dbOpenDynaset)

'1レコードごとに各フィールドを読み込み、Basp21にメール送信。

'送信結果として「入金確認メール」にフラグを立てる

Do Until RS.EOF

strFlg = RS![入金確認メール]

strTck = RS![トラッキングナンバー]

strAdr = RS![顧客メールアドレス]

strNam = RS![顧客氏名]

RS.Edit

If strFlg = "送信待ち" Then

strTo = strNam & "<" & strAdr & ">"

strBdy = strNam & "さま" & vbCrLf & vbCrLf & strTEXT

ret = objBsp.SendMail(strSvr, strTo, strFrm, strSbj, strBdy, strFle)

If ret = "" Then

RS![入金確認メール] = "送信完了"

Else

RS![入金確認メール] = "エラー"

End If

End If

RS.Update

RS.MoveNext

Loop

MsgBox "送信完了しました。"