VBからmdb, Mysql, Excel, Textへアクセスする方法

生産スケジューラー

VBからmdb, Mysql, Excel, Textへアクセスする方法


VBからJet Database Engineにアクセス

参照設定から「Microsoft ActiveX Data Objects 2.1 Library」 を追加。

Dim conn As New ADODB.Connection
Dim rs As New ADODB.Recordset

conn.Open _
    "Provider = Microsoft.Jet.OLEDB.4.0; " & _
"Data Source = C:\Users\Toshiba\Desktop\ERP\VB\LinkWithERP.mdb"

'DELETE
Dim query1 As String
query1 = "DELETE * FROM ORDER_SOURCE"
rs.Open query1, cn, adOpenKeyset, adLockOptimistic

'SELECT
Dim query2 As String
query2 = "SELECT * FROM ORDER_SOURCE"
rs.Open query2, cn, adOpenKeyset, adLockOptimistic

Do Until rs.EOF
    X1 = rs!Order_Code
    X2 = rs!Order_Item
    X3 = rs!Order_Qty

    rs.MoveNext
Loop

ADOやODBCはJet Database Engine(MDB)を利用するためのミドルウェアです。

MS-Access 形式の MDB ファイルの実体は Jet Database Engine(Microsoft社が開発したリレーショナルデータベースエンジン)ファイル、つまり単なるデータ格納用のコンテナであって、GUI を提供する Microsoft Access アプリケーションとは、独立した別個のファイルです。

つまりMDB ファイルは、MS-Access がインストールされていない環境下でも使用することは可能であるということを意味します。

MS-AccessがインストールされていないPCでExcel VBAでADOを使ってテーブル操作したり、MSのサイトからRuntimeをダウンロードして開くことも可能、しかもライセンスの制限に抵触しません。

Runtimeとは「アプリケーションの開発・実行の両方の機能を備えたソフトウェアから、開発の機能を省き、実効の機能のみを取り出したプログラム」のことです。つまり「実行環境 without 開発環境」ですな。

  • http://www.microsoft.com/ja-jp/download/details.aspx?id=4438

メモ帳はテキストファイルを編集するためのユーティリティだけれど、別にメモ帳だけがテキストファイルを編集できる唯一のアプリケーションというわけではない、というのと全く同じ理屈です。

システムからDB(RDB)への接続はADO接続かODBC接続かのどちらかがほとんどですが両者の違いは

  • ADOはOLEDBをActiveXコントロールとして操作するためのインターフェース
  • ODBCはOLEDBのうちRDBを提供するインターフェース

OLEDBの中にODBCプロバイダがあってRDBへ接続してくれるが、開発者はADOのインターフェースでOLEDBにアクセスできる。

Cikarangまで「開発者⇒ODBC⇒OLE(RDB・Excelなど)」という下道で行くと3時間はかかるが、「開発者⇒ADO⇒OLE(RDB・Excelなど)」というTol(高速道路)に乗ると1時間半で着くのと同じですw。

ちなみに接続オブジェクトとレコードセットオブジェクトの生成方法だがどれでもいいと思います。

CreateObject関数を使って新規オブジェクト変数を生成し、Setでオブジェクトへの参照をオブジェクト変数に代入します。

Dim conn As Variant
Dim rs As Variant
Set conn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

Newを使ったオブジェクト参照の代入は、SetでNewを使って新しくオブジェクトを作成し、オブジェクト変数にオブジェクトの参照を代入します。

Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset

VBからMySQLにアクセス

PCがWindows 64bit版でも開発環境であるVB6は32bitなのでMySQL ODBC Connectorも32bit版を使用します。

ADODB接続の場合は関係ないですが、DSN接続をする場合に32bit版のODBCドライバを64bit版Winにインストールすると、コントロールパネルからODBC設定画面を起動してもインストールしたはずのMySQL5.1ドライバが表示されずあせります。よってコマンドプロンプトから以下のコマンドで32bit版のODBCデータソースアドミニストレーター画面を表示させDSN設定を行います。

  • %windir%\SysWOW64\odbcad32.exe

DSN接続の場合

'Mysql5.1ドライバ用DSN用接続オブジェクト生成
Dim conn As New ADODB.Connection
Dim rs As New ADODB.Recordset

Dim dsn As String
dsn = "dsn=xxx;uid=yyy;pwd=zzz"
conn.Open dsn
conn.CursorLocation = 3

ADODB接続の場合

'Mysql5.1ドライバ用ADO用接続オブジェクト生成
Dim conn As New ADODB.Connection
Dim rs As ADODB.Recordset

Dim strConn, xServer, xDatabase, xUID, xPass As String
If conn.State = 0 Then
    xServer = "localhost"
    xDatabase = "xxx"
    xUID = "yyy"
    xPass = "zzz"

    strConn = "DRIVER={MySQL ODBC 5.1 Driver};SERVER=" & xServer & _
               ";PORT=3306" & _
               ";DATABASE=" & xDatabase & _
               ";USER=" & xUID & _
               ";PASSWORD=" & xPass & _
               ";OPTION=3;"
    conn.Open strConn
End If

'DELETE
Dim query1 As String
query1 = "DELETE * FROM ORDER_SOURCE"
rs.Open query1, strConn

'SELECT
Dim query2 As String
query2 = "SELECT * FROM ORDER_SOURCE"
rs.Open query2, strConn

Do Until rs.EOF
    X1 = rs!Order_Code
    X2 = rs!Order_Item
    X3 = rs!Order_Qty

    rs.MoveNext
Loop

VBからExcelにアクセス

参照設定からMicrosoft Excel 12.0 Object Libraryを追加。

'Excel関連オブジェクト
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet

'ファイル名(フルパス)
Dim strFilename As String
strFilename = "C:\SO\Delivery Schedule.xls"

'シート名
Dim strSheetName As String
strSheetName = "Delivery Schedule"

'Application生成
Set xlApp = CreateObject("Excel.Application")

'EXCELを開く
'xlApp.Workbooks.Open FileName:=strFilename, UpdateLinks:=0

'Workbookオブジェクト生成
'Set xlBook = xlApp.Workbooks(Dir(strFilename))
Set xlBook = xlApp.Workbooks.Open(strFilename)

'Worksheetオブジェクト生成
Set xlSheet = xlBook.Worksheets(strSheetName)

'Worksheetデータの取得
Dim cnt As Integer
cnt = 1

Do
    'ORDCOD = Worksheets("Delivery Schedule").Cells(cnt, 1).Textでも取得可
    ORDCOD = xlSheet.Cells(cnt, 1).Text
    ORDITM = xlSheet.Cells(cnt, 2).Text
    ORDQTY = xlSheet.Cells(cnt, 3).Text

    cnt = cnt + 1
Loop While ORDCOD <> ""

ちなみにブックオブジェクト生成の際のファイル名(フルパス)を変数から代入する場合、拡張子を削除してあげないと何故か取得できません。ただし上例のように固定パスを文字列で代入する場合は問題ないです。

InStrRevでピリオドの位置を取得し拡張子を除外します。

strFilename = CurDir & "\data\" & strFilename
'拡張子を削除する(これをやらないとエラーになる)
strFilename = Left(strFilename, InStrRev(strFilename, ".") - 1)

Set xlBook = xlApp.Workbooks.Open(strFilename)

ワークシート名の取得と追加は以下のとおり。

'ワークシート名の取得
j = 0
For Each ws In xlBook.Worksheets
    sheetArry(j) = ws.Name
    j = j + 1
Next

'ワークシート6の追加
Set xlSheet = xlBook.Worksheets.Add  'ワークシートを追加
xlSheet.Name = "New sheet"

VBからテキストファイル(CSV)にアクセス

参照設定からMicrosoft Scripting Runtimeを追加。FileSystemObjectはWindowsでファイルシステムを簡単に扱えるようにするためのコンポーネントです。

'DB接続オブジェクトとレコードセットオブジェクト生成
Dim conn As New ADODB.Connection
Dim rs As New ADODB.Recordset

'CSVファイルを扱うためのコンポーネントオブジェクト生成
Dim objFSO As New Scripting.FileSystemObject

'DBに接続
conn.Open _
    "Provider = Microsoft.Jet.OLEDB.4.0; " & _
    "Data Source = " & CurDir & "\SO.mdb"

'SELECTでレコードセット生成(空)
query1 = "SELECT * FROM ORDER_SOURCE"
rs.Open query1, conn, adOpenStatic, adLockOptimistic

'CSVファイルのデータをオブジェクトに読み込む
Set objFile = objFSO.OpenTextFile("C:\APS_ERP\VB\" & CSVNAME)

'1行ずつ読み込みレコードセットに格納
cnt = 0
Do Until objFile.AtEndOfStream
    strMastflow = objFile.ReadLine
    arrMastflow = Split(strMastflow, ",")

    rs.AddNew
    rs("Order_Code") = arrMastflow(0)
    rs("Order_Item") = arrMastflow(1)
    rs("Order_Qty") = arrMastflow(2)
    rs.Update

    cnt = cnt + 1
Loop

OLE DB関連用語

DBアクセス部分はコピペ中心でプログラムを書いていますが、ADOはいいとしてもテキストファイルやExcelを操作する必要が出てきてそろそろ限界を感じてきたのでここでちょっとおさらい。DBアクセスするまでのミドルウェアやコンポーネントの関係を明確にする画期的例えを発見。DBアクセスはShipperからConsigneeに荷物を送る過程に似ていますな。

oledb

開発アプリケーションプログラム(Shipper)からRDBやExcel, Texfile(Consignee)にアクセスするにはOLE DBデータプロバイダーという仲介人(Forwarder)に手続きをお願いするのだが、直接OLE DBとやりとりするのはややこしい書類手続きなど煩わしいのでADO(カーゴ業者)にやってもらう。

  • OLE DB(Object Linking and Embedding DataBase)
    Microsoft社によって開発された、データベースの種類によらず統一的な手法でデータベースにアクセスするためのプログラミングインターフェース。
    つまりDB(Consignee)<< OLE DBはCOMコンポーネント(プログラム部品)の集合体として実現され、どんなプログラミング言語からでも利用することができるようになっている。OLE DB は概念的に「コンシューマ (consumer)」と「プロバイダ(provider)」に分けられる。コンシューマとはデータにアクセスする必要のあるアプリケーションであり、プロバイダはインタフェースを実装したソフトウェアコンポーネントで、コンシューマにデータを提供する。
  • COMインターフェイス
    COM (Component Object Model)で内部のデータや処理内容を公開する仕組みで、プラグイン(ActiveX DLLファイル)やオートメーションクライアント(Standard EXEファイル)からアプリケーションが公開している機能にアクセスしプラグインを開発・公開できるようになっている。
  • ADO(ActiveX Data Object)とはOLE DBプロバイダを介してデータベースにアクセスするためのコンポーネント。OLE DBをActiveXコントロールの形で使えるようにしたプログラミングインターフェース。ADOを利用してVBからテーブルレコードを直接操作できる。
  • ActiveXコントロールは従来OLEコントロールと呼ばれていた技術にインターネットに対応するための拡張を施したもの。ActiveXコントロールはネットワークを通じてWebサーバからダウンロードされ、同社のWebブラウザであるInternet Explorerに機能を追加する形で使用される。





おすすめ記事一覧

1

よく会計の世界では「利益と減価償却でキャッシュを作る」と言われ、これは企業のキャッシュの源泉が利益と減価償却の二種類あるという意味なのですが、減価償却費はキャッシュの流出のない費用であり、P/L上の当期利益額よりも実際には減価償却費分だけキャッシュは多く残っているという数字遊びをしているだけで、物理的にキャッシュが生み出されるわけではありません。

2

毎月の生産でいくらコストがかかったかを計算する原価計算(実際原価)業務は、インドネシアではシステム化されている事例は少なく、細かく計算すれば時間がかかり、どんぶり勘定だと見たい情報が見られず、コロナ禍の影響で時間に余裕が出来た今は収益改善という観点から原価計算のやり方を見直す絶好の機会です。

3

ジャカルタでは、2019年4月にMRT地下鉄の第1期区間(総延長15.7km)が正式開通しましたが、コロナ禍の中にあった今年6月に、現在の始発駅であるPlaza Indonesia前のBendaran HI駅から、北ジャカルタのKotaまでの6.3km、西ジャカルタのアンチョールの車両基地までの5.2kmの総延長11.5km(11駅)を第2期区間として工事が開始されました。

4

2014年に誕生したジョコウィ政権は、事あるごとに中国寄りと言われ、2015年に中国と日本が受注合戦を繰り広げ日本の新幹線方式での導入が確実視されていたジャカルタ~バンドゥン高速鉄道案件では、インドネシア政府が手のひら返し中国案に鞍替えし、日本を袖にしたのは記憶に新しいところですが、南シナ海の南方にあるナトゥナ諸島周辺の排他的経済水域(EEZ)は、中国が主権を主張する「九段線」と呼ばれる境界と重複しており、中国漁船が公船を伴って活動する違法漁業問題で中国と対立しています。

大統領選挙で考えたギャップにハマるということ 5

ギャップにキュンとするというのは人間の本能みたいなもので、ジョコウィの私利私欲のない素朴なおじさん像と、その実強力なリーダーシップを発揮する実務派という内面が、一見普通の人だが実はスゴイというギャップ好きのインドネシア人に大ウケして、大衆は一種の集団催眠状態にあるようです。

情報の質のレベル 6

見える化された結果を共有化することで問題点が共通認識されますが、共有化が進むことで情報の持つ希少価値が薄れて困る人間がいる場合、有益な情報を独占することでポジションを高めようという政治力が働きます。

7

日本人がインドネシアに来ると、インドネシア人ののんびり加減にイライラするというのは昔からよく聞く話で、インドネシア在住日本人にとってのバイブル的小説である深田祐介著「ガルーダ商人」の中でも、インドネシア宗教省の高官が日本人とインドネシア人を自宅に招待する際に、インドネシア人向けの招待状には、遅刻することを前提にパーティ開始時間を三十分早く書いておくという記述があるほどです。

宗教によって異なる「死んだらどうなる」の考え方 8

キリスト教もイスラム教もともにユダヤ教から派生した宗教であり、それぞれイエス・キリスト(本人が神)またはアッラーという唯一無二の神を信じます。

株価操作なんてインドネシア株では当たり前 9

株価は売り注文と買い注文により変動し、大量の売り注文を買う注文がたくさん入れば、他の投資家達は「俺も俺も」と続くことで株価が上がります。

心臓に毛が生えたインドネシア人のずうずうしい転職活動を応援してみた 10

インドネシア人は秘密の話は誰かに暴露しないと精神の安定を保てない人が多いため、内緒の話に情報の希少性は少なく信憑性も低いことが多いので、「ここだけの話」という枕詞付きで聞かされる話は話半分に聞いておいたほうがいいかもしれません。

日系企業のインドネシアでの存在意義 11

今のまま日本の人口減が続けば、内需は縮小の一途をたどるわけで、そうなると日本国内市場だけで生き残るのは難しいと判断する国内企業が、海外市場に活路を見出そうとするのは必然です。

チャンスはあるが勝てる分野を見つけるのが難しい 12

実際にインドネシアに住んでみて、自分で動いて人と話しをして、現地の事情を少しずつ理解していくにつれて、インドネシアで起業することが意外と手強いことに気づき、その難しさの原因は、高い送料と関税であったりローカル企業との競争であったり、就労ビザ(IMTA)や外国人技能開発基金(DPKK)などのランニングコストの高さであったりします。

インドネシアのシステムインテグレーション業界 13

先日JETRO(日本貿易振興機構)さんと、インドネシアの中小企業のIT投資について意見交換させていただく機会をいただいたのですが、そこで「システム投資のコストメリットはどのように説明できるのか」という、システムインテグレーターの存在価値にも関わる重要な問題提起がありました。

肉体と精神と心と魂 14

「Body and Soul」といえば、昨日の内閣改造に伴う人事で内閣府政務官に内定した自民党の今井絵理子参議院員がメンバーだったSPEEDのデビュー曲であり、インドネシアの老舗女性ファッションブランド名でもあります。

ジャカルタのラーメン市場 15

僕がインドネシアに初めて来たのが1997年10月、インドネシア語は分からないし、仕事は辛いし、周囲の人間は理不尽だし、一時期日本に帰りたくて仕方がない時期がありましたが、当時自分をかろうじてインドネシアに繋ぎ止める心の支えとなっていたのが、協栄プリンスビル(今のWisma Keiai)の日本食レストラン「五右衛門」であり、ここでキムチラーメンを食べることが唯一の楽しみと言っても過言ではありませんでした。

ブランド力、技術力、資金力の3要素 16

1998年のジャカルタ暴動後、ルピアが暴落し海外からのドル建て債務を抱えた国内企業が利子の支払いに苦しんでいた頃、僕は外貨が獲得できるインドネシアでの新しいビジネスを探していました。

日本とインドネシアの間でのタイムマシン経営が通じなくなっている件 17

先進国と後進国との間にある流行のタイムラグを利用して、先進国での成功例を後進国で実践するビジネスモデルをタイムマシン経営といいますが、インターネットの普及に伴い情報がフラット化してしまい、モノと情報のタイムラグが限りなく小さくなった今、先駆者である中小零細同業他社が乱立し市場が出来上がったところに、後発の大手が参入し先発零細を駆逐していく、という典型的な負けパターンにはまります。

サリナデパートとマクドナルド 18

本日5月10日を最後にインドネシアのマクドナルド第1号店であるサリナデパート店(Sarinah)が閉店になりますが、ジャカルタのショッピングモールが新しいコンセプトでモダンにリニューアルされ続ける中で、僕がインドネシアに来たばかりの20数年前には、若者の待ち合わせ場所の定番でもあったサリナデパートやブロックMのパサラヤ(Pasaraya)などは完全に時代に取り残されてしまいました。

不景気の歴史 19

僕がインドネシアに来てからこれまで何度か経済不況を見てきましたが、今回の新型コロナウィルスの感染拡大により、間違いなく景気後退しますので、数年後にはこれがコロナショックとかコロナ不況とか呼ばれるようになるのかもしれません。

日本のバブル経済崩壊後とインドネシアの通貨危機後 20

自分が大学に入学したのがバブル経済末期の1991年、土地も株価もMAX爆上げして、三菱地所がアメリカの象徴であるロックフェラーセンタービルを買収し、ジュリアナ東京でワンレンボディコン(登美丘高校ダンス部のバブリーダンスみたいなやつ)のお姉さん達が扇子振って踊っている時期でした。

内需と外需の自国経済に及ぼす影響 21

公共事業投資を行っても、お金が企業内や個人の貯蓄に滞留してしまい国内消費が増えないのが日本の状況であり、国内消費は増えても消費材の輸入品比率が高く、国内資産が海外に流出しているのがインドネシアの状況です。

2019年の総選挙を前にインドネシア政治史のおさらい 22

来年の大統領選挙(Pemilu Pilpres Pileg Indonesia 2019)に向けての選挙運動(Kampanye)を解禁するにあたり、投票用紙に印字される順番はジョコウィ現職大統領・マフル副大統領候補組が1番、プラボウォ大統領候補・サンディアガウノ副大統領候補組が2番と決まりました。

コーヒーをもっと楽しくもっと美味しく 23

インドネシアは北回帰線と南回帰線をはさむコーヒーベルトに位置するコーヒー栽培に適した国で、1602年の東インド会社の進出を契機にオランダの植民地支配が300年以上続き、その間アラビカ種のコーヒーが持ち込まれ、気候のいい高原地帯で栽培が開始されました。

インドネシア人の悪魔祓い 24

人間誰しも自分の中に悪魔が潜んでおり、それが何らかのきっかけで表面に出て来るという考え方自体には、背景に宗教が有るか無いかの違いだけで、基本的に理解できる話であり、それを信じるか信じないかは別として、そういう考えがあることを認めることは大切なことだと思います。

-生産スケジューラー

© 2020 バテラハイシステム Powered by STINGER