準備
接続情報
PostgreSQL
ODBC設定あり
Dim con As New ADODB.Connection '接続先情報 Const DSN As String = "PostgreSQL" 'ODBCのデータソース名 'コネクション設定 con.ConnectionString = "DSN=" & DSN & ";" 'DB接続 con.Open
ODBC設定なし
Dim con As New ADODB.Connection '接続先情報 Const DRIVER As String = "PostgreSQL Unicode" 'ドライバー Const SERVER As String = "localhost" 'サーバー名 Const DATABASE As String = "postgres" 'データベース名 Const USER_ID As String = "postgres" 'ユーザID Const PASSWORD As String = "postgres" 'パスワード 'コネクション設定 con.ConnectionString = "Driver=" & DRIVER & ";" & _ "SERVER=" & SERVER & ";" & _ "DATABASE=" & DATABASE & ";" & _ "UID=" & USER_ID & ";" & _ "PWD=" & PASSWORD 'DB接続 con.Open
ORACLE
tnsnamesを使用する
Dim con As New ADODB.Connection '接続先情報 Const PROVIDER As String = "OraOLEDB.Oracle" Const DATA_SOURCE As String = "orcl" 'ネットサービス名 Const USER_ID As String = "user" 'ユーザID Const PASSWORD As String = "password" 'パスワード 'コネクション設定 con.ConnectionString = "Provider=" & PROVIDER _ & ";Data Source=" & DATA_SOURCE _ & ";User ID=" & USER_ID _ & ";PASSWORD=" & PASSWORD 'DB接続 con.Open
tnsnamesを使用しない
Dim con As New ADODB.Connection '接続先情報 Const PROVIDER As String = "OraOLEDB.Oracle" Const HOST_NAME As String = "localhost" 'データベースのホスト名orIPアドレス Const PORT_NO As String = "1521" 'データベースのポート Const SERVICE_NAME As String = "orcl" 'サービス名 Const USER_ID As String = "user" 'ユーザID Const PASSWORD As String = "password" 'パスワード 'コネクション設定 con.ConnectionString = "Provider=" & PROVIDER _ & ";Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)" _ & "(HOST=" & HOST_NAME & ")" _ & "(PORT=" & PORT_NO & "))" _ & "(CONNECT_DATA=" _ & "(SERVICE_NAME=" & SERVICE_NAME & ")))" _ & ";User ID=" & USER_ID _ & ";PASSWORD=" & PASSWORD 'DB接続 con.Open
SQL発行
Select
Dim con As New ADODB.Connection Dim rec As New ADODB.Recordset Dim sql As String Dim fld As Variant '接続先情報 Const DSN As String = "PostgreSQL" 'データソース名 'コネクション設定 con.ConnectionString = "DSN=" & DSN & ";" 'ODBCのデータソース名を入力 'DB接続 con.Open con.CursorLocation = adUseClient 'クライアントカーソル設定 con.CommandTimeout = 60 'タイムアウト時間 'SQL sql = "select " sql = sql & " * " sql = sql & "from " sql = sql & " member; " 'レコードセット取得 rec.Open sql, con, adOpenStatic, adLockReadOnly '一行ずつ処理 Do Until rec.EOF '値を取得(項目を指定) Debug.Print rec("name") '値を取得(全項目) For Each fld In rec.Fields Debug.Print fld.Value Next rec.MoveNext '次の行へ移動 'rs.MovePrevious '前の行へ移動 'rs.MoveFirst '最初の行へ移動 'rs.MoveLast '最後の行へ移動 Loop '項目名取得 Debug.Print rec.Fields.Item(0).Name '項目名取得(全部) For Each fld In rec.Fields Debug.Print fld.Name Next '結果をセルに張り付け rec.MoveFirst '先頭の行へ戻しておく Range("A1").CopyFromRecordset rec 'クローズ rec.Close: Set rec = Nothing con.Close: Set con = Nothing
Insert / Delete
Dim con As New ADODB.connection Dim sql As String '接続先情報 Const DSN As String = "PostgreSQL" 'データソース名 'コネクション設定 con.ConnectionString = "DSN=" & DSN & ";" 'ODBCのデータソース名を入力 'DB接続 con.Open con.CursorLocation = adUseClient 'クライアントカーソル設定 con.CommandTimeout = 60 'タイムアウト時間 On Error GoTo Err '■Delete sql = "DELETE " sql = sql & "FROM " sql = sql & " member " sql = sql & "WHERE " sql = sql & " id = '1' " 'トランザクション開始 con.BeginTrans 'レコード処理 con.Execute sql 'トランザクション終了 con.CommitTrans '■Insert sql = "INSERT " sql = sql & "INTO member " sql = sql & "VALUES ('1', 'サンプル', 19) " 'トランザクション開始 con.BeginTrans 'レコード処理 con.Execute sql 'トランザクション終了 con.CommitTrans Finally: If con.State <> adStateClosed Then con.Close: Set con = Nothing End If Exit Sub Err: MsgBox "エラー番号：" & Err.Number & vbCrLf & "エラー内容：" & Err.Description, vbOKOnly + vbCritical 'ロールバック con.RollbackTrans Resume Finally
パラメータを使用
| プロバイダ | プレースホルダー |
|---|---|
| ODBC | '?' |
| OLEDB | '?' |
| SQLClient | '@' + 名前 |
| OracleClient | ':' + 名前 |
Dim rec As New ADODB.Recordset Dim cmd As New ADODB.Command Dim sql As String '接続先情報 Const DSN As String = "PostgreSQL" 'データソース名 'コネクション設定 con.ConnectionString = "DSN=" & DSN & ";" 'ODBCのデータソース名を入力 'DB接続 con.Open con.CursorLocation = adUseClient 'クライアントカーソル設定 con.CommandTimeout = 60 'タイムアウト時間 On Error GoTo Err 'SQL sql = "select " sql = sql & " * " sql = sql & "FROM " sql = sql & " member " sql = sql & "WHERE " sql = sql & " id = ? " sql = sql & " and name = ? " cmd.ActiveConnection = con cmd.CommandType = adCmdText cmd.CommandText = sql 'パラメータを設定してレコードセット取得 Set rec = cmd.Execute(Parameters:=Array("1", "サンプル")) Range("A1").CopyFromRecordset rec Set rec = cmd.Execute(Parameters:=Array("2", "鈴木")) Range("A2").CopyFromRecordset rec Finally: Set cmd = Nothing If rec.State <> adStateClosed Then rec.Close: Set rec = Nothing End If If con.State <> adStateClosed Then con.Close: Set con = Nothing End If Exit Sub Err: MsgBox "エラー番号：" & Err.Number & vbCrLf & "エラー内容：" & Err.Description, vbOKOnly + vbCritical Resume Finally
直接更新
Insert
Dim con As New ADODB.connection Dim rec As New ADODB.Recordset '接続先情報 Const DSN As String = "PostgreSQL" 'データソース名 'コネクション設定 con.ConnectionString = "DSN=" & DSN & ";" 'ODBCのデータソース名を入力 On Error GoTo Err 'DB接続 con.Open con.CursorLocation = adUseClient 'クライアントカーソル設定 con.CommandTimeout = 60 'タイムアウト時間 rec.Open "社員マスタ", con, adOpenKeyset, adLockOptimistic, adCmdTableDirect With rec .AddNew !社員コード = "017" !氏名 = "サンプル" !生年月日 = "1999/01/01" !住所 = "大阪府大阪市" !電話番号 = "090-1111-2222" !部署コード = "001" !入社年月日 = "2010/11/22" .Update End With Finally: If con.State <> adStateClosed Then con.Close: Set con = Nothing End If Exit Sub Err: MsgBox "エラー番号：" & Err.Number & vbCrLf & "エラー内容：" & Err.Description, vbOKOnly + vbCritical Resume Finally
