Program Page: Accessファイルの新規作成

カテゴリー名：　［ADOによるデータベースの新規作成］

2016/08/16

《このページの目次》

1.　JScript
2.　VBScript

1.　JScript

△　create_accdb.js

// Accessデータベース(accdb)の作成。ADO, ADOXを利用
  // データの書き込みにはレコードセットを利用
var DbName, TableName, TypeStr;
var fso, DbPath;
var CN, ConnStr, CAT, RS, sql;

DbName = "test.accdb";
TableName = "TestTable";
TypeStr = "氏名 varchar(20),身長 float";

fso = WScript.CreateObject("Scripting.FileSystemObject");
DbPath = fso.GetAbsolutePathName(DbName);
if (fso.FileExists(DbPath))  fso.DeleteFile(DbPath);
fso = null;

ConnStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + DbPath + ";";
CAT = WScript.CreateObject("ADOX.Catalog");
CN = CAT.Create(ConnStr);  // データベース作成
CAT = null;

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

RS = WScript.CreateObject("ADODB.Recordset");
sql = "select * from " + TableName + ";";
RS.Open(sql,CN,0,2,1);
RS.AddNew();
    RS.Fields("氏名").Value = "鈴木accdb";
    RS.Fields("身長").Value = 172.3;
RS.Update();
RS.AddNew();
    RS.Fields(0).Value = "高橋accdb";
    RS.Fields(1).Value = 168.5;
RS.Update();
RS.AddNew(["氏名", "身長"], ["田中accdb", 183.6]);
RS.Update();
RS.Close();
CN.Close();
RS = null;
CN = null;

目次に戻る

△　qt_accdb.js

// test.accdb → test_accdb.xls の変換。ExcelのQueryTableを利用。
var fso, bookName, bookPath;
var ExlApp, wb, ws, qt;
var DbName, DbPath, sql, ConnStr;
var xlWorkbookNormal = -4143;

DbName = "test.accdb";
sql = "select * from TestTable;";

fso = WScript.CreateObject("Scripting.FileSystemObject");
DbPath = fso.GetAbsolutePathName(DbName);
if (fso.FileExists(DbPath) == false) {
    WScript.Echo(DbName + "がみつかりません.");
    WScript.Quit();
}
ConnStr = "ODBC;DSN=MS Access Database;DBQ=" + DbPath;

bookName = DbName.replace(/\./g, "_") + ".xls";
bookPath = fso.GetAbsolutePathName(bookName);
if (fso.FileExists(bookPath))  fso.DeleteFile(bookPath);

ExlApp = WScript.CreateObject("Excel.Application");  // Excelの起動
ExlApp.Visible = true;  // Excelを見える状態に
wb = ExlApp.Workbooks.Add();  // Workbookの新規作成
ws = wb.Worksheets(1);
qt = ws.QueryTables.Add(ConnStr, ws.Range("A1"), sql);
with (qt) {
    BackgroundQuery = false;  // バックグラウンド処理をしない
    Refresh();
    Delete();  // クエリテーブルを削除
}
wb.SaveAs(bookPath, xlWorkbookNormal);
ExlApp.Quit();

目次に戻る

△　create_mdb.js

// Accessデータベース(mdb)の作成。ADO, ADOXを利用
  // データの書き込みにはレコードセットを利用
var DbName, TableName, TypeStr;
var fso, DbPath;
var CN, ConnStr, CAT, RS, sql;

RunOn32bit();  // 32bitコマンドで当スクリプトを実行
DbName = "test.mdb";
TableName = "TestTable";
TypeStr = "氏名 varchar(20),身長 float";

fso = WScript.CreateObject("Scripting.FileSystemObject");
DbPath = fso.GetAbsolutePathName("test.mdb");
if (fso.FileExists(DbPath))  fso.DeleteFile(DbPath);
fso = null;

ConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;" +
    "Data Source=" + DbPath + ";";
CAT = WScript.CreateObject("ADOX.Catalog");
CN = CAT.Create(ConnStr);  // データベース作成
CAT = null;

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

RS = WScript.CreateObject("ADODB.Recordset");
sql = "select * from " + TableName + ";";
RS.Open(sql,CN,0,2,1);
RS.AddNew();
    RS.Fields("氏名").Value = "鈴木mdb";
    RS.Fields("身長").Value = 172.3;
RS.Update();
RS.AddNew();
    RS.Fields(0).Value = "高橋mdb";
    RS.Fields(1).Value = 168.5;
RS.Update();
RS.AddNew(["氏名", "身長"], ["田中mdb", 183.6]);
RS.Update();
RS.Close();
CN.Close();
RS = null;
CN = null;

function RunOn32bit() {
    var p_objWshShell, p_admArg, p_admArrayArguments;
    var p_admWscriptCscript, p_admCommand, i;
    p_objWshShell = WScript.CreateObject("Wscript.Shell");
    if (p_objWshShell.Environment("Process").Item(
      "PROCESSOR_ARCHITECTURE") != "x86") {
        p_admArrayArguments = "";
        if (WScript.Arguments.length > 0) {
            for (i = 0; i < WScript.Arguments.length; i++) {
              p_admArg = WScript.Arguments(i);
              p_admArrayArguments = p_admArrayArguments +
                  " \"" + p_admArg + "\"";
            }
        }
        if (WScript.FullName.toLowerCase().indexOf("wscript") > 0) {
            p_admWscriptCscript = "WScript.exe";
        } else {
            p_admWscriptCscript = "CScript.exe";
        }
        p_admCommand = "\"" + p_objWshShell.Environment("Process").Item(
            "windir") + "\\SysWOW64\\" + p_admWscriptCscript + "\" \"" +
            WScript.ScriptFullName + "\"" + p_admArrayArguments;
        p_objWshShell.Run(p_admCommand);
        WScript.Quit();
    }
}

目次に戻る

△　echo_mdb.js

// test.mdb の中身を確認する
var DbName, TableName;
var fso, DbPath;
var CN, ConnStr, CAT, RS, sql, MyStr;

DbName = "test.mdb";
TableName = "TestTable";

fso = WScript.CreateObject("Scripting.FileSystemObject");
DbPath = fso.GetAbsolutePathName(DbName);
if (fso.FileExists(DbPath) == false) {
    WScript.Echo(DbName + "がみつかりません.");
    WScript.Quit();
}
fso = null;

ConnStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + DbPath + ";";
CN = WScript.CreateObject("ADODB.Connection");
CN.Open(ConnStr);
RS = WScript.CreateObject("ADODB.Recordset");
sql = "select * from " + TableName + ";";
RS.Open(sql,CN,0,1,1);
while (RS.EOF != true) {
    MyStr = RS.Fields("氏名").Value + ", " + RS.Fields("身長").Value;
    WScript.Echo(MyStr);
    RS.MoveNext();
}
RS.Close();
CN.Close();
RS = null;
CN = null;

目次に戻る

△　create_accdb02.js

// Accessデータベース(accdb)の作成。ADO, ADOXを利用
  // データの書き込みにはSQL命令のinsertを利用
var DbName, TableName, TypeStr;
var fso, DbPath;
var CN, ConnStr, CAT, sql;

DbName = "test.accdb";
TableName = "TestTable";
TypeStr = "氏名 varchar(20),身長 float";

fso = WScript.CreateObject("Scripting.FileSystemObject");
DbPath = fso.GetAbsolutePathName(DbName);
if (fso.FileExists(DbPath))  fso.DeleteFile(DbPath);
fso = null;

ConnStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + DbPath + ";";
CAT = WScript.CreateObject("ADOX.Catalog");
CN = CAT.Create(ConnStr);  // データベース作成
CAT = null;

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

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

CN.Close();
CN = null;

目次に戻る

△　create_mdb02.js

// Accessデータベース(mdb)の作成。ADO, ADOXを利用
  // データの書き込みにはSQL命令のinsertを利用
var DbName, TableName, TypeStr;
var fso, DbPath;
var CN, ConnStr, CAT, sql;

RunOn32bit();  // 32bitコマンドで当スクリプトを実行
DbName = "test.mdb";
TableName = "TestTable";
TypeStr = "氏名 varchar(20),身長 float";

fso = WScript.CreateObject("Scripting.FileSystemObject");
DbPath = fso.GetAbsolutePathName("test.mdb");
if (fso.FileExists(DbPath))  fso.DeleteFile(DbPath);
fso = null;

ConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;" +
    "Data Source=" + DbPath + ";";
CAT = WScript.CreateObject("ADOX.Catalog");
CN = CAT.Create(ConnStr);  // データベース作成
CAT = null;

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

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

CN.Close();
CN = null;


function RunOn32bit() {
    var p_objWshShell, p_admArg, p_admArrayArguments;
    var p_admWscriptCscript, p_admCommand, i;
    p_objWshShell = WScript.CreateObject("Wscript.Shell");
    if (p_objWshShell.Environment("Process").Item(
      "PROCESSOR_ARCHITECTURE") != "x86") {
        p_admArrayArguments = "";
        if (WScript.Arguments.length > 0) {
            for (i = 0; i < WScript.Arguments.length; i++) {
              p_admArg = WScript.Arguments(i);
              p_admArrayArguments = p_admArrayArguments +
                  " \"" + p_admArg + "\"";
            }
        }
        if (WScript.FullName.toLowerCase().indexOf("wscript") > 0) {
            p_admWscriptCscript = "WScript.exe";
        } else {
            p_admWscriptCscript = "CScript.exe";
        }
        p_admCommand = "\"" + p_objWshShell.Environment("Process").Item(
            "windir") + "\\SysWOW64\\" + p_admWscriptCscript + "\" \"" +
            WScript.ScriptFullName + "\"" + p_admArrayArguments;
        p_objWshShell.Run(p_admCommand);
        WScript.Quit();
    }
}

目次に戻る

2.　VBScript

△　create_accdb.vbs

' Accessデータベース(accdb)の作成。ADO, ADOXを利用
    ' データの書き込みにはレコードセットを利用
Option Explicit
Dim DbName, TableName, TypeStr
Dim FSO, DbPath
Dim CN, ConnStr, CAT, RS, sql

DbName = "test.accdb"
TableName = "TestTable"
TypeStr = "氏名 varchar(20),身長 float"

Set FSO = CreateObject("Scripting.FileSystemObject")
DbPath = FSO.GetAbsolutePathName(DbName)
If (FSO.FileExists(DbPath) = True) Then FSO.DeleteFile(DbPath)
Set FSO = Nothing

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)  ' テーブル作成

Set RS = CreateObject("ADODB.Recordset")
sql = "select * from " & TableName & ";"
RS.Open sql,CN,0,2,1
RS.AddNew
    RS.Fields("氏名").Value = "鈴木accdb"
    RS.Fields("身長").Value = 172.3
RS.Update
RS.AddNew
    RS.Fields(0).Value = "高橋accdb"
    RS.Fields(1).Value = 168.5
RS.Update
RS.AddNew Array("氏名", "身長"), Array("田中accdb", 183.6)
RS.Update
RS.Close
CN.Close
Set RS = Nothing
Set CN = Nothing

目次に戻る

△　qt_accdb.vbs

' test.accdb → test_accdb.xls の変換。ExcelのQueryTableを利用。
Option Explicit
Dim FSO, BookName, BookPath
Dim EXLapp, WBobj, WSobj, QTobj
Dim DbName, DbPath, sql, ConnStr
Const xlWorkbookNormal = -4143

DbName = "test.accdb"
sql = "select * from TestTable;"

Set FSO = CreateObject("Scripting.FileSystemObject")
DbPath = FSO.GetAbsolutePathName(DbName)
If (FSO.FileExists(DbPath) = False) Then
    WScript.Echo DbName & "がみつかりません."
    WScript.Quit
End If
ConnStr = "ODBC;DSN=MS Access Database;DBQ=" & DbPath

BookName = Replace(DbName, ".", "_") & ".xls"
BookPath = FSO.GetAbsolutePathName(BookName)
If (FSO.FileExists(BookPath) = True) Then FSO.DeleteFile(BookPath)

Set EXLapp = CreateObject("Excel.Application")  ' Excelの起動
EXLapp.Visible = True  ' Excelを見える状態に
Set WBobj = EXLapp.Workbooks.Add()  ' Workbookの新規作成
Set WSobj = WBobj.Worksheets(1)
Set QTobj = WSobj.QueryTables.Add(ConnStr, WSobj.Range("A1"), sql)
With QTobj
    .BackgroundQuery = False  ' バックグラウンド処理をしない
    .Refresh
    .Delete  ' クエリテーブルを削除
End With
WBobj.SaveAs BookPath, xlWorkbookNormal
EXLapp.quit

目次に戻る

△　create_mdb.vbs

' Accessデータベース(mdb)の作成。ADO, ADOXを利用
    ' データの書き込みにはレコードセットを利用
Option Explicit
Dim DbName, TableName, TypeStr
Dim FSO, DbPath
Dim CN, ConnStr, CAT, RS, sql

RunOn32bit  ' 32bitコマンドで当スクリプトを実行
DbName = "test.mdb"
TableName = "TestTable"
TypeStr = "氏名 varchar(20),身長 float"

Set FSO = CreateObject("Scripting.FileSystemObject")
DbPath = FSO.GetAbsolutePathName("test.mdb")
If (FSO.FileExists(DbPath) = True) Then FSO.DeleteFile(DbPath)
Set FSO = Nothing

ConnStr = "Provider=Microsoft.Jet.OLEDB.4.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)  ' テーブル作成

Set RS = CreateObject("ADODB.Recordset")
sql = "select * from " & TableName & ";"
RS.Open sql,CN,0,2,1
RS.AddNew
    RS.Fields("氏名").Value = "鈴木mdb"
    RS.Fields("身長").Value = 172.3
RS.Update
RS.AddNew
    RS.Fields(0).Value = "高橋mdb"
    RS.Fields(1).Value = 168.5
RS.Update
RS.AddNew Array("氏名", "身長"), Array("田中mdb", 183.6)
RS.Update
RS.Close
CN.Close
Set RS = Nothing
Set CN = Nothing


Public Sub RunOn32bit
    Dim p_objWshShell, p_admArg, p_admArrayArguments
    Dim p_admWscriptCscript, p_admCommand
    Set p_objWshShell = CreateObject("Wscript.Shell")
    If p_objWshShell.Environment("Process").Item( _
      "PROCESSOR_ARCHITECTURE") <> "x86" Then
        If Not WScript.Arguments.Count = 0 Then
            For Each p_admArg In Wscript.Arguments
              p_admArrayArguments = p_admArrayArguments & _
                  " """ & p_admArg & """"
            Next
        End If
        If InStr(LCase(WScript.FullName), "wscript") > 0 Then
            p_admWscriptCscript = "WScript.exe"
        Else
            p_admWscriptCscript = "CScript.exe"
        End If
        p_admCommand = """" & p_objWshShell.Environment("Process").Item( _
            "windir") & "\SysWOW64\" & p_admWscriptCscript & """ """ & _
            WScript.ScriptFullName & """" & p_admArrayArguments
        p_objWshShell.Run p_admCommand
        WScript.Quit
    End If
End Sub

目次に戻る

△　echo_mdb.vbs

' test.mdb の中身を確認する
Option Explicit
Dim DbName, TableName
Dim FSO, DbPath
Dim CN, ConnStr, CAT, RS, sql, MyStr

DbName = "test.mdb"
TableName = "TestTable"

Set FSO = CreateObject("Scripting.FileSystemObject")
DbPath = FSO.GetAbsolutePathName(DbName)
If (FSO.FileExists(DbPath) = False) Then
    WScript.Echo DbName & "がみつかりません."
    WScript.Quit
End If
Set FSO = Nothing

ConnStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DbPath & ";"
Set CN = CreateObject("ADODB.Connection")
CN.Open ConnStr
Set RS = CreateObject("ADODB.Recordset")
sql = "select * from " & TableName & ";"
RS.Open sql,CN,0,1,1
Do Until RS.EOF
    MyStr = RS.Fields("氏名").Value & ", " & RS.Fields("身長").Value
    WScript.Echo MyStr
    RS.MoveNext
Loop
RS.Close
CN.Close
Set RS = Nothing
Set CN = Nothing

目次に戻る

△　create_accdb02.vbs

' Accessデータベース(accdb)の作成。ADO, ADOXを利用
    ' データの書き込みにはSQL命令のinsertを利用
Option Explicit
Dim DbName, TableName, TypeStr
Dim FSO, DbPath
Dim CN, ConnStr, CAT, sql

DbName = "test.accdb"
TableName = "TestTable"
TypeStr = "氏名 varchar(20),身長 float"

Set FSO = CreateObject("Scripting.FileSystemObject")
DbPath = FSO.GetAbsolutePathName(DbName)
If (FSO.FileExists(DbPath) = True) Then FSO.DeleteFile(DbPath)
Set FSO = Nothing

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('鈴木accdb', 172.3);"
CN.Execute(sql)
sql = "insert into TestTable (氏名,身長)  values('高橋accdb', 168.5);"
CN.Execute(sql)
sql = "insert into TestTable (氏名,身長)  values('田中accdb', 183.6);"
CN.Execute(sql)

CN.Close
Set CN = Nothing

目次に戻る

△　create_mdb02.vbs

' Accessデータベース(mdb)の作成。ADO, ADOXを利用
    ' データの書き込みにはSQL命令のinsertを利用
Option Explicit
Dim DbName, TableName, TypeStr
Dim FSO, DbPath
Dim CN, ConnStr, CAT, sql

RunOn32bit  ' 32bitコマンドで当スクリプトを実行
DbName = "test.mdb"
TableName = "TestTable"
TypeStr = "氏名 varchar(20),身長 float"

Set FSO = CreateObject("Scripting.FileSystemObject")
DbPath = FSO.GetAbsolutePathName("test.mdb")
If (FSO.FileExists(DbPath) = True) Then FSO.DeleteFile(DbPath)
Set FSO = Nothing

ConnStr = "Provider=Microsoft.Jet.OLEDB.4.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('鈴木mdb', 172.3);"
CN.Execute(sql)
sql = "insert into TestTable (氏名,身長)  values('高橋mdb', 168.5);"
CN.Execute(sql)
sql = "insert into TestTable (氏名,身長)  values('田中mdb', 183.6);"
CN.Execute(sql)

CN.Close
Set CN = Nothing


Public Sub RunOn32bit
    Dim p_objWshShell, p_admArg, p_admArrayArguments
    Dim p_admWscriptCscript, p_admCommand
    Set p_objWshShell = CreateObject("Wscript.Shell")
    If p_objWshShell.Environment("Process").Item( _
      "PROCESSOR_ARCHITECTURE") <> "x86" Then
        If Not WScript.Arguments.Count = 0 Then
            For Each p_admArg In Wscript.Arguments
              p_admArrayArguments = p_admArrayArguments & _
                  " """ & p_admArg & """"
            Next
        End If
        If InStr(LCase(WScript.FullName), "wscript") > 0 Then
            p_admWscriptCscript = "WScript.exe"
        Else
            p_admWscriptCscript = "CScript.exe"
        End If
        p_admCommand = """" & p_objWshShell.Environment("Process").Item( _
            "windir") & "\SysWOW64\" & p_admWscriptCscript & """ """ & _
            WScript.ScriptFullName & """" & p_admArrayArguments
        p_objWshShell.Run p_admCommand
        WScript.Quit
    End If
End Sub

～　以上　～

Program Page: Accessファイルの新規作成

《このページの目次》

1. JScript

△ create_accdb.js

△ qt_accdb.js

△ create_mdb.js

△ echo_mdb.js

△ create_accdb02.js

△ create_mdb02.js

2. VBScript

△ create_accdb.vbs

△ qt_accdb.vbs

△ create_mdb.vbs

△ echo_mdb.vbs

△ create_accdb02.vbs

△ create_mdb02.vbs

1.　JScript

△　create_accdb.js

△　qt_accdb.js

△　create_mdb.js

△　echo_mdb.js

△　create_accdb02.js

△　create_mdb02.js

2.　VBScript

△　create_accdb.vbs

△　qt_accdb.vbs

△　create_mdb.vbs

△　echo_mdb.vbs

△　create_accdb02.vbs

△　create_mdb02.vbs