実践Linux
CentOS5/6.5        CentOS5/6.5 目次へ  TOP(HOME)へ


Excel+ADOでpostgreSQLにアクセス   2014年9月更新





●postgreSQLとのやりとり用ドライバODBCの入手(windows8.1)
postgreSQLとのやりとり用ドライバODBCの入手
PostgreSQLのODBCドライバは以下のURLからダウンロードできる。
http://www.postgresql.org/ftp/odbc/versions/msi/
最新の32ビット版を入手して、インストール。2014/9現在、psqlodbc_09_03_0300-1.zipが最新。
64ビット版だとうまくいかない。

コントロールパネル→システムとセキュリティ→管理ツール→ODBCデータソース(32ビット)→システムDSN→追加→PostgreSQL Unicode
そのまま、保存。

@データベースとの接続と解除(Excelマクロ)
Sub Macro1()
 Dim myCon As ADODB.Connection
 
 Set myCon = New ADODB.Connection
 myCon.Open "Provider=MSDASQL; DSN=PostgreSQL;DATABASE=zaiko;SERVER=192.168.7.1;PORT=5432;UID=zaiko;;SSLmode=disable"
 
 myCon.Close
 Set myCon = Nothing
 
End Sub

補足
 myCon.Open "Provider=MSDASQL; DSN=PostgreSQL;DATABASE=zaiko;SERVER=192.168.7.1;PORT=5432;UID=zaiko;;SSLmode=disable"
 の代わりに次のようにしてもよい。
 With myCon
  .ConnectionString = "Provider=MSDASQL; DSN=PostgreSQL;DATABASE=zaiko;SERVER=192.168.7.1;PORT=5432;UID=zaiko;;SSLmode=disable"
  .Open
 End With

 パスワードを使う場合
 myCon.Open "Provider=MSDASQL; DSN=PostgreSQL; DATABASE=zaiko; SERVER=192.168.7.1; PORT=5432; UID=zaiko; PWD=****; SSLmode=disable"
 次のような書き方もある
 myCon.Open "Driver={PostgreSQL}; server=192.168.7.1; database=zaiko; username=zaiko; password=****;"

Aデータの読み込み(取得型)
Sub Macro1()
 Dim myCon As ADODB.Connection
 Dim myRS As ADODB.Recordset
 
 Set myCon = New ADODB.Connection
 myCon.Open "Provider=MSDASQL; DSN=PostgreSQL;DATABASE=zaiko;SERVER=192.168.7.1;PORT=5432;UID=zaiko;;SSLmode=disable"
 
 Set myRS = New ADODB.Recordset
 
 With myRS
  .ActiveConnection = myCon
  .Source = "SELECT * FROM 在庫01"
  .Open
 End With

 For i = 1 To myRS.Fields.Count    フィールド名を取り込む
 Cells(1, i).Value = myRS.Fields(i - 1).Name
 Next

 Range("A2").CopyFromRecordset myRS  データの書き出し

 myRS.Close
 Set myRS = Nothing
 myCon.Close
 Set myCon = Nothing

End Sub

補足
 With myRS
  .ActiveConnection = myCon
  .Source = "SELECT * FROM 在庫01"
  .Open
 End With
 の代わりに次のようにしてもよい。
 myRS.Open "SELECT * FROM 在庫01", myCon


●RecordSetオブジェクト説明
取得
  myRS.Open [Source], [ActiveConnection], [CursorType], [LockType], [Options]
   Source  SQL文
   ActiveConnection  Connectionオブジェクトを指定
   CursorType  レコードの走査手段を指定(省略可 規定値はadOpenForwardOnly前方専用カソール)
   LockType  同時アクセス時の処理(省略可 規定値はadLockReadOnly読みとり専用)
   Options  引数Sourceの種類を指定(省略可 規定値はadCmdUnknownソースのタイプ不明)
Range("A2").CopyFromRecordset myRS 転記
  転記した時点で、myRSの内容は削除されてしまうので注意(データ枠だけは残っている?)。
myRS.Fields.Count   フィールド数
myRS.Field(n).Name   n番目(0から数える)のフィールド名
myRS.Field(n).Type   n番目(0から数える)のフィールドのタイプ
myRS.RecordCount   レコード数(取得したレコードの行数)
  ただし、myRSをOpenする前に、myRS.CursorLocation = adUseClientを指定しておかなくてはならない。これを指定しておかないとRecordCountは使えないので注意。
myRS.Sort = "メーカー ASC, 仕入先 DESC"   「メーカー」フィールドを昇順、「仕入先」を降順でソート
  これも上と同様、myRSをOpenする前に、myRS.CursorLocation = adUseClientを指定しておかなくてはならない。これを指定しておかないとSortは使えないので注意。
myRS.GetRows
  レコードの内容を2次元配列に格納
    Dim myArray() As Variant
    myRS.CursorLocation = adUseClient
    myRS.Open "…………", myCon
    ReDim myArray(myRS.Fields.Count - 1, myRS.RecordCount - 1)
    myArray = myRS.GetRows  格納
  これは次のように省略できる。
    Dim myArray() As Variant
    myRS.Open "…………", myCon
    myArray = myRS.GetRows  格納 myArray(フィールド位置、レコード位置)のように代入される。
特定のフィールドの値を配列に格納
  myArray = myRS.GetRows(Fields:= "部品名")
  myArray = myRS.GetRows(Fields:= Array("部品名", "仕入先"))
myRS.GetString
  レコードの内容を文字列として返す。
  タブと改行で区切った一連の文字列になる。

Bデータの更新や挿入(実行型)
Sub Macro2()
 Dim myCon As ADODB.Connection
 Dim myCmd As ADODB.Command
 
 Set myCon = New ADODB.Connection
 myCon.Open "Provider=MSDASQL; DSN=PostgreSQL;DATABASE=zaiko;SERVER=192.168.7.1;PORT=5432;UID=zaiko;;SSLmode=disable"
 
 Set myCmd = New ADODB.Command
 
 With myCmd
  .ActiveConnection = myCon
  .CommandText = "UPDATE 在庫01 SET 部品名 = 'test2' WHERE 品番 = '111-2'"
  .Execute
 End With

 Set myCmd = Nothing
 myCon.Close
 Set myCon = Nothing

End Sub

補足
 Dim myCmd As ADODB.Command
 Set myCmd = New ADODB.Command
 With myCmd
  .ActiveConnection = myCon
  .CommandText = "UPDATE 在庫01 SET 部品名 = 'test2' WHERE 品番 = '111-2'"
  .Execute
 End With
 Set myCmd = Nothing
 myCon.Close
 の代わりに次のようにしてもよい。
 myCon.Execute "UPDATE 在庫01 SET 部品名 = 'test2' WHERE 品番 = '111-2'"


サンプルのダウンロードはこちらから



TOP(HOME)へ

目 次
HOME
 全体のシステム構成&目次


RockyLinux

RockyLinux8.4のインストール

旧サーバーからのデータ移行

ネットワークの設定ほか
ダイレクトルールを使ったfirewallの強化
DNS(BIND)サーバー
DHCPサーバー
FTPサーバー
FTPS(FTP over SSL/TLS)
Webサーバー
  アクセス解析ツールAwstats
Mail(Dovecot&Postfix)サーバー
MariaDB(MySQL)サーバー
SSHサーバー
SSHトンネル
SSH+MariaDB+Exel
Sambaサーバー
openVPNサーバー
VNCサーバー(リモートディスクトップ)

systemdとfirewalldについて


CentOS8
CentOS7
CentOS5〜6

SELinux

Cプログラミング


●その他
クラウド Amazon EC2
Raspberry Piで遊ぶ
印刷機関連開発 刷版絵柄面積率測定
数独をExcelで解く


●趣味の世界
相対性理論
量子力学
群論
熱力学・統計力学
解析力学
物理でつかう数学
超弦理論(ノート作成中)