BLOG　Tokyo

このファイルを開けません。

このファイルは、イントラネット外または信頼されていないサイトに保存されています。
ファイルを開くには、ローカル コンピュータまたはアクセスできるネットワークの保存場所にいったんコピーしてください。

と突然、アクセスのファイルが開けなくなることがある。よくよく記憶を辿ってもらってみるとインターネットエクスプローラー７（IE７）にアップグレードした場合によく見られるのだ。2008年冬にはIE8がリリースされるらしく、この種のトラブルはしばらく続きそうだ。

パソコンを再インストールし、いつものアクセスファイルを開くと

読み取り専用です。
このデータベースのデータやオブジェクトの定義を変更することはできません。

や

データベースファイルが無いか他のユーザーが排他的モードで開いているため、そのデータベースを開くことができません。

と表示された。これは、ネットワークを介して、複数人でデータベースを更新する場合に起きるエラーで、例えば、ネットワーク上のユーザID、パスワードが正常に使える人と同じでなければ、このようなエラーが出続ける。

「コントロールパネル」から、「ユーザーアカウント」で、「ネットワークパスワードを管理」で再設定できる。再設定後は、再起動が必要である。

パソコンを再インストールしたら、これまで表示されていた、クエリのデザインで表示→ピボットグラフビューをすると、

OLEサーバーと通信できません。

OLEサーバーが登録されていません。

OLEサーバーを再セットアップしてください。

と表示された。いろいろ調べるがよくわからなかったので、OFFICE UPDATEをしたら治りました。

ACCESSはそもそもスタンドアロンで利用されることを目的とされており、ネットワーク上で共有して使うには多くの問題点を抱えているようだ。そこでSQL Serverを導入してはどうかというのがマイクロソフトの戦略なわけなんだが、使ってみるとバージョンごとのサポートがないため、コピー＆ペーストで簡単にできるわけではないらしい。

ACCESSのアップサイジングという機能を使うと簡単にSQL Serverにデータを移行でき、その時に作られる.adp（ACCESSプロジェクト）を使ってC/S版のデータベースを開発するというような仕組みらしいのだが、ストアドプロシージャとかがいきなり出て来て、これを使わないと開発も進められないので、なかなか敷居が高い。

もっと詳しくMS ACCESS

職場でACCESSを使う機会が多いが、ネットワークを介して複数人で利用する場合、頻繁にデータが壊れてしまう。経験的に5人の同時アクセスでレコードの更新をかけるだけでかなり危険だ。そのうち誰かがクエリのデザインを変更したりすると物凄い確率で破損する。MySQLへのテーブルの移動も考えたが、文字コードやデータベースエンジンの違いにより結構複雑であるし、持続可能ではない。やはり、Microsoft同士の相性が良さそうなので、SQL Server 2005を試してみた。Express版は無料というのがポイントだ。

クエリで集計した結果の列がいくつかあって、さらにそれを集計したい場合がある。しかし、なかには、ゼロや空白などのnull値を含む場合、うまく足し算が出来ない。というかエラーになる。
そんなときは、この関数だ。

Nz関数

使用例
-------
源泉徴収税額: nz([作業従事税金の合計])+nz([講演・講師税金の合計])+nz([旅費税金の合計])+nz([会議出席税金の合計])

フォームに入力中のテーブルにない情報を参照したい場合がある。たとえば、重複データの確認とか、顧客の詳細情報とか。今回は、重複入力の確認をDlookup関数を用いる方法を紹介するが、このDlookup関数の動作が遅く、VBAの作業中、突然フリーズしたりする。なかなか問題のある関数だが、非常に有用だ。

DLookup 関数を使うと、基になるレコード ソースにないフィールドの値をフォームやレポートに表示することができます。たとえば、演算コントロールで DLookup 関数を使って、[商品] テーブルにある [商品名] フィールドを、[受注明細] テーブルを基にするフォームに表示することができます。

DLookup(抽出フィールド, テーブル/クエリ名, 条件)

もっと詳しく

重複入力した場合、エラーメッセージを表示させたい場合がある。全くの入力を拒否したい場合は、そもそもそのフィールドに主キーを設定しておくのが良いと思われるが、複数のフィールドでとなるとちょっと複雑だ。さらに重複しているなら、入力させないということならば、BeforeUpdateがいい。今回は、それより難易度が低く、メッセージのみを表示するものだ。

参考

せっかくテーブルにメールアドレスと名前、所属などの情報があるのならば、それを使って事務連絡等をした方が断然効率がいい。よく、メルマガ等で○○様という宛名でメールが送信される仕組みと似ている。OUTLOOKがインストールされていないと不発に終わる。問題解決に2時間くらい掛かった。（涙）

詳しくはこちら

突然フォームのテキストボックスに入力ができなくなった。確認作業の結果わかったことが、データをバックアップからリストアしたとのことだったが、バックアップツールは主キーの設定がリセットされていて、今回のフォームはクエリを参照して、データを編集する仕組みになっていたため、クエリが追加更新不可になっているからだった。

オートナンバーをCGIでレコードIDとして使いたい場合がある。数値型なので、8桁にして使いたい時少し迷うんだが、デザインビューの書式で

#\10000000

のように記述すれば、10000001,10000002....という風に8桁表示することができる。

"(ダブルクオテーション）を出力したいときがあるのだが、どうも、perlやPHPとは違う。この場合は、Chr$(34)を使えばよい。

Chr$(34)　(ダブルクオテーション）

フォルダ内のテキストファイル（csv）をテーブルに一括取り込みしたい場合がある。どうすれば良いか。
DoCmd.TransferTextというインポートコマンドを使うと便利だ。

Dir関数内では「＊」ワイルドカードを使えるので、myFilenameが空になるまでループさせる。
（Dir関数はファイル名を取得する関数で、()パスを指定しないとカレントフォルダを検索する）

インポート先のテーブルはフィールド名なし（最後の引数）を指定すると、"f1,f2......"とう名前をデフォルトで探す。カンマが多い場合は、f100くらいまで作っておくと安全だ。

Private Sub cmdImport()

Dim myFilename As Variant
Dim myPath As Variant

myPath = "C:\mfind\03\"

myFilename = Dir("C:\mfind\03\*.txt")

Do Until myFilename = ""
DoCmd.TransferText acImportDelim, , "T_table", myPath & myFilename, False
myFilename = Dir()
Loop

End Sub

Accessのデータを個別にそして、その本文の内容をテンプレートを使った差込形式で一斉送信することは、サーバサイドの技術かと思っていたが、いまやそれもクライアント上で可能なのだ。AccessのVBAでもその機能があるが、BASP21というのを使えばかなり簡単にそれを実現できる。お試しあれ。

参考URL

改行コードの話はすでに述べた。
てっきりFirefoxの仕業かと思いきや、アップロード時にLFを指定していたためだった。FTPクライアントでは改行コードを指定できるものがあり、職場ではWinSCP3の使用を義務付けられていたため、起こった障害だった。

このサイトはかなり詳しく、WinSCP3の利用方法が説明されている。
Login時の最初の画面で設定できる。

改行コードの指定の仕方

IDを数値型なんかで保存してしまっている場合、本当は8桁なんだけど7桁になったりして、検索で探せなくなったりして不便だ。やっぱ先頭を0（ゼロ）を使うのであれば、テキスト型でないと駄目だろう。

ACCESSは文字数を数えるLEN関数を利用すると便利だ。

改行コードにはいろいろある。WebサーバがUNIX/Apacheの場合など、ブラウザを通すと勝手に書き換えられたりして、行頭が乱れたりしてしまう。やれやれだ。Firefoxも改行コードが若干怪しく、LFになってしまう場合もあるので、注意が必要だ。

VBAで使う場合と改行コード
Chr(13)---CR---Mac---キャリッジリターン
Chr(10)---LF---Unix---ラインフィールド
Chr(10)+Chr(13)--Win

重複するレコードを見つけて削除したい場合がよくある。重複クエリウィザードを使っても一発ではできないのだが、ちょっとだけ工夫をすると意外と簡単だ。

１．元テーブルを構造だけコピーして貼り付けする
２．コピーしたテーブルに重複を調べるフィールドに主キーを設定する
３．元テーブルをベースに新規クエリで追加クエリを作成し、コピーしたテーブルに追加クエリを実行すると一意のフィールドのみが追加される（主キーを設定しているから）。

マイクロソフトのサイトへ

たとえば、2004/5/1って言うのを2004-05ていう型にしたい時がある。そんな時は、Format関数とMonth関数を組み合わせて使うと結構簡単だ。

test: Year([日付]) & "-" & Format(Month([日付]),"mm")

ACCESSでADOを使えるようになってくると処理能力が画期的に向上する。あまり文献も多くないのだが、是非覚えたい技術である。

今回は、タイトル別レコードを個人番号別にCSVファイルに出力するコード書いてみた。さらにこれを個人番号の個人へメールで添付一括送信とうところまで辿り着ければ、一連の事務手続きは大幅に改善すると思われる。今回は対象レコードは1000件くらいであったが、この数になってくると手作業はほぼ徹夜作業になるだろうし、アホらしい。次は8000件だよっと普通に依頼されたらイラっときてしまうかもしれないが、いったんこのサイクルを作ってしまえば気持ちにゆとりができるはずだ。

レコードの列で異なるデータ型ある場合は、型エラーがでるので、注意しよう。

ADO関連参考サイト
一括メール送信設定参考サイト

職員番号別にフォルダを作成し、その個人情報をそこに保存したい場合は、少なくない。まさか、手入力でフォルダ作成、名前の変更、個人情報をエクセルにエクスポートして、その行をコピーして、テキストファイルにタブ区切りで保存するの？

全職員分ならまだしも、該当者のみとか？該当者は1300人中の900人ですなんて？いやいや、力任せに2連チャンで徹夜してみてもいいんだけど、その翌日に該当者の範囲が変わって1200人になりましたなんていわれた日にはちょっとムカッとくるかも知れません。課長には夕飯を食べる暇があるなら帰れと言われるし。。。（俺に払える残業代はないってことね　トホホ）そこで、やっぱり少しだけ頭を使ってバッチ処理（一括処理）を使わざるを得ないんですねぇ。今後はもっと頭を使わないと飲みにも行けなくなくかもなぁ。がんばろっと。

出力するデータ型が列ごとに異なると型エラーが出るので注意が必要だ。

例えば、職員テーブルと兼業テーブルがある。兼業テーブルには兼業している人のデータしかない場合で、兼業していない人の職員IDを調べ、1件1件、該当データはありませんでしたなんて処理にしていきたい場合が稀にある。（あんまりないかな）

これも重複しているデータを排除するのと似ていて、
例えば、

１．兼業テーブルをコピーしておいて、その職員IDに主キーを設定する。
２．職員テーブルを元に追加クエリを作成して、
３．コピーした兼業テーブルに追加（実行）すると全職員分の兼業テーブルができる。
４．例えば、兼業テーブルに相手先フィールドがあれば、そこに選択クエリでis nullを設定・実行すると兼業してない、職員一覧が作成できる。

ACCESSをネットワークで使いたい場合、直接データベースを編集するのは処理速度とかもろもろを考えても好ましくない。リンクテーブルを利用してデータベースを分割するのがよい。これはあくまでもパフォーマンスの問題で、だからといってデータベースが壊れにくくなるわけではない。自分がつかっているACCESSは最大9名がネットワーク越しで利用しているが、2週間に１回以上の頻度で故障している。最悪です。。
それはさておき、データベースの分割はどうすればいいのか。簡単にいうと、テーブルだけ別のデータベースに移して（バックエンド）、各利用者には、クエリとフォーム、レポートが入っているデータベースを配布する（フロントエンド）。そうすることで、フォームを開くなどのデータのやり取りをネットワーク越しにしなくて済むので、純粋にデータのやりとりだけになるのだ。開発とか、管理とか考える上では、結構楽だと思う。

同じレコードを同時に編集できないようにするには、レコードロックをかける必要がある。

ロックしない・・同じレコードを他人が修正した場合、それが反映されてしまう。
すべてのレコード・・テーブルごとにレコードがロックされる。
編集済みレコード・・編集が開始されたレコードがロックされる。

ツール-オプション-詳細で設定できるが、フォームに個別に設定することもでき、それが優先される。

今までなんとなくしか理解していなかった「結合」をわかりやすく説明しているサイトを見つけた。助かります。

内部結合（INNER JOIN): 両方の表で一致する行だけを表示させたい時に使う。

外部結合(LEFT/RIGHT OUTER JOIN): 左（右）の表のすべての行を表示させたい時に使う。

参考

Dim db As DAO.Database
Dim rs As DAO.Recordset

などを使うと
「ユーザー定義型は定義されていません」
というエラーが連発して先に進めない。困ったもんだと思っていたら、簡単に解決できる。

参照設定はAccessのツールではなくツールのマクロからVisualBasicEditorを開きVisualBasicのツールに参照設定があるので、参照設定のDAOのObjectLibraryにチェックすれば、解決する。

２つのフィールドをクエリで結合できる。

（結合前）　
姓　　　名
井坂　　幸太郎

（結合後）
姓名
井坂幸太郎

（結合の仕方）
姓名: Trim([姓] & [名])

姓名の後の：（コロン）はフィールド名と式を区別するもの。
Trim()の中にフィールド名を[　]でくくって＆で結べばよい。

他のデータベースに移行したいと考えたとき、文字化けの問題が障壁になる。ACCESSからMySQLへの移行作業をしているのだが、大問題だ。さらにそれをPHPをつかって接続するとさらに頭が痛い。それを緩和する一つの手段として、

フィールド名（カラム名）は半角の英数字が基本である。

わかりにくいじゃないか。ごもっともだ。半角英数字のフィールド名はわかりにくい。そこでACCESSにはフィールドに標題を設定できるのだ。

フィールプロパティの標題で編集することができる。
MySQLにリンクテーブルを張っている場合も可能なのは素敵だ。

フィールド名を半角英数にすると次のようなメリットがある。
・ACCESSの日本語化に伴う潜在バグを回避できるかもしれない。
・VBAコードを入力するとき、「Me」を入力して候補リストからコントロール名を選択できる。

テーブルを表示させるときは、実フィールド名を表示させるのが一般的なようで、クエリに標題を設定することが多いようだ。

テーブルから重複するレコードを削除するには、次の 2 つの手順が必要です。最初に、重複のあるテーブルの構造のコピーを作成してから、重複のあるすべてのフィールドの主キーを作成します。次に、元のテーブルから新しいテーブルに追加クエリを作成して実行します。主キーであるフィールドには重複するレコードを含めることはできないので、この手順によって重複するレコードのないテーブルが生成されます。
テーブルをコピーして重複のあるフィールドの主キーを作成するには

1. データベース ウィンドウでテーブルを選択します。
2. ツールバーの [コピー] をクリックします。
3. ツールバーの [貼り付け] をクリックします。
4. [テーブルの貼り付け] ダイアログ ボックスでコピーするテーブルの名前を入力し、[テーブル構造のみ] をクリックして、[OK] をクリックします。
5. デザイン ビューで新しいテーブルを開き、コピーしたテーブルの重複を含むフィールドを選択します。
6. ツールバーの [主キー] をクリックして、選択したフィールドに基づいて主キーを作成します。
7. テーブルを保存して閉じます。