VBA AccessとExcelの連携パート2【実際にプログラムを作ってみる】

ExcelとAccessの連携



The following two tabs change content below.
アバター

taka

あることがキッカケでVBAを独学で勉強しました、今ではブログを通してVBAでできることを解説しつつ、VBAや他の言語の勉強、ブログ運営の勉強をしています(^^♪

どうも、taka(@takabou63)です(^^♪

さて、前回はAccessのデータベースを読み込むコードを掲載しました、前回のプログラムを利用して実際に使えるAccessとExcelを連携するプログラムを作成してみましょう(^^♪

VBAでExcelからAccessにデータを登録するプログラムを作成しよう!

今回はデータを新しく追加するプログラムを作成していきましょう。

まずAccessのデータベースを用意します。一応私が用意したデータベースをUPしておきます。今回はこのデータベースを使用して作成していくのでよろしければ使ってください。

前回のサンプルコードを少しだけ編集したものを使用します。

今回使うAccessデータベースはこちら

ファイル名:Access連携

データの登録フォームを作ってみよう

まずは登録フォームの作成していきます。今回は簡単に作ることが目的なので最低限で。

登録フォームに必要なものはとりあえず

  • TextBox×項目数分
  • 登録のボタン

最低この二つがあれば登録する事が可能になります。逆に言えばこの二つだけあれば登録することが可能になるのです。

 Accessデータベースを読み込むコード

今回はデータを登録するフォームを作成するのでユーザーフォームを起動したときにコードが実行されるイベント【UserForm_Initialize()】にデータベースを読み込むコードを記述します。

Option Explicit
 Dim con As New ADODB.Connection
 Dim rs As New ADODB.Recordset

Private Sub UserForm_Initialize()

Worksheets("date").Select
 '↑一応エクスポート先のシートを指定

 Dim constr As String, pswd As String, pas As String
 pswd = "パスワード" ''↓フルパスで指定。’pas’はパスワードの意味ではありません
 pas = "ここにファイルを保存しているフルパスを指定してください"



 constr = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
 "Data Source=" & pas & ";" & _
 "Jet OLEDB:Database Password=" & pswd & ";"
 
 con.Open ConnectionString:=constr


 ' ↓テーブル名
 rs.Open Source:="会員名簿", ActiveConnection:=con, _
 CursorType:=adOpenKeyset, LockType:=adLockOptimistic
 'エクスポートの基準点
 Range("A2").CopyFromRecordset Data:=rs
End Sub

設定の方法は前回の記事をご覧ください。

 AccessとExcelの連携パート1AccessとExcelを連携する意味AccessとExcelを連携する意味、それはExcelの限界にあります。Excelは表演算ソフトですデータベースではありません...

読み込み結果

このようになっていれば接続成功です。このようにならない場合はどこかが間違っていますので前回の記事やコード、参照設定などが間違っていないか確認をしましょう。

データ登録用のコード

ここからがいよいよ本番、AccessデータベースにExcelからデータを追加してみましょう。上記のフォームにデータを追加するコードをコマンドボタンに割り振りましょう。

Accessにデータを追加する場合は3っつのステップが必要です

  1. 新しいレコードを追加
  2. 新レコードの項目別にデータを設定
  3. テーブルに反映

それをプログラムコードに直すとこうなります。データ登録といってもコード量は少ないです。

Private Sub CommandButton1_Click()

With rs
  
  .AddNew      '①新規レコードに移動
  .Fields("氏名").Value = TextBox1.Value '②フィールドにデータ割り当て
  .Fields("住所").Value = TextBox2.Value '②フィールドにデータ割り当て
  .Update '③テーブルに反映

End With
Range("A2").CopyFromRecordset Date:=rs ’④追加したデータをExcelに反映
 rs.Close
 con.Close
End Sub

登録用コードの解説

まずデータ登録用のコードを解説していきます。

.AddNew      '①新規レコードに移動

AddNewメソッドを使用します、メソッド実行語は新規レコードにカーソルが動くため変数【rs】のレコードセットオブジェクトを指定することにより新規レコードを操作することができるのです。

続いてフィールドの指定です。

  .Fields("氏名").Value = TextBox1.Value '②フィールドにデータ割り当て
  .Fields("住所").Value = TextBox2.Value '②フィールドにデータ割り当て

フィールドを指定する場合はFieldsプロパティを使用します、

Fields(”フィールド名”)

Fieldsプロパティの()の中にフィールド名を指定することによって指定することができます。あとはExcelの要領で

  .Fields("氏名").Value = TextBox1.Value

としてあげれば完了です。

ですがAccessデータベースにExcelから変更をした場合【反映させる】という記述が追加で必要になります

  .Update '③テーブルに反映

これでExcelからAccessへデータを登録することができたはずです。

一通りの処理が終了したときにはデータベースを閉じてあげましょう。

 rs.Close
 con.Close

実行結果

このようにAccessとExcelのデータが同じようになっていたら成功です。

まとめ

Excelをデータベースとして使用していた場合、大量のデータを扱った場合おそらくExcelの限界にぶつかってしまう事でしょう。ですがAccessとの連携が可能になればAccessとExcelの良いところだけを集めたシステムが構築可能になります。Excelだけでの限界を感じた方はAccessとの連携を考えるのも一つの手だと考えます。

今回使用したファイルはUPしておきますのでご参考にお使いください。※本当に登録作業のコードしか書いていないので実用性はありません。

フォロー・チャンネル登録お願いします

VBA以外についてのブログはこちら↓

https://yb-log.com/

■Twitterフォロー

■YouTubeチャンネル登録

ファイルやコードの利用、WEBサイトの利用について

サンプルコードなどは当サイトの免責事項をよくお読みになってからお使いください。

Sponsored Link

 

WordPressでブログを始めるなら

WordPress簡単インストール&安心の安定性

レンタルサーバー Xserver

ExcelとAccessの連携

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です