VBScript で Access を操作する方法

今回は「VBScriptでAccessを操作する方法」についてまとめます。

アプリケーションの起動/終了

インスタンス生成することで起動し、Quit を実行することで終了できる。

Sub Main()
  Dim objAccess : Set objAccess = CreateObject("Access.Application")
  objAccess.Visible = True
  
  ' 何か処理…
  
  objAccess.Quit
  Set objAccess = Nothing
End Sub

Main

データベースへ接続/切断

接続文字列 strConnection に含まれるファイルパス strFilePath は絶対パスになるよう構成する。

VBScriptなので接続文字列を組み立てているが、Access VBA を利用するのであれば CurrentProject.Connection に接続文字列があるので、これを利用すると簡単。

Sub Main()
  Dim objAccess, strFilePath, strConnection, objConnection, objRecordset, strQuery

  ' Accessオブジェクト生成
  Set objAccess = CreateObject("Access.Application")
  objAccess.Visible = True

  ' DB接続文字列
  strFilePath = GetCurrentDirectory() & "\Database1.accdb"
  strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                  "Data Source=" & strFilePath & ";"

  ' 接続
  Set objConnection = CreateObject("ADODB.Connection")
  objConnection.Open strConnection
  
  ' DB操作…
  
  ' 切断
  objConnection.Close

  objAccess.Quit
  Set objConnection = Nothing
  Set objAccess = Nothing
End Sub

Function GetCurrentDirectory()
  Dim objShell : Set objShell = CreateObject("WScript.Shell")
  GetCurrentDirectory = objShell.CurrentDirectory
End Function

Main

参考記事

• MSDN - Connection オブジェクト
• MSDN - Open、Close メソッドの例 (VBScript)
• REONTOSANTA - VBScriptでAccessのデータを操作する

テーブルの操作

テーブル作成

ADODB.Command を利用して SQL によるテーブル作成を行う。 利用できるデータ型については下の表を参照。

Sub Main()
  Dim objAccess, strFilePath, strConnection, objConnection, objCommand
  Set objAccess = CreateObject("Access.Application")
  objAccess.Visible = False

  ' DB接続文字列
  strFilePath = GetCurrentDirectory() & "\Database1.accdb"
  strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                  "Data Source=" & strFilePath & ";"

  ' 接続
  Set objConnection = CreateObject("ADODB.Connection")
  objConnection.Open strConnection

  ' クエリ実行
  Set objCommand = CreateObject("ADODB.Command")
  objCommand.ActiveConnection = objConnection
  objCommand.CommandText = "CREATE TABLE [T_COMPANY] (" & _ 
                           "  [ID]            COUNTER," & _ 
                           "  [DISPLAY_NAME]  TEXT(128) NOT NULL," & _ 
                           "  [REGIST_DATE]   DATE," & _ 
                           "  PRIMARY KEY([ID], [DISPLAY_NAME])" & _ 
                           ");"
  objCommand.Execute

  ' 切断
  objConnection.Close

  objAccess.Quit
  Set objCommand = Nothing
  Set objConnection = Nothing
  Set objAccess = Nothing
End Sub

Function GetCurrentDirectory()
  Dim objShell : Set objShell = CreateObject("WScript.Shell")
  GetCurrentDirectory = objShell.CurrentDirectory
End Function

Main

データ型		内部設定
論理名	物理名	DataTypeEnum	Size
テキスト型	TEXT	adVarWChar	536870910
メモ型	LONGTEXT	adVarWChar	536870910
数値型（バイト型）	BYTE	adUnsignedTinyInt	1
数値型（整数型）	SHORT	adSmallInt	2
数値型（長整数型）	LONG	adInteger	4
数値型（単精度浮動小数点型）	SINGLE	adSingle	4
数値型（倍精度浮動小数点型）	DOUBLE	adDouble	8
日付/時刻型	DATETIME	adDate	8
通貨型	CURRENCY	adCurrency	8
オートナンバー型	COUNTER	adInteger	4
Yes/No型	BIT	adBoolean	2

テーブル削除

テーブル作成同様 ADODB.Command を利用してクエリで削除する。

Sub Main()
  Dim objAccess, strFilePath, strConnection, objConnection, objCommand
  Set objAccess = CreateObject("Access.Application")
  objAccess.Visible = False

  ' DB接続文字列
  strFilePath = GetCurrentDirectory() & "\Database1.accdb"
  strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                  "Data Source=" & strFilePath & ";"

  ' 接続
  Set objConnection = CreateObject("ADODB.Connection")
  objConnection.Open strConnection

  ' クエリ実行
  Set objCommand = CreateObject("ADODB.Command")
  objCommand.ActiveConnection = objConnection
  objCommand.CommandText = "DROP TABLE [T_COMPANY]"
  objCommand.Execute

  ' 切断
  objConnection.Close

  objAccess.Quit
  Set objCommand = Nothing
  Set objConnection = Nothing
  Set objAccess = Nothing
End Sub

Function GetCurrentDirectory()
  Dim objShell : Set objShell = CreateObject("WScript.Shell")
  GetCurrentDirectory = objShell.CurrentDirectory
End Function

Main

インデックス作成

Sub Main()
  Dim objAccess, strFilePath, strConnection, objConnection, objCommand

  ' Accessオブジェクト生成
  Set objAccess = CreateObject("Access.Application")
  objAccess.Visible = False

  ' DB接続文字列
  strFilePath = GetCurrentDirectory() & "\Database1.accdb"
  strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                  "Data Source=" & strFilePath & ";"

  ' 接続
  Set objConnection = CreateObject("ADODB.Connection")
  objConnection.Open strConnection

  ' クエリ実行
  Set objCommand = CreateObject("ADODB.Command")
  objCommand.ActiveConnection = objConnection
  objCommand.CommandText = "CREATE TABLE [T_COMPANY] (" & _ 
                           "  [ID]            COUNTER," & _ 
                           "  [DISPLAY_NAME]  TEXT(128) NOT NULL," & _ 
                           "  [REGIST_DATE]   DATE," & _ 
                           "  PRIMARY KEY([ID])" & _ 
                           ");"
  objCommand.Execute

  objCommand.CommandText = "CREATE INDEX [IX_COMPANY] ON [T_COMPANY] (" & _
                           "  [DISPLAY_NAME] ASC" & _
                           ")"
  objCommand.Execute

  ' 切断
  objConnection.Close

  objAccess.Quit
  Set objCommand = Nothing
  Set objConnection = Nothing
  Set objAccess = Nothing
End Sub

Function GetCurrentDirectory()
  Dim objShell : Set objShell = CreateObject("WScript.Shell")
  GetCurrentDirectory = objShell.CurrentDirectory
End Function

Main

参考記事

• WSH JScriptを使いこなそう ～Access操作～
• Microsoft Docs - Microsoft Access データ型
• MSDN - CREATE INDEX ステートメント

レコードの操作

データ選択（SELECT）

パラメタライズドクエリを実行してデータ取得し、表示するサンプル。

パラメタライズドクエリは PARAMETERS ステートメント を最初に宣言することで利用できる。 変数名は "[]" (角括弧) で囲んで指定する。 変数の具体的な値は PARAMETERS ステートメント で宣言されたパラメータ順に Command.Parameters へ設定する。 （＝ PARAMETERS ステートメント を宣言していれば、実行するクエリの順序は無視してよい。）

取得された Recordset は Do ... Loop で EOF になるまで Recordset.MoveNext で順に取り出して利用する。 Recordset 内のカーソルを移動するメソッドは MoveFirst, MoveLast, MoveNext, MovePrevious がある。

Sub Main()
  Dim objAccess, strFilePath, strConnection, objConnection, objRecordset

  ' Accessオブジェクト生成
  Set objAccess = CreateObject("Access.Application")
  objAccess.Visible = False

  ' DB接続文字列
  strFilePath = GetCurrentDirectory() & "\Database1.accdb"
  strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                  "Data Source=" & strFilePath & ";"

  ' 接続
  Set objConnection = CreateObject("ADODB.Connection")
  objConnection.Open strConnection

  ' クエリ作成
  Dim strParameters, strQuery
  strParameters = "PARAMETERS [UserName] TEXT, [Gender] BYTE; "
  strQuery = "SELECT * FROM T_MEMBER" & _
             " WHERE DISPLAY_NAME = [UserName]" & _
             "   AND GENDER = [Gender];"

  ' クエリ実行
  Set objCommand = CreateObject("ADODB.Command")
  objCommand.ActiveConnection = objConnection
  objCommand.CommandText = strParameters & strQuery
  objCommand.CommandType = 1    ' adCmdText = 1
  objCommand.Parameters(0) = "tanaka"
  objCommand.Parameters(1) = 1
  Set objRecordset = objCommand.Execute()

  Do While Not objRecordset.EOF
    wscript.echo objRecordset("ID") & ", " & objRecordset("DISPLAY_NAME")
    objRecordset.MoveNext
  Loop

  ' 切断
  objConnection.Close

  objAccess.Quit
  Set objRecordset = Nothing
  Set objConnection = Nothing
  Set objAccess = Nothing
End Sub

Function GetCurrentDirectory()
  Dim objShell : Set objShell = CreateObject("WScript.Shell")
  GetCurrentDirectory = objShell.CurrentDirectory
End Function

Main

参考記事

• MSDN - PARAMETERS 宣言 (Microsoft Access SQL)
• MSDN - Recordset オブジェクト
• MSDN - Recordset オブジェクトのプロパティ、メソッド、およびイベント

データ追加（INSERT）

INSERT ステートメント で挿入することもできるが、投入量が多い場合バッファ処理される Recordset を利用した以下の INSERT が速い。

Recordset.AddNew を行うとカーソルが新しいレコードとなるため、そのまま投入したいデータを設定する。 一通りデータ投入が終わったタイミングで Recordset.Update を実行することで更新される。

INSERT クエリで実行したい場合、上記「データ選択」で行った ADODB.Command を利用した方法で投入できる。 こちらの方法の場合、毎回 Command.Execute を実行する必要がある。

Sub Main()
  Dim objAccess, strFilePath, strConnection, objConnection, objCommand, objRecordset

  ' Accessオブジェクト生成
  Set objAccess = CreateObject("Access.Application")
  objAccess.Visible = False

  ' DB接続文字列
  strFilePath = GetCurrentDirectory() & "\Database1.accdb"
  strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                  "Data Source=" & strFilePath & ";"

  ' 接続
  Set objConnection = CreateObject("ADODB.Connection")
  objConnection.Open strConnection

  ' クエリを使ったデータ投入
  Dim strParameters, strQuery
  strParameters = "PARAMETERS [DisplayName] TEXT, [Gender] BYTE, [LoginDate] DATE;"
  strQuery = "INSERT INTO T_USER (" & _
             " DISPLAY_NAME, GENDER, LOGIN_DATE " & _
             ") VALUES (" & _
             " [DisplayName], [Gender], [LoginDate] " & _
             ");"
  Set objCommand = CreateObject("ADODB.Command")
  objCommand.ActiveConnection = objConnection
  objCommand.CommandText = strParameters & strQuery

  objCommand.Parameters(0) = "mukai with query"
  objCommand.Parameters(1) = 1
  objCommand.Parameters(2) = "2018/9/30"
  objCommand.Execute

  objCommand.Parameters(0) = "furuya with query"
  objCommand.Parameters(1) = 2
  objCommand.Parameters(2) = "2018/3/1"
  objCommand.Execute

  ' Recordsetを使ったデータ投入
  Const adOpenStatic = 3        ' https://msdn.microsoft.com/ja-jp/library/cc389787.aspx
  Const adLockOptimistic = 3    ' https://msdn.microsoft.com/ja-jp/library/cc389829.aspx
  Const adCmdTable = 2          ' https://msdn.microsoft.com/ja-jp/library/cc389765.aspx
  Set objRecordset = CreateObject("ADODB.Recordset")
  objRecordset.Open "T_USER", objConnection, adOpenStatic, adLockOptimistic, adCmdTable

  objRecordset.AddNew
  objRecordset("DISPLAY_NAME") = "mukai with recordset"
  objRecordset("GENDER") = 1
  objRecordset("LOGIN_DATE") = "2018/9/30"

  objRecordset.AddNew
  objRecordset("DISPLAY_NAME") = "furuya with recordset"
  objRecordset("GENDER") = 2
  objRecordset("LOGIN_DATE") = "2018/3/1"

  objRecordset.Update

  ' 切断
  objConnection.Close

  objAccess.Quit
  Set objRecordset = Nothing
  Set objConnection = Nothing
  Set objAccess = Nothing
End Sub

Function GetCurrentDirectory()
  Dim objShell : Set objShell = CreateObject("WScript.Shell")
  GetCurrentDirectory = objShell.CurrentDirectory
End Function

Main

参考記事

• MSDN - Open メソッド (ADO Recordset)
• MSDN - CursorTypeEnum
• MSDN - LockTypeEnum
• MSDN - CommandTypeEnum
• MSDN - AddNew メソッド

データ更新（UPDATE）

クエリを使った更新を行う場合、WHERE 句 で指定するカラム名を テーブル名.カラム名 という指定をした方が良い。 ID はテーブル名もあわせて指定しないと条件指定が有効に働かず全量更新されてしまう。 他のカラムはテーブル名指定しなくても有効に条件指定が効く。

Recordsetを使った更新を行う場合、Find で対象を検索して削除する。 検索して見つからなかった場合、カーソルが末端に行く（ Recordset.EOF ) ので、正しく検索できたかは Recordset.EOF で確認する。 連続操作する場合、検索開始位置を最初の場所（ Recordset.MoveFirst ）へ移動しないと正しく検索できない懸念があるので注意。

Sub Main()
  Dim objAccess, strFilePath, strConnection, objConnection, objCommand, objRecordset

  ' Accessオブジェクト生成
  Set objAccess = CreateObject("Access.Application")
  objAccess.Visible = False

  ' DB接続文字列
  strFilePath = GetCurrentDirectory() & "\Database1.accdb"
  strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                  "Data Source=" & strFilePath & ";"

  ' 接続
  Set objConnection = CreateObject("ADODB.Connection")
  objConnection.Open strConnection

  ' クエリを使ったデータ更新
  Dim strParameters, strQuery
  strParameters = "PARAMETERS [Id] LONG, [DisplayName] TEXT;"
  strQuery = "UPDATE T_USER" & _
             "   SET T_USER.DISPLAY_NAME=[DisplayName]" & _
             " WHERE T_USER.ID=[Id];"

  Set objCommand = CreateObject("ADODB.Command")
  objCommand.ActiveConnection = objConnection
  objCommand.CommandText = strParameters & strQuery
  objCommand.Parameters(0) = 1
  objCommand.Parameters(1) = "ほげほげ"
  objCommand.Execute

  ' Recordsetを使ったデータ更新
  Const adOpenStatic = 3        ' https://msdn.microsoft.com/ja-jp/library/cc389787.aspx
  Const adLockOptimistic = 3    ' https://msdn.microsoft.com/ja-jp/library/cc389829.aspx
  Const adCmdTable = 2          ' https://msdn.microsoft.com/ja-jp/library/cc389765.aspx
  Set objRecordset = CreateObject("ADODB.Recordset")
  objRecordset.Open "T_USER", objConnection, adOpenStatic, adLockOptimistic, adCmdTable
  objRecordset.Find "ID=2"
  If Not objRecordset.EOF Then
    objRecordset("DISPLAY_NAME") = "ほげ２"
    objRecordset.Update
  End If

  ' 切断
  objConnection.Close

  objAccess.Quit
  Set objRecordset = Nothing
  Set objConnection = Nothing
  Set objAccess = Nothing
End Sub

Function GetCurrentDirectory()
  Dim objShell : Set objShell = CreateObject("WScript.Shell")
  GetCurrentDirectory = objShell.CurrentDirectory
End Function

Main

参考記事

• MSDN - Update メソッド
• MSDN - Find メソッド

データ削除（DELETE）

基本的に注意すべき箇所は UPDATE と同じ。

Sub Main()
  Dim objAccess, strFilePath, strConnection, objConnection, objCommand, objRecordset

  ' Accessオブジェクト生成
  Set objAccess = CreateObject("Access.Application")
  objAccess.Visible = False

  ' DB接続文字列
  strFilePath = GetCurrentDirectory() & "\Database1.accdb"
  strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                  "Data Source=" & strFilePath & ";"

  ' 接続
  Set objConnection = CreateObject("ADODB.Connection")
  objConnection.Open strConnection

  ' クエリを使ったデータ削除
  Dim strParameters, strQuery
  strParameters = "PARAMETERS [Id] LONG;"
  strQuery = "DELETE FROM T_USER" & _
             " WHERE T_USER.ID=[Id];"

  Set objCommand = CreateObject("ADODB.Command")
  objCommand.ActiveConnection = objConnection
  objCommand.CommandText = strParameters & strQuery
  objCommand.Parameters(0) = 3
  objCommand.Execute

  ' Recordsetを使ったデータ削除
  Const adOpenStatic = 3        ' https://msdn.microsoft.com/ja-jp/library/cc389787.aspx
  Const adLockOptimistic = 3    ' https://msdn.microsoft.com/ja-jp/library/cc389829.aspx
  Const adCmdTable = 2          ' https://msdn.microsoft.com/ja-jp/library/cc389765.aspx
  Set objRecordset = CreateObject("ADODB.Recordset")
  objRecordset.Open "T_USER", objConnection, adOpenStatic, adLockOptimistic, adCmdTable
  objRecordset.Find "ID=2"
  If Not objRecordset.EOF Then
    objRecordset.Delete
  End If

  ' 切断
  objConnection.Close

  objAccess.Quit
  Set objRecordset = Nothing
  Set objConnection = Nothing
  Set objAccess = Nothing
End Sub

Function GetCurrentDirectory()
  Dim objShell : Set objShell = CreateObject("WScript.Shell")
  GetCurrentDirectory = objShell.CurrentDirectory
End Function

Main

参考記事

• MSDN - Delete メソッド (ADO Recordset)

今回は「VBScript で Access を操作する方法」についてまとめました。 参考になったでしょうか？ 本記事がお役に立っていると嬉しいです！！

最後に… このブログに興味を持っていただけた方は、 ぜひ 「Facebookページ に いいね！」または 「Twitter の フォロー」 お願いします！！

• Facebook ページ
• twitter