MENU
インフラエンジニア。得た知識や日々の気づきと、生活に便利な技術やPC周辺機器のレビューを書いています
  1. ホーム
  2. インフラ
  3. データベース
  4. VBSとVBAでSQL Serverのレコードを操作する

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オブジェクトを使用しない。
データベース プログラミング
DELETE Excel VBA INSERT SELECT SQL SQL Server UPDATE VBA VBS VBScript Visual Basic 違い

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

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

関連記事

コメント

コメント一覧 （2件）

  • 令和にもなってVB6のプログラム修正を依頼される工場エンジニアです
    VB６のSQL接続がわからずここにたどり着いて無事解決しました
    感謝です

    返信

    • 記事が参考になって大変嬉しく思います。
      コメントありがとうございました！

      返信

コメントする

コメントは日本語で入力してください。(スパム対策)

CAPTCHA

もくじ
閉じる