VBScript で Access を操作する方法

2018年9月30日 22:41 0 件のコメント

今回は「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

参考記事

データ追加（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を使ったデータ投入
  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

参考記事

データ更新（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を使ったデータ更新
  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

参考記事

データ削除（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を使ったデータ削除
  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

参考記事

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

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