VBSとVBAでSQL Serverのレコードを操作する

はじめに

SQL Serverを構築して、データベース、テーブル、そしてレコードも挿入した。

今回はVBSとVBAを使い、このSQL Serverに対してレコードの操作を行ってみたいと思う。

VBSとVBAで若干書き方が異なるので、その違いを確認しながら実行してみたいと思ったのです。

関連記事

SQL Serverの構築については下の記事でまとめている。

【SQLServer】データベース作成~データ挿入。一通りのSQL文(外部キー)

SQL Serverにデータベースを作成する手順(SQL)については下の記事に書いた。

【SQLServer】データベース作成~データ挿入。一通りのSQL文(外部キー)

テーブルのデータ

SQL Server上のテーブルのデータは下記の通りとなっている。これからSQL文を実行していくので、少し頭に入れておいていただくと助かる。

[Users]テーブル

UserID GivenName FamilyName Age JoinDate FactoryID
U001 John Smith 33 NULL F001
U002 Raymond Non 45 NULL F002
U003 Bob Cruise 23 NULL F003




SELECT文でレコードを参照する

さて、まずはSELECT文でレコードを参照したいと思う。VBSとVBAのコードはそれぞれ下に書く。

内容は、[UserID]列が「U001」の[GivenName]列の値を参照する内容だ。「John」が表示されるはずだね。

VBS(SELECT文)

' 変数の宣言
Dim adoConn
Dim adoRset
Dim adoComm
Dim strSQL

' ADODB.Connectionオブジェクトを変数に格納。
Set adoConn = CreateObject("ADODB.Connection")
' データベースへ接続。
adoConn.Open = "Driver={SQL Server}; Server=172.31.0.211\SQLEXPRESS; Database=Masters; UID=sa; PWD=*****;"

' SQL文を作成する。
strSQL = "SELECT GivenName FROM Users WHERE UserID='U001'"

' ADODB.Commandオブジェクトを変数に格納し、SQL文をセットする。
Set adoComm = CreateObject("ADODB.Command")
adoComm.ActiveConnection = adoConn
adoComm.CommandText = strSQL

' ADODB.Recordsetオブジェクトを変数に格納し、SQL文を実行する。
Set adoRset = CreateObject("ADODB.Recordset")
adoRset.Open adoComm

' SQL文の実行結果の'GivenName'列の値をポップアップで表示する。
MsgBox adoRset.Fields("GivenName").Value

' ADODB.Connectionオブジェクトをクローズする。
adoConn.Close

下を見てくれ。表示された。

VBA(SELECT文)

Sub Connect_SQLServer()
    'ADODB.Connectionオブジェクトを変数として宣言する。
    Dim adoConn As New ADODB.Connection
    'ADODB.Recordsetオブジェクトを変数として宣言する。
    Dim adoRset As New ADODB.Recordset
    
    ' SQL文を格納する変数を宣言し、SQL文を格納する。
    Dim strSQL As String
    strSQL = "SELECT GivenName FROM Users WHERE UserID='U001'"
    
    ' データベースに接続する。
    adoConn.Open "Driver={SQL Server}; Server=172.31.0.211\SQLEXPRESS; Database=Masters; UID=sa; PWD=*****;"
    
    ' SQL文を実行する。
    adoRset.Open strSQL, adoConn
    
    ' 実行したSQL文の結果の'GivenName'列をポップアップで表示する。
    MsgBox adoRset.Fields("GivenName").Value
    
    ' ADODB.RecordsetとADODB.Connectionオブジェクトをクローズする。
    adoRset.Close
    adoConn.Close

End Sub

こちらも下を見てくれ。表示された。

INSERTでレコードを追加する

さて次はINSERT文で、レコードを追加してみたい。

下のレコードを追加することにする。ちなみに内容は適当なので気にしないでくれ。

UserID GivenName FamilyName Age JoinDate FactoryID
U004 Atie Kruga 53 NULL F002

VBS(INSERT文)

' 変数の宣言
Dim adoConn
Dim adoRset
Dim adoComm
Dim strSQL

' ADODB.Connectionオブジェクトを変数に格納。
Set adoConn = CreateObject("ADODB.Connection")
' データベースへ接続。
adoConn.Open = "Driver={SQL Server}; Server=172.31.0.211\SQLEXPRESS; Database=Masters; UID=sa; PWD=*****;"

' SQL文を作成する。
strSQL = "INSERT INTO Users (UserID, GivenName, FamilyName, Age, FactoryID) VALUES ('U004', 'Atie', 'Kruga', 53, 'F002');"

' ADODB.Commandオブジェクトを変数に格納し、SQL文をセットする。
Set adoComm = CreateObject("ADODB.Command")
adoComm.ActiveConnection = adoConn
adoComm.CommandText = strSQL

' ADODB.Recordsetオブジェクトを変数に格納し、SQL文を実行する。
Set adoRset = CreateObject("ADODB.Recordset")
adoRset.Open adoComm

' ADODB.Connectionオブジェクトをクローズする。
adoConn.Close

実行したら、さっそくSSMSでテーブルの状態を確認してみる。レコードが追加されたことが確認できる。

VBA(INSERT文)

Sub INSERT_SQLServer()
    'ADODB.Connectionオブジェクトを変数として宣言する。
    Dim adoConn As New ADODB.Connection
    'ADODB.Recordsetオブジェクトを変数として宣言する。
    Dim adoRset As New ADODB.Recordset
    
    ' SQL文を格納する変数を宣言し、SQL文を格納する。
    Dim strSQL As String
    strSQL = "INSERT INTO Users (UserID, GivenName, FamilyName, Age, FactoryID) VALUES ('U004', 'Atie', 'Kruga', 53, 'F002');"
    
    ' データベースに接続する。
    adoConn.Open "Driver={SQL Server}; Server=172.31.0.211\SQLEXPRESS; Database=Masters; UID=sa; PWD=*****;"
    
    ' SQL文を実行する。
    adoRset.Open strSQL, adoConn
    
    ' ADODB.Connectionオブジェクトをクローズする。
    adoConn.Close

End Sub

結果の確認は同じだが、追加されたことが確認できる。当たり前だが、VBSで追加したレコードはDelete文で消してから、VBAを実行した。




UPDATE文でレコードを更新する

さて次はUPDATE文でレコードを更新してみよう。UsersテーブルのRaymondさんの[JoinDate]に今日の日付(2020/2/24)を入れる(更新)してみようと思う。

UserID GivenName FamilyName Age JoinDate FactoryID
U002 Raymond Non 45 NULL F002

⇓  ⇓  ⇓  ⇓  ⇓  ⇓  ⇓  ⇓  ⇓

UserID GivenName FamilyName Age JoinDate FactoryID
U002 Raymond Non 45 2020-02-24 F002

VBS(UPDATE文)

' 変数の宣言
Dim adoConn
Dim adoRset
Dim adoComm
Dim strSQL

' ADODB.Connectionオブジェクトを変数に格納。
Set adoConn = CreateObject("ADODB.Connection")
' データベースへ接続。
adoConn.Open = "Driver={SQL Server}; Server=172.31.0.211\SQLEXPRESS; Database=Masters; UID=sa; PWD=*****;"

' SQL文を作成する。
strSQL = "UPDATE Users SET JoinDate = '2020-02-24' WHERE GivenName = 'Raymond';"

' ADODB.Commandオブジェクトを変数に格納し、SQL文をセットする。
Set adoComm = CreateObject("ADODB.Command")
adoComm.ActiveConnection = adoConn
adoComm.CommandText = strSQL

' ADODB.Recordsetオブジェクトを変数に格納し、SQL文を実行する。
Set adoRset = CreateObject("ADODB.Recordset")
adoRset.Open adoComm

' ADODB.Connectionオブジェクトをクローズする。
adoConn.Close

下を見てくれ。更新された。

VBA(UPDATE文)

Sub UPDATE_SQLServer()
    'ADODB.Connectionオブジェクトを変数として宣言する。
    Dim adoConn As New ADODB.Connection
    'ADODB.Recordsetオブジェクトを変数として宣言する。
    Dim adoRset As New ADODB.Recordset
    
    ' SQL文を格納する変数を宣言し、SQL文を格納する。
    Dim strSQL As String
    strSQL = "UPDATE Users SET JoinDate = '2020-02-24' WHERE GivenName = 'Raymond';"
    
    ' データベースに接続する。
    adoConn.Open "Driver={SQL Server}; Server=172.31.0.211\SQLEXPRESS; Database=Masters; UID=sa; PWD=*****;"
    
    ' SQL文を実行する。
    adoRset.Open strSQL, adoConn
    
    ' ADODB.Connectionオブジェクトをクローズする。
    adoConn.Close

End Sub

実行してエラーが出なければOKだ。こちらもしっかりと更新してくれた。

Delete文でレコードを削除

さて、Delete文だね。ここだけの話だが、一回Where句を指定するのを忘れて、テーブルのデータをすべて消してしまった。ここだけの話だぜ。

くだらないジョークは置いといて、さっそくコードにいってみよう。Bobさんを消すぜ。

VBS(Delete文)

' 変数の宣言
Dim adoConn
Dim adoRset
Dim adoComm
Dim strSQL

' ADODB.Connectionオブジェクトを変数に格納。
Set adoConn = CreateObject("ADODB.Connection")
' データベースへ接続。
adoConn.Open = "Driver={SQL Server}; Server=172.31.0.211\SQLEXPRESS; Database=Masters; UID=sa; PWD=Ledger2008122;"

' SQL文を作成する。
strSQL = "DELETE FROM Users WHERE GivenName = 'Bob';"

' ADODB.Commandオブジェクトを変数に格納し、SQL文をセットする。
Set adoComm = CreateObject("ADODB.Command")
adoComm.ActiveConnection = adoConn
adoComm.CommandText = strSQL

' ADODB.Recordsetオブジェクトを変数に格納し、SQL文を実行する。
Set adoRset = CreateObject("ADODB.Recordset")
adoRset.Open adoComm

' ADODB.Connectionオブジェクトをクローズする。
adoConn.Close

エラーは発生せず。Usersテーブルの全データを見てみるとちゃんと消えている。

VBA(Delete文)

Sub UPDATE_SQLServer()
    'ADODB.Connectionオブジェクトを変数として宣言する。
    Dim adoConn As New ADODB.Connection
    'ADODB.Recordsetオブジェクトを変数として宣言する。
    Dim adoRset As New ADODB.Recordset
    
    ' SQL文を格納する変数を宣言し、SQL文を格納する。
    Dim strSQL As String
    strSQL = "UPDATE Users SET JoinDate = '2020-02-24' WHERE GivenName = 'Raymond';"
    
    ' データベースに接続する。
    adoConn.Open "Driver={SQL Server}; Server=172.31.0.211\SQLEXPRESS; Database=Masters; UID=sa; PWD=*****;"
    
    ' SQL文を実行する。
    adoRset.Open strSQL, adoConn
    
    ' ADODB.Connectionオブジェクトをクローズする。
    adoConn.Close

End Sub

こいつもうまくいった。エラーは発生せず、Bobのレコードが削除された。




まとめ

VBAとVBSで以下の違いがあるんじゃないかと思う。簡単にまとめてみたので参考にしていただければと思う。

VBS VBA
オブジェクト変数の宣言
(ADODB.Connection)
Dim ‘変数名’
Set ‘変数名’ = CreateObject(“ADODB.Connection”)
Dim ‘変数名’ As New ADODB.Connection
データベースへの接続 同じ
SQL文の実行 ADODB.Commandオブジェクトを使用する。 ADODB.Commandオブジェクトを使用しない。

この記事が気に入ったら
フォローしてね!

よかったらシェアしてね!

コメント

コメントする

CAPTCHA