vba を使用した excel と accessの接続は、ADOまたはオートメーションで簡単に実現できます!


 このサイトの vba は、主に一人のユーザーが excel と access とを連携する方法を記述しています。
参考までに、複数の人が access を共有し、高速で安定した処理をする主な内容を次に示しました。
  【1】サーバーDBは、access で作成します。
  【2】クライアントデータは、 excel のシートまたはリストなどに一時保管します。
  【3】vba で記述したマクロは、excel に集中させ、原則としてaccessには作成しません。
  【4】レコードの追加・更新・削除は、1秒未満の瞬間に完了させます。
  【5】DBとの接続は、処理の後すぐに解除します。
     以上、「access が壊れることが無い方法」です。

1.vbaによる有効な2つの「接続」方法は、ado と オートメーションです。


 次のステートメントは、ADOを利用したaccessDBとの接続・解除の手順を実行します。
ADOは、簡単・高速・メモリーのオーバーヘッドが少なく・HDDの使用容量が少ないので、軽快に動作します。
  Dim oCn As ADODB.Connection '(1)

  Set oCn = New ADODB.Connection '(2)
  oCn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
         "Data Source=C:\Program Files\Microsoft Office\Office14\ACCWIZ\Imp_Accdb.accdb;" '(3)

  oCn.Close '(4)
  Set oCn = Nothing '(5)

 ※ 詳細は、「接続」記事を参照願います。 上記の(1)から(5)までの説明をしました。


2.vbaを使用して excel から access へデータをエクスポートできます。


 次のマクロは、ADOを使用して、excelの表(テーブル)をaccessのテーブルへエクスポートします。
Sub export_ws_ADO()
''VBEの「ツール」ー「参照設定」で Microsoft ActiveX Data Objects X.XX Library にチェックを入れます。
  Dim oCn As ADODB.Connection
  Dim oRs As ADODB.Recordset '(1)
  Dim intL As Long, intMaxF As Integer '使用する仮変数の宣言です。

  Debug.Print "Start ADO " & Time

  Set oCn = New ADODB.Connection
  Set oRs = New ADODB.Recordset  '(2)
  oCn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
    "Data Source=C:\Program Files\Microsoft Office\Office14\ACCWIZ\Imp_Accdb.accdb;"

  oRs.Open "DELETE FROM Sheet1;", oCn  '(3)−1)
  oRs.Open "SELECT * FROM Sheet1;", oCn, adOpenDynamic, adLockOptimistic '(3)−2)

  intMaxF = Range("A1").CurrentRegion.Columns.Count - 1 '(4)

  intL = 2 '(5)
  Do While (Cells(intL, 1).Value <> "") '(6)
    oRs.AddNew '(7) ★
    For j = 0 To intMaxF
      oRs.Fields(j + 1) = Cells(intL, j + 1).Value ' (8)
    Next j
    oRs.Update '(9) ★
    ntL = intL + 1 '(10)
  Loop
  oRs.Close '(11)
  oCn.Close

  Set oRs = Nothing '(12)
  Set oCn = Nothing

  Debug.Print "END ADO " & Time

End Sub
Closeメソッドに対する注意があります。
このメソッドを使用してRecordSetを閉じる前にConnection オブジェクトを閉じる場合があります。
その場合、その接続に関連するアクティブな Recordset オブジェクトもすべて閉じてしまいます。
この結果、保留中の更新または編集がキャンセルされますので、注意が必要です。
最初に RecordSet オブジェクトを Close し、保留中の更新または編集を完了しなければなりません。
※ 詳細は、「エクスポート」記事を参照願います。 上記の(1)から(12)までの説明をしました。


3.vba使用し、 excel と access とを連携させ、データをインポート・エクスポートできます。


Sub import_ws_ADO()
''VBEの「ツール」ー「参照設定」で Microsoft ActiveX Data Objects X.XX Library にチェックを入れます。
  Dim oCn As ADODB.Connection
  Dim oRs As ADODB.Recordset 

  Debug.Print "Start ADO " & Time

  Set oCn = New ADODB.Connection
  Set oRs = New ADODB.Recordset 
  oCn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
    "Data Source=C:\Program Files\Microsoft Office\Office14\ACCWIZ\Imp_Accdb.accdb;"

  oRs.Open "SELECT * FROM Sheet1;", oCn, adOpenStatic, adLockReadOnly 

  WorkSheets("Sheet1").Activate
  Cells(1, 1).Value = "Data1"
  Cells(1, 2).Value = "Data2"

  Range("A2").CopyFromRecordset oRs '(1) テーブルのデータをまとめてコピーできます。

  Set oRs = Nothing 
  Set oCn = Nothing

  Debug.Print "END ADO " & Time

End Sub
※ 詳細は、「連携」記事を参照願います。 上記の(1)の説明をしました。


4.vbaで簡単に使えるADO(ActiveX Data Objects)は、高速・小容量メモリのため軽快に動作します。


(1) まず第1に、ADOで接続するために Dim文でProgIDが「ADODB.Connection」であるオブジェクト変数を宣言します。
''VBEの「ツール」ー「参照設定」で Microsoft ActiveX Data Objects X.XX Library にチェックを入れます。
※ 詳細は、「接続」記事を参照願います。

(2) 次に、レコードセットに対して Dim文で ADODB.Recordset オブジェクト変数を宣言します。
※ 詳細は、「エクスポート」、「連携」記事を参照願います。

(3) ADOの接続は、Set文でオブジェクトの参照を代入した変数に対して行われます。
※ 詳細は、「エクスポート」、「連携」記事を参照願います。

(4) カーソル(Cursor)は、一連のデータに順にアクセスする際の検索条件および「現在位置」を保持するデータベースの要素です。
※ カーソル(データベース) Wikipediaより引用

※ カーソルの詳細は、「ado」記事を参照願います。
  次の3つを詳細に説明しました。
    1) カレントレコード位置の移動性
    2) データの更新可能性
    3) 他のユーザーが行ったデータベースの変更結果の可視性


5.excelとaccessで vba の文法の違いは、ありません。


(1) excel で学んだ vba の知識は、 そのまま access で役立ちます。
同じ vba で excel と access を操作できます。
excel で vba は、マクロを作成するプログラミング言語です。
access でも vba は、excel と同様のマクロを作成するプログラミング言語です。
『Visual Basic for Applications(ビジュアルベーシック・フォー・アプリケーションズ、VBA)は、マイクロソフト社製のMicrosoft Officeシリーズに搭載されているプログラミング言語である。』
(※ vba Wikipediaより引用)

(2) Excel 2010は、Excel 4 マクロを作成、編集、実行できます。
vba が発表される前、Excelには Excel 4 マクロと呼ばれるマクロ機能がありました。
このマクロのファイルは、拡張子を XLM としていたので、Excel 4 マクロをXLMと呼んでいました。
XLMは、関数を中心とした「関数型」言語で記述されます。
例えば、印刷は、次のPRINT関数を使用します。
=PRINT(「印刷範囲」「,先頭ページ」「,終了ページ」「,部数」「,画像」「,プレビュー」「,メモ印刷」「,カラー印刷」「,給紙方法」「,品質」「,解像度」「,印刷対象」)
※ 詳細は、「違い」記事を参照願います。 


ADOとオートメーションのvba を使用した excel と access の接続を活用しましょう!


vba excel access 簡単です! Homeへ


×

この広告は1年以上新しい記事の投稿がないブログに表示されております。