Access: 2008年5月アーカイブ

前回でもちょこっと紹介しましたが、AccessのSQL (いわゆるJet SQL)で　1→001 に変換する方法を紹介します。

下記のようなテーブルで、IDを"001"形式で表示させたいとします。
Table1

つぎにこのようなSQLを書きます。

SELECT Right([ID]+1000,3) AS newID FROM Table1;

実行結果

IDがゼロ付きで表示されています。

おまけ

"0001"とゼロを３つ表示させたい場合のSQL文

SELECT Right([ID]+10000,4) AS newID FROM Table1;

ついにやり方を発見!!
WSHでDBプログラムが書けるなんて夢のようです。

やり方

以下のファイルを作って、同じフォルダに置く。

1. MDBファイル


2. JSファイル

MDBファイルを用意

ファイル名：db1.mdb
テーブル：Table1

JScriptを作成

ファイル名：ado.js

var cn = new ActiveXObject("ADODB.Connection"); // ADO接続の準備

var ConnectString = "Driver={Microsoft Access Driver (*.mdb)};DBQ=db1.mdb";  // 接続用文字列


cn.Open(ConnectString);  // ADO接続をオープン

var rs = cn.Execute("SELECT * FROM Table1");  // SQLを実行し、結果のレコードセットを格納

for(; !rs.Eof; rs.MoveNext() ) {  // レコードセットを１レコードずつなめる

    WSH.Echo(rs.Fields("ID").Value + " " + rs.Fields("Name").Value);  //レコードの中身を表示

}

rs.Close;

cn.Close;

rs = null;

cn = null;

mdbファイルが別フォルダにある場合は、
　　DBQ=db1.mdb
の代わりに
　　DBQ=C:￥￥フォルダ名￥￥db1.mdb
とフルパスで書けばＯＫ。

ファイルサーバや別PCの共有フォルダにあるmdbファイルにもアクセスできます。(たぶん)

実行結果

ｷﾀｺﾚ

Accessで、数値の"1"を文字列の"001"に変換する方法を紹介します。
専門的にはゼロ埋め、ゼロパディングなどと言います。

下記のようなテーブルで、IDを"001"形式に変換してみましょう。

クエリでゼロパディング

クエリのデザインビューで、ゼロ付きIDという列を作ります。
フィールドの中身はこう書きます。

ゼロ付きID: Right( [ID] + 1000, 3 )

クエリの実行結果
ちゃんとゼロ付きで表示されました。

SQLでゼロパディング

ちなみに、SQLで書くとこのようになります。

SELECT ID, Right([ID]+1000,3) AS ゼロ付きID, Name
FROM Table1;

前回に続き、WSH(VBS)からAccessのクエリ（削除クエリや更新クエリ)を実行する方法を紹介します。





←実行したいアクションクエリ

VBSのコード

Option Explicit

Dim cn

Dim rs

Set cn= CreateObject("ADODB.Connection")

Set rs = CreateObject("ADODB.Recordset")

cn.Open "Driver={Microsoft Access Driver (*.mdb)}; DBQ=C:￥～￥ファイル名.mdb;"

rs.Open "Q_del", cn

rs.Open "Q_add", cn

cn.Close

Set rs = Nothing

Set cn = Nothing

MsgBox "完了しますた。"

Excel VBAからAccessのクエリ（削除クエリや更新クエリ)を実行したい場合があります。
その場合も、前回の記事と同様にADOを使うと簡単に実現できます。

←実行したいアクションクエリ
Excel VBA側でのコード

Sub macro1()
    
    Dim myProvider As String
    Dim mySrc As String
    
    mySrc = "Data Source=C:￥～￥アクセスのファイル名.mdb;"
    myProvider = "Provider=Microsoft.Jet.OLEDB.4.0;"
    
    Dim cn As New ADODB.Connection
    Dim rs As New ADODB.Recordset

    cn.Open myProvider & mySrc
    
    rs.Open "Q_del", cn
 
    rs.Open "Q_add", cn

    Set rs = Nothing
    cn.Close
    Set cn = Nothing
     
End Sub

VBAでゼロパディング（1→001に変換するやつ）のやり方を紹介します。
Excel, Access, Powerpoint等、Office製品の全てで共通に使えます。

１　を　００１に変換してみる

Sub test()

    MsgBox Right(1 + 10 ^ 3, 3)

End Sub

実行結果

一般に、任意の桁でゼロ埋めしたいとき

ゼロパディング関数を作ります。

Function padZero(n As Integer, keta As Integer)
    padZero = Right(n + 10 ^ keta, keta)
End Function

関数を呼び出すときは、

Sub test()
  MsgBox padZero(1, 4)  ' 1 →　0001 に変換
End Sub

などと書きます。

考え方は、前回紹介したExcelのゼロパディングと全く同じです。
[Excel] "1"を"001"に変換する方法

上記のように書くと、「それってAccessのエクスポート機能を使えばいいじゃん」と思われるかもしれません。
たしかにAccessを開いて、テーブルやクエリをエクセル形式でエクスポートするやり方もあります。

しかし、Excelファイルを開いた上で、そこからAccessのデータを取りに行くというのが必要なときもあります。
Excel VBAからAccessのデータを呼び出す方法、と言った方がわかりやすいかもしれません。
ADO(ActiveX Data Object)という仕組みを使うと、これを実現できます。

ADOを使う準備

ExcelのVBE(Visual Basic Editor)の画面で、ADOの参照設定をします。

Excel VBAコードを書く

下記のようにマクロを書きます。

Sub get_data()
    
    Dim myPath As String
    Dim myProvider As String
    Dim mySrc As String
    
    myPath = "C:\～\サンプル.mdb"
    myProvider = "Provider=Microsoft.Jet.OLEDB.4.0;"
    mySrc = "Data Source=" & myPath & ";"
    
    Dim cn As New ADODB.Connection
    Dim rs As New ADODB.Recordset

    cn.Open myProvider & mySrc
    rs.Open "テーブル名またはクエリ名", cn
    
    Range("a1").CopyFromRecordset rs
     
End Sub

マクロを実行すると、Excel上にAccessのデータが展開されます。

ADOとは何か？

ADOというものが一体何なのか、最初はよくわからないと思います。
そいういう場合は、無理に理解する必要はなく、上記のようにやるとVBAからAccessのデータをいじれるとだけ覚えておけばよいと思います。

感覚的には、FSO( File System Object )と似ているかもしれません。
FSOを使うと、VBAからＰＣ上のフォルダやファイルを操作することができます。
同じように、ADOを使うと、VBAからデータベース上のデータを操作することができます。