2014年6月30日月曜日

MS Access のVBAモジュールを全てエクスポート

Microsoft Access のVBAモジュール（プログラム ソースコード）を一括でファイルにエクスポートするには、VBComponent.Export メソッドを使います。
下にサンプルを挙げますが、参照設定として以下の２つが人必要です。
  • Microsoft Visual Basic for Applications Extensibility
  • Microsoft Scripting Runtime
参照設定は、Visual Basic Editor(VBE)のメニューから、ツール(T) ＞ 参照設定(R) と辿ります。

VBAモジュールをファイルにエクスポートするサンプル
Sub ExportComponents()
    Dim Fso As New Scripting.FileSystemObject
    Dim OutDir As String

    OutDir = Fso.GetParentFolderName(CurrentDb.Name) & "\src"
    If Not Fso.FolderExists(OutDir) Then
        Fso.CreateFolder (OutDir)
    End If

    Dim Component As VBComponent
    For Each Component In Application.VBE.ActiveVBProject.VBComponents
        Component.Export Filename:=OutDir & "\" & Component.Name & GetExtension(Component.Type)
    Next
End Sub

Function GetExtension(ComponentType As vbext_ComponentType) As String
    Select Case ComponentType 
    Case vbext_ComponentType.vbext_ct_ClassModule
        GetExtension= ".cls"
    Case vbext_ComponentType.vbext_ct_MSForm
        GetExtension= ".frm"
    Case vbext_ComponentType.vbext_ct_StdModule
        GetExtension= ".bas"
    Case vbext_ComponentType.vbext_ct_ActiveXDesigner
        GetExtension= ".cls"
    Case vbext_ComponentType.vbext_ct_Document
        GetExtension= ".cls"
    End Select
End Function
時刻:

MS Access を使った大規模な開発のリスク

Microsoft Accessで大規模なデータベースを構築するのは無茶ですが、コストに負けて採用してしまうことはある。それでも MS Access を使うことになった場合は、以下のことに気を付けるべきです。

１．データサイズ

Access の仕様として公開されている制限があります。
  • Access データベース (.accdb) ファイルのサイズは 2GB
  • テーブルのサイズも 2GB
Access 2010 の仕様
http://office.microsoft.com/ja-jp/access-help/HA010341462.aspx

運用中に 2GB に到達してしまうと、データベースファイルはほぼ確実に破損します。ファイルを分割すれば、ファイルサイズの制限は解決できるかもしれませんが、テーブルサイズの制限を超過してしまう場合は、テーブルを分割して解決できるでしょうか？

じつは仕様書には書かれていない制限もいくつかあります。例えば、
  • クエリ結果のサイズ（下記を参照）
  • 連続で開く処理と閉じる処理を行うと、データファイルが破損する。（下記を参照）
これ以外にも MS Accessで大量のデータを扱うには以下の懸念があります。
  • オプティマイザー、統計情報が貧弱
  • 最適な実行計画に誘導することができない
  • SQLチューニングのツールが無い
また分割したテーブルを UNION 統合する必要があるならば検索時間は長くなり、大量のデータで処理回数が増加すれば処理が終わらないという問題が出てきます。テーブルのサイズ上限は Access の採用可否の判断材料とすべきです。

２．クエリサイズ

エラーメッセージ：「3183 DAO.Database クエリを完了できません。クエリ結果のサイズがデータベースの最大サイズ (2GB) より大きいか、クエリ結果を一時的に保存するディスクの空き容量が不足しています。」

クエリの仕様として Accessの仕様書にも書くべきだと思うが、クエリにも制限があります。さらに「クエリ結果のサイズ」が指すものが曖昧で、出力結果のサイズでもあり、内部の一時領域でもある様子です。内部的にはどういう実行計画になるか分からないので、この制限を意識するデータ量になるならば、Access の採用可否の判断材料とすべきです。

３．連続で開く処理と閉じる処理を行うと、データファイルが破損する

これ言い換えると、「繰り返しのベースとなるレコードが 40,000 件を超えており、データファイルが分割されているため処理毎に接続を切り替えるとデータファイルが破損する。」これも 40,000件 をAccess の採用可否の判断材料としたほうが無難です、理由は下記に挙げるように想定外の問題が多いため。

http://support.microsoft.com/kb/283849
Access 2002 以降の破損したデータベースをトラブルシューティングおよび修復する方法
ループ処理の中で、開く処理と閉じる処理を大量に行うことを避けます (40,000 回を超えて、連続で開く処理と閉じる処理を行うと破損する場合があります)。

この対策としては、接続したままにする手段がある。
  • リンクテーブルを開いたままにする
  • DAO.DBEngine.OpenDatabaseでデータベースを開いたままにする

http://office.microsoft.com/ja-jp/access-help/HP005187453.aspx
Access データベースのパフォーマンスを向上させる
リンクしたデータベースを開いたままにしておくと、メイン データベース、テーブル、およびフォームを開く際のパフォーマンスが向上します。（中略）また、関連する .ldb ファイルの作成や削除を防ぐこともできます。

ただしリンクテーブルとDAO.DBEngine.OpenDatabase は接続が衝突する。
エラーメッセージ：「3734 DAO.Workspace マシン 'localhost' のユーザ 'Admin' がデータベースを開けない状態、またはロックできない状態にしています。」

それに繰り返し回数が少なくても、必ずしも安全ではないのは以下の通り。
エラーメッセージ：「3051 DAO.Database ファイル 'C:\work\Sample.laccdb' を開くか、書き込むことができませんでした。他のユーザが排他的に開いているか、データの表示と書き込みを行う権限がありません。」
排他モードでなくともシングルユーザでの接続を繰り返すと発生する可能性がある。ロックファイル(laccdb)のオーバーヘッドは無視できないと考えたほうが良い。データベースを同時に使用できるユーザーの数は 255 まで可能である仕様なのは本当なのだろうか？
時刻:

MS Access の実行計画を参照する

Microsoft Access にもオプティマイザと統計情報はある。ただし最適な実行計画に誘導することはできないので、検索速度の不安定リスクは消えない。

Access 2000 でクエリを最適化する方法
http://support.microsoft.com/kb/209126/ja

MS Access における検索のパフォーマンス改善は、SQL について対策するのであれば Rushmore を使えるようにすることが第一の目標となる。オプティマイザが決定した実行計画を出力するには、レジストリのキーを設定することで操作できる。
SQLの実行毎に、showplan.out ファイルがユーザのマイ ドキュメント(C:\Users\UserName\Documents)もしくは、カレントディレクトリに出力されます。ここに using rushmore と出力されると、クエリの検索で Rushmore が使えたことが分かる。
  1. キーを追加する （※ Access 2007 の場合）
    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\12.0\Access Connectivity Engine\Engines\Debug
  2. このキーに文字列値を追加する
    JETSHOWPLAN=ON
JETSHOWPLAN in Access2013 （※ Access 2013 の場合）
http://social.msdn.microsoft.com/Forums/office/en-US/b786a029-fa8c-4556-a40c-e749ba73499e/jetshowplan-in-access2013

出力例１（主キーの場合）

- Inputs to Query -
Table 'TEST_TABLE1'
- End inputs to Query -

01) Restrict rows of table TEST_TABLE1
      using index 'Pri-Key'
      for expression "USER_NAME='User1'"

出力例２（rushmoreの場合）

- Inputs to Query -
Table 'TEST_TABLE1'
- End inputs to Query -

01) Restrict rows of table TEST_TABLE1
      using rushmore
      for expression "USER_NAME='User1'"
02) Group result of '01)'

ただし SHOWPLAN の出力は恐ろしく使えない。例えば、
  • 複数のSQLを実行したら、どのSQLの実行計画か全く分からない
    複数のテーブルを結合すると、Temp02 とか内部の名称で出力されるので、アプリケーションのボトルネックとなる SQL をコストの重い順に探すような使い方はできない
  • SHOWPLAN についてのリファレンスが MSDN に無い！
  • 出力内容が恐ろしく薄い
    SQLの区別や処理時間やコストなど、チューニングに必要な情報が出力されない
これでは、検索の実行計画に Rushmore を使ったかどうかのチェックを、SQL の１つ毎に確認する程度の使い方しかできない。


Rushmore や、これ以外のパフォーマンスチューニングについての資料など。

Access データベースのパフォーマンスを向上させる
http://office.microsoft.com/ja-jp/access-help/HP005187453.aspx

Access のパフォーマンスを向上させる
http://office.microsoft.com/ja-jp/access-help/HA010235589.aspx

インデックスの作成と使用によりパフォーマンスを向上させる
http://office.microsoft.com/ja-jp/access-help/HA010210347.aspx

ACC: Access 2.0、Access 95 および Microsoft Access 97 でクエリを最適化する方法
http://support.microsoft.com/kb/112112

Visual Basic でクエリーを最適化する方法
http://support.microsoft.com/kb/172199/ja

Using Rushmore Query Optimization to Speed Data Access
http://msdn.microsoft.com/en-us/library/aa975907.aspx
時刻:

2014年6月29日日曜日

[VBA] 文字列と数値を比較しても、エラーにならないケース

VBAでは、関数の戻り値と異なるデータ型を比較してもエラーとならない。
例えば
If 3 < Left("ABC", 1) Then
  Debug.Print "3 < A"
Else
  Debug.Print "3 >= A"
End If

＜処理結果＞
3 < A


ただし、数値と文字のリテラルを比較すると実行時エラーとなるので、方式が統一されているわけではない。
例えば
If 3 < "A" Then
  Debug.Print "3 < A"
End If

＜処理結果＞
実行時エラー13：型が一致しません。
時刻:

[MS Access] SQLのユーザ定義関数で Err.Raise しても捕まえられない

Microsoft Access で、SQLのユーザ定義関数の中でランタイム エラーを生成する場合は、Err.Raise メソッドではSQLを実行した側でエラー処理ルーチン (On Error GoTo) で捕まえることができない。

ランタイム エラーを捕まえられないと、VBAのエラーダイアログが表示されてしまう。SQLの中で発生したランタイム エラーを捕まえるには、CVErr関数でユーザー指定のエラーを戻すことになります。ただし欠点があり、関数戻り値のデータ型をVariant型に変更する必要があります。Variant型で数値を戻すと、SQL中では文字列として扱われるので厄介なのです。

１．ランタイム エラーを捕まえられないケース

Public Function Func1(Value As Integer) As Integer
  Err.Raise Number:=100, Source:="UserDefined", Description:="メッセージ"
  Func1 = Value
End Function

Sub Test1()
  On Error GoTo ErrorHandler
  Call CurrentDb.OpenRecordset("SELECT Func1(123)")
  Exit Sub

ErrorHandler:
  ' ここには到達できない。
  Debug.Print Err.Number & vbTab & Err.Source & vbTab & Err.Description
End Sub

２．CVErr関数の戻り値を返すケース

Public Function Func2(Value As Integer) As Variant
  Func2 = CVErr(100)
End Function

Sub Test2()
  On Error GoTo ErrorHandler
  Call CurrentDb.OpenRecordset("SELECT Func2(123)")
  Exit Sub

ErrorHandler:
  ' ここを通ることができる。
  Debug.Print Err.Number & vbTab & Err.Source & vbTab & Err.Description
End Sub
時刻:

2014年6月28日土曜日

VBAの関数は、Nullでもエラーにならない

VBAの関数で引数にNullを指定してもエラーとならないのは、深い知識が無くても使えるための言語仕様だと思うけど、想定外の結果を招くことがある。
例えば
If Month(Null) > 3 Then
  Debug.Print "Null > 3"
Else
  Debug.Print "Null <= 3 !?"
End If

＜処理結果＞
Null <= 3 !?
こういう場合に Else だけでは想定外の結果となる。


ただし、数値と文字の比較ではエラーとなる。
例えば
If 3 < "" Then Debug.Print "3 < ''"

＜処理結果＞
13 VBAProject 型が一致しません。
時刻:

VBAの繰り返し処理内で変数を定義しても、２回目以降が初期化されない

VBAの繰り返し処理内で変数を定義しても、初期化は初回だけで２回目以降が初期化されない。
初回だけは初期化されているので誤魔化されてしまうため、余計に注意が必要です。

Dim i As Integer
For i = 1 To 5
  ' 繰り返し処理内で変数定義
  Dim Value1 As String ' 初期化されない！
  Dim Value2 As Integer ' 初期化されない！
  If i Mod 2 = 0 Then
    ' 偶数
    Value1 = CStr(i)
    Value2 = i
  End If

  Debug.Print i & "回目" & vbTab & Value2 & vbTab & Value2
Next

＜処理結果＞
1回目 0 0 ← 初期化されている
2回目 2 2
3回目 2 2 ← 初期化されていない！
4回目 4 4
5回目 4 4 ← 初期化されていない！
時刻:

Kernel-Power 41 エラーの PowerButtonTimestamp を日時に変換する

Windows で電源ボタンを長押し（4秒以上）して電源切断した場合のシステムイベントログ "Kernel-Power 41" に含まれる PowerButtonTimestamp の値ですが、Windowsの時刻の起点 (1900年1月1日 0時0分0秒 (UTC)) を基準に算出しても全く合わない。仕様を探してもまるで情報が見つかりませんでした。

これを逆算して起点を算出すると、以下のような結果になる。
  • 時刻の起点は 1601/01/01 09:00:00 (GMT+0900)
  • タイムスタンプの単位は 100ナノ秒

この方法で PowerButtonTimestamp の日時を算出します。

結果：  

時刻:

2014年6月27日金曜日

Windows パフォーマンスモニターのログ頻度を調整する

パフォーマンスモニターを何時間も継続して走行していると、ログのサイズが大きくなって分析が困難です。サンプルの間隔を調整することで、効率的に運用できます。この間隔については、Microsoft からガイドラインが提示されています。

http://technet.microsoft.com/ja-jp/magazine/2008.08.pulse.aspx
通常の運用時、ベースラインを確立するための適切なサンプリング間隔は 15 分です。問題が発生するまでの平均経過時間が約 4 時間の場合は、サンプリング間隔を 15 秒に設定します。問題が発生するまでの時間が 8 時間以上の場合は、サンプリング間隔を 5 分以上に設定します。

データコレクタには３種類（下記を参照）ありますが、このうち調整を行えるのは「パフォーマンス カウンター」のみです。
調整するには、
  1. パフォーマンスモニターにユーザ定義として作成した「データコレクタセット」を選択する。
  2. 定義に含めたデータコレクタのうち、「パフォーマンス カウンター」であるものを右クリックして、プロパティを選択
  3. プロパティの設定画面で、「パフォーマンス カウンター」タブを開いて、「サンプルの間隔」を変更します。

（参考）データコレクタの一覧
  • パフォーマンス カウンター
  • イベント トレース データ
  • システム構成情報
時刻:

2014年6月21日土曜日

Windows標準の機能で動作をモニタリングする

なるべくWindows標準の機能でモニタリングするしかないときに有用なツールのまとめ

・リソース モニター

Windowsの[Start] ＞ アクセサリ ＞ システムツール ＞ リソース モニター
http://technet.microsoft.com/ja-jp/windows/ee350564.aspx
CPU、メモリ、ディスク、ネットワーク のモニタリングが簡単にできるが、特徴としてリアルタイムで確認できる反面、ずっと見張っていないと問題を見逃してしまう。

・パフォーマンス モニター

コントロール パネル ＞ システムとセキュリティ ＞ 管理ツール ＞ パフォーマンス モニター
http://technet.microsoft.com/ja-jp/library/cc749115.aspx
http://technet.microsoft.com/ja-jp/library/cc749249.aspx
デフォルトの「データ コレクター セット」でモニタを開始することもできるが、通常は「データ コレクター セット」のユーザ定義を準備する。特徴としては詳細な情報をモニタリングできるが、走行中は結果を確認できない。
かなり詳細なデータ コレクターが揃っているが、各データ コレクターのリファレンスは残念ながら見つけられません。

主なデータ コレクターは以下
  • CPU（Processor）
  • メモリ（Memory）
  • ディスク（物理：PhysicalDisk ／論理：LogicalDisk）
  • ネットワーク（ICMP, TCP, IP, UDP, etc.）
  • 他にもたくさん

・Firewall のログ

コントロール パネル ＞ システムとセキュリティ ＞ Windows ファイアウォール ＞ 詳細設定
「セキュリティが強化された Windows ファイアウォール」画面の「監視」メニューから、
ログ設定を開始する。
ネットワークのトラブルを解析する情報が得られます。ログできる情報は以下
  • 破棄されたパケット
  • 正常な接続

・ODBCのトレースログ

http://support.microsoft.com/kb/274551/ja
コントロール パネル ＞ システムとセキュリティ ＞ 管理ツール ＞ データ ソース (ODBC)
「ODBC データソース アドミニストレーター」画面の「トレース」タブから、「トレースの開始」ボタンを押下
ODBC接続のトラブルを解析する情報が得られます。

他には、Microsoft製品ならばインストールしても良いならば、以下の選択肢がある。

・Microsoft Message Analyzer

http://msdn.microsoft.com/en-us/library/jj649776.aspx
インストールが必要
ネットワークパケットをモニターできるが、未だ資料があまり無くて、
次に挙げる１世代古い「Microsoft ネットワーク モニター」のほうが楽だし、翻訳された情報も多い。

・Microsoft ネットワーク モニター

http://support.microsoft.com/kb/933741/ja
インストールが必要
ネットワークパケットをモニターできる。

・Process Monitor

http://technet.microsoft.com/ja-jp/sysinternals/bb896645.aspx
インストールが必要
プロセスのファイル／レジストリ／ネットワーク アクセスをモニタできる。
時刻:

2014年6月19日木曜日

cronのジョブが二重に起動されてしまう

ある時点から、cronのジョブが複数起動されてしまうようになったとすると、恐らく最初に疑うべきは、crond が複数起動しているのではないか？

例えばこんな状態に

ps -ef | grep crond
[root@localhost work]# ps -ef | grep crond
root 5129 1 0 22:59 ? 00:00:00 crond
root 5132 4899 0 22:59 pts/0 00:00:00 grep crond
root 20368 1 0 00:28 ? 00:00:00 crond
時刻:
登録: コメント (Atom)