今回は「VBScriptでAccessを操作する方法」についてまとめます。
アプリケーションの起動/終了
インスタンス生成することで起動し、Quit
を実行することで終了できる。
1 2 3 4 5 6 7 8 9 10 11 | 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
に接続文字列があるので、これを利用すると簡単。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 | 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 |
参考記事
テーブルの操作
テーブル作成
ADODB.Command
を利用して SQL によるテーブル作成を行う。
利用できるデータ型については下の表を参照。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 | 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
を利用してクエリで削除する。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 | 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 |
インデックス作成
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 | 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 |
参考記事
レコードの操作
データ選択(SELECT)
パラメタライズドクエリを実行してデータ取得し、表示するサンプル。
パラメタライズドクエリは PARAMETERS ステートメント
を最初に宣言することで利用できる。
変数名は "[]" (角括弧)
で囲んで指定する。
変数の具体的な値は PARAMETERS ステートメント
で宣言されたパラメータ順に Command.Parameters
へ設定する。
(= PARAMETERS ステートメント
を宣言していれば、実行するクエリの順序は無視してよい。)
取得された Recordset
は Do ... Loop
で EOF
になるまで Recordset.MoveNext
で順に取り出して利用する。
Recordset
内のカーソルを移動するメソッドは MoveFirst
, MoveLast
, MoveNext
, MovePrevious
がある。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 | 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
を実行する必要がある。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 | 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を使ったデータ投入 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
)へ移動しないと正しく検索できない懸念があるので注意。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 | 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を使ったデータ更新 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" ) = "ほげ2" 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 |
参考記事
データ削除(DELETE)
基本的に注意すべき箇所は UPDATE と同じ。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 | 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を使ったデータ削除 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 |
参考記事
今回は「VBScript で Access を操作する方法」についてまとめました。 参考になったでしょうか? 本記事がお役に立っていると嬉しいです!!
最後に… このブログに興味を持っていただけた方は、 ぜひ 「Facebookページ に いいね!」または 「Twitter の フォロー」 お願いします!!