Accessファイルの新規作成

カテゴリー名: [ADOによるデータベースの新規作成

2016/08/16

 データベース操作のうち、新規作成とデータの書き込みを取り上げます。

 Accessファイルを作成しますが、Accessを用いず VBScriptで行います。

 Accessがインストールされていない環境でも実行できます。

関連の Program Page

    


《このページの目次》


    

1. 概要

 今回は Accessファイルの test.accdb, test.mdb を作成します。

 Accessがインストールされていなくても大丈夫です。

 ただし、Office2007以降がインストールされていることを前提にします。

 Office2003までの場合は、test.accdbを扱うことができません。ですが、test.mdbの方は大丈夫だと思います。

    

 作成するデータベースの中身は、「Excelファイルの新規作成」のときと同じです。

 氏名と身長のデータが3人分記録されているデータベースを作成します。表形式で示すと下のとおり。

氏名 身長
鈴木 172.3
高橋 168.5
田中 183.6

    

 データベースを作成する手順を箇条書きにすると下のようになります。

 ほとんどは前ページの「Excelファイルの新規作成」と共通ですが、異なる点があるので、以下ではそれを中心に解説します。

目次に戻る


    

2. Accessのaccdbファイルの作成

 test.accdb というファイルを作成します。

 テーブル名は TestTable です。

 「氏名」と「身長」の二つのフィールドからなるレコードを3人分登録します。

 まずは VBScript を掲げます。解説は、その後に記します。

 これを実行すると test.accdb が作成されます。既に test.accdb が存在する場合は、それを削除した上で新たに作成します。

 テーブル作成、レコードセットによるデータの書き込みの部分は、Excelファイルの場合と同じです。必要に応じて「Excelファイルの新規作成」を参照してください。

    

△ create_accdb.vbs

 1Option Explicit
 2Dim DbName, TableName, TypeStr
 3Dim FSO, DbPath
 4Dim CN, ConnStr, CAT, RS, sql
 5
 6DbName = "test.accdb"
 7TableName = "TestTable"
 8TypeStr = "氏名 varchar(20),身長 float"
 9
10Set FSO = CreateObject("Scripting.FileSystemObject")
11DbPath = FSO.GetAbsolutePathName(DbName)
12If (FSO.FileExists(DbPath) = True) Then FSO.DeleteFile(DbPath)
13Set FSO = Nothing
14
15ConnStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DbPath & ";"
16Set CAT = CreateObject("ADOX.Catalog")
17Set CN = CAT.Create(ConnStr)  ' データベース作成
18Set CAT = Nothing
19
20sql = "create table " & TableName & " (" & TypeStr & ");"
21CN.Execute(sql)  ' テーブル作成
22
23Set RS = CreateObject("ADODB.Recordset")
24sql = "select * from " & TableName & ";"
25RS.Open sql,CN,0,2,1
26RS.AddNew
27    RS.Fields("氏名").Value = "鈴木accdb"
28    RS.Fields("身長").Value = 172.3
29RS.Update
30RS.AddNew
31    RS.Fields(0).Value = "高橋accdb"
32    RS.Fields(1).Value = 168.5
33RS.Update
34RS.AddNew Array("氏名", "身長"), Array("田中accdb", 183.6)
35RS.Update
36RS.Close
37CN.Close
38Set RS = Nothing
39Set CN = Nothing

目次に戻る


    

(1) 接続文字列(ConnectionString)

 データベース test.accdb のフルパスが C:\work\test.accdb の場合でいうと、データベースに接続するための接続文字列が下のようになります。

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\work\test.accdb;

 Excelファイルの場合より短いですが、Extended Properties の記述がないからです。

 データベースにアクセスするための UserID と PassWord が設定されている場合、あるいは設定したい場合は、それを接続文字列に盛り込むことができますが、ここでは触れません。

目次に戻る


    

(2) データベース本体の作成

 データベース本体、つまり test.accdb を作成するには ADOX を使います。

 具体的には、まず Set CAT = CreateObject("ADOX.Catalog") として、ADOXのカタログオブジェクトを生成します。

 次に、接続文字列が代入されている変数 ConnStr を用いて CAT.Create(ConnStr) とします。

 これで test.accdb が新規に作成されます。

    

 既に test.accdb というファイルが存在するときは、この CAT.Create(ConnStr) を実行するとエラーが発生します。「既にそのファイルは存在するので作れない」というエラーです。

 今回のVBScriptでは、その最初の方で予め test.accdb を削除した上で処理手順を進めるようにしているのでエラーになりませんが、実践用のVBScriptを書くときは注意が必要な点です。

    

 それから、CAT.Create(ConnStr) は、その戻り値としてADOの接続用オブジェクトを返します。なので、その戻り値を変数CNに代入します。

 Excelファイルを扱ったときは次の2行でCNを生成しました。

Set CN = CreateObject("ADODB.Connection")
CN.Open ConnStr

 しかし、今回は上のようにしてCNを生成する必要はありません。

目次に戻る


    

(3) test.accdbをExcelで取り込む

 Accessがインストールされているパソコンであれば test.accdb がちゃんと作成されているかどうか確認できます。

 しかし、Accessがない環境ではその中身を見ることができません。

 そこで、とりあえずの対処方法として、test.accdbをExcelで取り込んでみます。Excelを起動するのでExcelがインストールされている必要があります。

 ExcelのQueryTableを用います。これは外部データを取り込む機構です。

 単に取り込むだけでなく、Query(問合せ)というだけにSQL命令文を指定して条件に当てはまるものを抽出できたりしますが、今回は単純な取り込みです。

 下のVBScriptを実行すると、test.accdbを取り込んで結果を test_accdb.xls として書き出します。

    

△ qt_accdb.vbs

 1Option Explicit
 2Dim FSO, BookName, BookPath
 3Dim EXLapp, WBobj, WSobj, QTobj
 4Dim DbName, DbPath, sql, ConnStr
 5Const xlWorkbookNormal = -4143
 6
 7DbName = "test.accdb"
 8sql = "select * from TestTable;"
 9
10Set FSO = CreateObject("Scripting.FileSystemObject")
11DbPath = FSO.GetAbsolutePathName(DbName)
12If (FSO.FileExists(DbPath) = False) Then
13    WScript.Echo DbName & "がみつかりません."
14    WScript.Quit
15End If
16ConnStr = "ODBC;DSN=MS Access Database;DBQ=" & DbPath
17
18BookName = Replace(DbName, ".", "_") & ".xls"
19BookPath = FSO.GetAbsolutePathName(BookName)
20If (FSO.FileExists(BookPath) = True) Then FSO.DeleteFile(BookPath)
21
22Set EXLapp = CreateObject("Excel.Application")  ' Excelの起動
23EXLapp.Visible = True  ' Excelを見える状態に
24Set WBobj = EXLapp.Workbooks.Add()  ' Workbookの新規作成
25Set WSobj = WBobj.Worksheets(1)
26Set QTobj = WSobj.QueryTables.Add(ConnStr, WSobj.Range("A1"), sql)
27With QTobj
28    .BackgroundQuery = False  ' バックグラウンド処理をしない
29    .Refresh
30    .Delete  ' クエリテーブルを削除
31End With
32WBobj.SaveAs BookPath, xlWorkbookNormal
33EXLapp.quit

    

 上を実行して作られる test_accdb.xls を見れば、氏名と身長が3人分書かれていると思います。

 QueryTableについての説明は、別の機会に譲ります。

目次に戻る


    

3. Accessのmdbファイルの作成

 test.mdbの作成を考えます。やり方は、ほとんど test.accdb のときと同じです。

 ただし、ACEエンジンでなくJETエンジンの方を使います。なので、VBScriptが64bit版のコマンドで実行されてしまうとエラーになります。32bit版の WScript.exe または CScript.exe の下でVBScriptを実行する必要があります。

 64bitのWindowsにも32bit版のコマンドが用意されているので対応可能ではありますが、explorer(マイコンピュータ)でVBScriptをクリックすると、64bit版のWScript.exeが呼び出されてしまいます。

 DOSプロンプトで test.vbs [enter] のように単純に実行したときも、64bit版のCScript.exeの下でtest.vbsが実行されてしまいます。

    

 ここで、とても助かるのが下のサイトに書かれているノウハウです。

WSH VBScript - ひとり64bit OS対応 - ノートの余白みたいなもの

 このサイトには RunOn32bit というサブプロシージャが掲載されています。

 自作の test.vbs の適当なところに RunOn32bit を記述しておくと、いったんは64bitコマンドの下で実行されるものの、32bitコマンドの下でそのtest.vbsが実行し直されます。

 32bitのWindows環境であれば、最初から32bitのコマンドの下でtest.vbsが実行されますが、その場合は、RunOn32bit は何もしません。

 優れもののサブプロシージャです。これを利用します。

 以下、create_mdb.vbs です。これを実行すると test.mdb が作成されます。

    

△ create_mdb.vbs

 1Option Explicit
 2Dim DbName, TableName, TypeStr
 3Dim FSO, DbPath
 4Dim CN, ConnStr, CAT, RS, sql
 5
 6RunOn32bit  ' 32bitコマンドで当スクリプトを実行
 7DbName = "test.mdb"
 8TableName = "TestTable"
 9TypeStr = "氏名 varchar(20),身長 float"
10
11Set FSO = CreateObject("Scripting.FileSystemObject")
12DbPath = FSO.GetAbsolutePathName("test.mdb")
13If (FSO.FileExists(DbPath) = True) Then FSO.DeleteFile(DbPath)
14Set FSO = Nothing
15
16ConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
17    "Data Source=" & DbPath & ";"
18Set CAT = CreateObject("ADOX.Catalog")
19Set CN = CAT.Create(ConnStr)  ' データベース作成
20Set CAT = Nothing
21
22sql = "create table " & TableName & " (" & TypeStr & ");"
23CN.Execute(sql)  ' テーブル作成
24
25Set RS = CreateObject("ADODB.Recordset")
26sql = "select * from " & TableName & ";"
27RS.Open sql,CN,0,2,1
28RS.AddNew
29    RS.Fields("氏名").Value = "鈴木mdb"
30    RS.Fields("身長").Value = 172.3
31RS.Update
32RS.AddNew
33    RS.Fields(0).Value = "高橋mdb"
34    RS.Fields(1).Value = 168.5
35RS.Update
36RS.AddNew Array("氏名", "身長"), Array("田中mdb", 183.6)
37RS.Update
38RS.Close
39CN.Close
40Set RS = Nothing
41Set CN = Nothing
42
43Public Sub RunOn32bit
44    Dim p_objWshShell, p_admArg, p_admArrayArguments
45    Dim p_admWscriptCscript, p_admCommand
46    Set p_objWshShell = CreateObject("Wscript.Shell")
47    If p_objWshShell.Environment("Process").Item( _
48      "PROCESSOR_ARCHITECTURE") <> "x86" Then
49        If Not WScript.Arguments.Count = 0 Then
50            For Each p_admArg In Wscript.Arguments
51              p_admArrayArguments = p_admArrayArguments & _
52                  " """ & p_admArg & """"
53            Next
54        End If
55        If InStr(LCase(WScript.FullName), "wscript") > 0 Then
56            p_admWscriptCscript = "WScript.exe"
57        Else
58            p_admWscriptCscript = "CScript.exe"
59        End If
60        p_admCommand = """" & p_objWshShell.Environment("Process").Item( _
61            "windir") & "\SysWOW64\" & p_admWscriptCscript & """ """ & _
62            WScript.ScriptFullName & """" & p_admArrayArguments
63        p_objWshShell.Run p_admCommand
64        WScript.Quit
65    End If
66End Sub

目次に戻る


    

(1) 接続文字列(ConnectionString)

 データベース test.mdb のフルパスが C:\work\test.mdb の場合でいうと、データベースに接続するための接続文字列が下のようになります。

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\work\test.mdb;

 JETエンジンを指定しています。

 ここでちょっと疑問。

    

 Excelファイルのxlsを扱うときは、JETでもACEでもどちらでもよかった訳ですから、mdbをACEで扱うことができるのではないか?

 ACEは、JETの上位互換的な位置づけのようですから、ACEでmdbを扱うことができて当然、と感じます。

 試しに接続文字列をACEにしてtest.mdbを作成してみると、エラーが発生することもなくちゃんと作られます。

 しかし、できるのは test.mdb であっても中身はaccdbファイルのようで、test.mdbを利用しようとするとエラーになったり警告が出たりします。

 ACEエンジンで正常なmdbファイルを作成する方法がないかをいろいろ探りましたが、分かりませんでした。

 結局、RunOn32bitを活用するところに落ち着きました。

 ただし、「作成」ではなく「読み取り」であれば、JETでなくACEで大丈夫です。それについては次の項で触れます。

目次に戻る


    

(2) test.mdbの中身を確認する

 Accessがインストールされているパソコンであれば test.mdb がちゃんと作成されているかどうか確認できます。

 しかし、Accessがない環境ではその中身を見ることができません。

 accdbファイルについてはExcelでその中身を確認するための qt_accdb.vbs を掲げました。

 この qt_accdb.vbs の中の test.accdb を test.mdb に変更すれば、
test.mdb の中身をtest_mdb.xlsとして書き出すことができます。

 ですが、ここではADOでの「読み取り」を試してみます。

 データベースの「読み取り」については別のページで取り上げますが、ちょっとだけ先走って試します。

    

 ここで示したいのは、test.mdbの「読み取り」の場合は、JETエンジンでなくACEエンジンでも大丈夫だということです。

 以下に echo_mdb.vbs を掲げます。

 これを実行すると、一人分のデータを読み取ってそれを表示します。

 エンターキーをたたくと、次の人のデータが表示されます。

    

△ echo_mdb.vbs

 1Option Explicit
 2Dim DbName, TableName
 3Dim FSO, DbPath
 4Dim CN, ConnStr, CAT, RS, sql, MyStr
 5
 6DbName = "test.mdb"
 7TableName = "TestTable"
 8
 9Set FSO = CreateObject("Scripting.FileSystemObject")
10DbPath = FSO.GetAbsolutePathName(DbName)
11If (FSO.FileExists(DbPath) = False) Then
12    WScript.Echo DbName & "がみつかりません."
13    WScript.Quit
14End If
15Set FSO = Nothing
16
17ConnStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DbPath & ";"
18Set CN = CreateObject("ADODB.Connection")
19CN.Open ConnStr
20Set RS = CreateObject("ADODB.Recordset")
21sql = "select * from " & TableName & ";"
22RS.Open sql,CN,0,1,1
23Do Until RS.EOF
24    MyStr = RS.Fields("氏名").Value & ", " & RS.Fields("身長").Value
25    WScript.Echo MyStr
26    RS.MoveNext
27Loop
28RS.Close
29CN.Close
30Set RS = Nothing
31Set CN = Nothing

    

 RS.EOF とか RS.MoveNext については別の機会に説明したいと思います。

目次に戻る


    

4. SQL命令のinsertによる書き込み

 これまで、データの書き込みをレコードセットにより行ってきましたが、レコードセットオブジェクトを設けず、SQL命令文により行うことができます。

 SQL命令文は次のようなものです。

insert into TestTable (氏名,身長)  values('田中', 183.6);

 これを CN.Execute で実行すれば一人分のデータが書き込まれます。

 プログラムの関連部分だけ掲げると下のとおり。

    

-------- ここから
ConnStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DbPath & ";"
Set CAT = CreateObject("ADOX.Catalog")
Set CN = CAT.Create(ConnStr)  ' データベース作成
Set CAT = Nothing

sql = "create table " & TableName & " (" & TypeStr & ");"
CN.Execute(sql)  ' テーブル作成

sql = "insert into TestTable (氏名,身長)  values('鈴木', 172.3);"
CN.Execute(sql)
sql = "insert into TestTable (氏名,身長)  values('高橋', 168.5);"
CN.Execute(sql)
sql = "insert into TestTable (氏名,身長)  values('田中', 183.6);"
CN.Execute(sql)
-------- ここまで

 zip圧縮ファイルに含まれている create_accdb02.vbs, create_mdb02.vbs を参照して下さい。

  このようなSQL命令文のinsertによるデータの書き込みは、Accessファイルの場合だけでなく、Excelファイル、CSVファイルの場合も使うことができます。

    

 以上でこのページは終了です。

 次回は ADOによるCSVファイルの取り扱い方法です。

〜 以上 〜