カテゴリー名： ［ADOによるデータベースの新規作成］
データベース操作のうち、新規作成とデータの書き込みを取り上げます。
Accessファイルを作成しますが、Accessを用いず VBScriptで行います。
Accessがインストールされていない環境でも実行できます。
今回は Accessファイルの test.accdb, test.mdb を作成します。
Accessがインストールされていなくても大丈夫です。
ただし、Office2007以降がインストールされていることを前提にします。
Office2003までの場合は、test.accdbを扱うことができません。ですが、test.mdbの方は大丈夫だと思います。
作成するデータベースの中身は、「Excelファイルの新規作成」のときと同じです。
氏名と身長のデータが３人分記録されているデータベースを作成します。表形式で示すと下のとおり。
| 氏名 | 身長 |
|---|---|
| 鈴木 | 172.3 |
| 高橋 | 168.5 |
| 田中 | 183.6 |
データベースを作成する手順を箇条書きにすると下のようになります。
ほとんどは前ページの「Excelファイルの新規作成」と共通ですが、異なる点があるので、以下ではそれを中心に解説します。
test.accdb というファイルを作成します。
テーブル名は TestTable です。
「氏名」と「身長」の二つのフィールドからなるレコードを３人分登録します。
まずは VBScript を掲げます。解説は、その後に記します。
これを実行すると test.accdb が作成されます。既に test.accdb が存在する場合は、それを削除した上で新たに作成します。
テーブル作成、レコードセットによるデータの書き込みの部分は、Excelファイルの場合と同じです。必要に応じて「Excelファイルの新規作成」を参照してください。
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
データベース test.accdb のフルパスが C:\work\test.accdb の場合でいうと、データベースに接続するための接続文字列が下のようになります。
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\work\test.accdb;
Excelファイルの場合より短いですが、Extended Properties の記述がないからです。
データベースにアクセスするための UserID と PassWord が設定されている場合、あるいは設定したい場合は、それを接続文字列に盛り込むことができますが、ここでは触れません。
データベース本体、つまり 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ファイルを扱ったときは次の２行でCNを生成しました。
Set CN = CreateObject("ADODB.Connection")
CN.Open ConnStr
しかし、今回は上のようにしてCNを生成する必要はありません。
Accessがインストールされているパソコンであれば test.accdb がちゃんと作成されているかどうか確認できます。
しかし、Accessがない環境ではその中身を見ることができません。
そこで、とりあえずの対処方法として、test.accdbをExcelで取り込んでみます。Excelを起動するのでExcelがインストールされている必要があります。
ExcelのQueryTableを用います。これは外部データを取り込む機構です。
単に取り込むだけでなく、Query(問合せ)というだけにSQL命令文を指定して条件に当てはまるものを抽出できたりしますが、今回は単純な取り込みです。
下のVBScriptを実行すると、test.accdbを取り込んで結果を test_accdb.xls として書き出します。
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 を見れば、氏名と身長が３人分書かれていると思います。
QueryTableについての説明は、別の機会に譲ります。
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 が作成されます。
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
データベース 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で大丈夫です。それについては次の項で触れます。
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 を掲げます。
これを実行すると、一人分のデータを読み取ってそれを表示します。
エンターキーをたたくと、次の人のデータが表示されます。
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 については別の機会に説明したいと思います。
これまで、データの書き込みをレコードセットにより行ってきましたが、レコードセットオブジェクトを設けず、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ファイルの取り扱い方法です。
〜 以上 〜