BLOG　Tokyo

IDが数値だったり、文字列だったりして、主キーの型が合わないためにうまく、データを引っ張ってこれないことがある。こういう場合は、フォーマットを整える必要がある。

ID：234
↓
ID：00000234

と数値型3桁を文字型8桁に変更するなどだ。これはFormat関数を使えばとても簡単に実現できる。

クエリのフィールド欄に下記のようにしたらいい。

共通ID: Format(T_test.ID,"00000000")

T_test.IDにテーブル名の項目、
"00000000"に8桁ならば、0を8個、18桁にしたいならば、0を18個並べればよい。

【参考文献】
access club：数字の先頭に 0 を付ける方法-Format関数：AccessTips115

重複するレコードを削除するのとちょっと違うことをしたい。例えば、ある名簿があって、同姓同名と、同姓同名以外のグループ分けたい場合などだ。

１．「対象テーブル」の「重複クエリ」を実行
２．「対象テーブル」と「重複クエリ結果」に「不一致クエリ」を実行

後でデータ加工したい場合は、デザインビューから「テーブル作成クエリ」で新規テーブルとして保存しておくのもいい。

10万件を超えるｃｓｖデータを眼で検査するのはさすがにきつい。エクセルでもやっぱりきつい。ACCESSは外部データのインポートが簡単に行えるが、10万件もあるとエクセルのセル内に改行コードがあったりすると1行のレコードが分裂し、とてもとても眼で検査できるような形でインポートされない。

そんな時は、外部データの1行に改行コードが入っているセルを"（ダブルクオテーション）で囲めば解決できる。10万件もある場合は、VBAを使って機械的にやってしまおう。エクスポートする際に、各カラム（フィールド）を"（ダブルクオテーション）で括るように出力すればよい。

組織の設置年から○○周年記念などを調べたい場合がある。Year()を使えば速攻だ。

周年: Year(Now())-[設置年]+1

設置年はフィールド名
周年は新規フィールド名

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

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

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

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

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

や

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

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

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

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

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

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

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

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

クエリで集計した結果の列がいくつかあって、さらにそれを集計したい場合がある。しかし、なかには、ゼロや空白などの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

【2012年1月18日更新】
BASP21には英語版のWindowsで動作するものが用意されています。日本語版のものをダウンロードして使うとCannot openとでるのでこちらをつかったらよいです。

http://www.hi-ho.ne.jp/babaq/eng/basp21.html

また、メール送信で最近は認証を用いていることも多くはず。ぼくも実際困りました。なんとBASP21ではその認証にも対応しているのです。

●SMTP AUTH を使う 2000.07.20 New!
mailfrom = "" & vbTab & "user1:pass1" ' AUTH LOGIN
mailfrom = "" & vbTab & "user1:pass1" & vbTab & "CRAM-MD5" ' AUTH CRAM-MD5

実際のテクとしてテンプレートを元に複数のメールを一括配信するで用いるmail.datは下記のとおりにするとうまくいく。"（ダブルクオテーションは不要）

WINGSニュース & vbTab & username1:password

注意） & vbTab & はタブでよいです。usernameとpasswordはoutgoingのメールサーバーのものですので管理者に聞かないとわからないものです。

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

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

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

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

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

たとえば、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を設定・実行すると兼業してない、職員一覧が作成できる。

MySQLとAccess連携でメリットは下記のように考えている。（もっとあると思うけど。）
・高機能データベースをAccessのフォームインターフェイスを作って操作できるというメリットは大きい。
・開発もphpよりは簡単だし、ユーザは使い慣れている。
・Accessを複数人（経験では8人）でネットワーク上で使用すると、よくAccessデータベース自体が破損してしまう。（使用頻度-高、レコード件数-約1万5千件/年、ファイルサイズ11MB）

AccessからMySQLを操作する場合下記のことに注意しなければならない。

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にチェックすれば、解決する。

ACCESSのデータをMySQLでいきなり使おうと思っても文字化けしてしまうことが多い。やはり設定が必要なのだ。Windowsの日本語の文字コードといばShift-JISなんだが、正式にはWindows-31Jと呼ばれていて、若干違うところがあるらしい。このWindows-31Jと互換性があるように作られたのがMySQLのcp932と呼ばれる文字コードなのだ。

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

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

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

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

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

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

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

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

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

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

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

MS ACCESSからのデータをMｙSQLに移行する作業を行っている。ODBC経由でエクスポートするのが楽なんだが、主キーの設定が無効になってしまうので、MySQLから主キーの設定をする必要がある。文字コードはsjisなので、あとでPHPから接続しようと思うと文字化けの原因にもなったりするので、その設定にも留意が必要である。

何も考えずに、ACCESSのデータをMｙSQLにインポートしてODBC経由でACCESSから接続して、データの更新をしようと思うとなぜかうまくいかない。問題は、主キーを設定していないためだ。主キーを設定してから再度リンクテーブルを設定しなおすとうまく行く。

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

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

MS AccessでMySQLに接続できるようになれば、かなり便利だ。MS Accessを複数人で利用する場合、５，６人が限界らしく、ファイル自体が壊れてしまうケースも報告されている。そこで本格派データベースが必要なわけだ。それがMySQLなんだけど、まぁマイクロソフト製品ほど導入が簡単ではないため今後も勉強が必要そうだ。

さて、MySQLにMS Accessで接続できるのは便利だが、やはりそれなりの制限もある。さらなる研究が必要だが、現在把握しているもので代表的なものが文字化けだ。

MySQLは世界的に広く利用されているデータベースで動作が軽快なことで知られている。Windows上でも動作確認がされていてUNIX/Linuxに不慣れな人にも扱いやすい。

マイクロソフトアクセスで接続できるようになると、アパッチとPHPをインストールして、PHPでプログラミングでWEB連携しなくて済むのでかなり期待している。