ExcelVBA 【データベース操作編】



目次


データベースへの接続

データベースに接続する処理について解説します。

プログラムソース

Sub データベースへの接続()

Dim Cn As Object
Dim strCn As String

strCn = "Provider=SQLOLEDB;" & _
         "Data Source=localhost\SQLExpress;" & _
         "Initial Catalog=Database;User ID=sa;Password=sa;"

Set Cn = CreateObject("ADODB.Connection")

Cn.ConnectionString = strCn

Cn.Open

MsgBox "接続成功"

Cn.Close

Set Cn = Nothing

End Sub

解説

ADODB.Connection

データベース接続に使用するオブジェクト

Connection.ConnectionString = 接続文字列

接続文字列を設定します。

Connection.Open

データベースに接続します。

Connection.Close

データベースとの接続を切断します。


データの取得

データを取得する処理について解説します。

プログラムソース

Sub データの取得()

Dim Cn As Object, Rs As Object
Dim strCn As String, strSQL As String

strCn = "Provider=SQLOLEDB;" & _
         "Data Source=localhost\SQLExpress;" & _
         "Initial Catalog=Database;User ID=sa;Password=sa;"

Set Cn = CreateObject("ADODB.Connection")
Cn.ConnectionString = strCn
Cn.Open

strSQL = "SELECT * FROM 社員マスタ"

Set Rs = CreateObject("ADODB.Recordset")
Rs.Open strSQL, Cn, 1

For I = 1 To Rs.RecordCount
 Cells(I, 1).Value = Rs("社員コード").Value
 Cells(I, 2).Value = Rs("社員名").Value
 Rs.MoveNext
Next I

Rs.Close: Set Rs = Nothing
Cn.Close: Set Cn = Nothing

End Sub

解説

ADODB.Recodeset

データ操作に使用するオブジェクト

Recodeset.Open SQL文 Connectionオブジェクト

レコードセットを作成します。

Recordset.Close

レコードセットを閉じます。


SQLの実行

SQLを実行する処理について解説します。

プログラムソース

Sub SQLの実行()

Dim Cn As Object
Dim strCn As String, strSQL As String

strCn = "Provider=SQLOLEDB;" & _
        "Data Source=localhost\SQLExpress;" & _
        "Initial Catalog=Database;User ID=sa;Password=sa;"

Set Cn = CreateObject("ADODB.Connection")
Cn.ConnectionString = strCn
Cn.Open

strSQL = "UPDATE 社員マスタ " & _
         "SET 部門コード = 3 " & _
         "WHERE 社員コード = 4"

Cn.Execute strSQL

Cells(1, 1).Value = "更新完了"

Cn.Close: Set Cn = Nothing

End Sub

解説

Connection.Execute SQL文

指定されたSQL文を実行します。


トランザクション処理

トランザクション処理について解説します。

プログラムソース

Sub トランザクション処理()
On Error Resume Next
Dim Cn As Object, strCn As String, strSQL As String
Dim blnErr As Boolean : blnErr = True
strCn = "Provider=SQLOLEDB;" & _
         "Data Source=localhost\SQLExpress;" & _
         "Initial Catalog=Database;User ID=sa;Password=sa;"
Set Cn = CreateObject("ADODB.Connection")
Cn.ConnectionString = strCn
Cn.Open

Cn.BeginTrans

strSQL = "INSERT 社員マスタ VALUES(1, '社員Ａ', 1)"
Cn.Execute strSQL
If Err.Number <> 0 Then blnErr = False
strSQL = "INSERT 所有資格マスタ VALUES(1, 1, '簿記３級')"
Cn.Execute strSQL
If Err.Number <> 0 Then blnErr = False

If blnErr = True Then
   Cn.CommitTrans: MsgBox "更新完了"
Else
   Cn.RollbackTrans: MsgBox "更新失敗"
End If
Cn.Close: Set Cn = Nothing
End Sub

解説

Connection.BeginTrans

トランザクション処理を開始します。

Connection.CommitTrans

更新内容を確定する場合は、コミットします。

Connection.RollbackTrans

更新内容を破棄する場合は、ロールバックします。


データベース別の接続文字列

データベース別の接続文字列については、ご覧のとおりです。

プログラムソース

Oracle
strCn = "Provider=OraOLEDB.Oracle;" & _
         "Data Source=ホスト名:1521/XEPDB1;" & _
         "User Id=MYUSER;Password=MYUSER;"
MySQL
strCn = "DRIVER={MySQL ODBC 8.0 ANSI Driver};" & _
         "SERVER=localhost;PORT=3306; " & _
         "DATABASE=myschema;UID=root;PASSWORD=manager;"
Access
strCn = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
         "Data Source=C:\Users\home\Desktop\Database.accdb"