もくじ

taka






最新記事 by taka (全て見る)
- 別のBookのマクロを実行「Runメソッド」 - 2019年2月7日
- VBA 列挙型変数を使ってみる「列挙型変数Enum」 - 2019年2月4日
- VBA ダブルクォーテーションを文字列として反映する方法 - 2019年1月31日
- VBAでGooglemapを使わずに地図を作成(Yahoo!API) - 2019年1月30日
- Excel関数 【STEXT関数/ISNONTEXT関数】文字列かどうかを調べる - 2019年1月29日
どうもtakaです(^^♪
今回はAccessとExcelの連携という事で少しVBAを始めたばかりの方には少し難易度が高いかもしれません(;^ω^)
ですが、この記事のよく読んでコードをコピーしていただければとりあえずは動くかと思います(;^ω^)
そもそもAccessとは?Excelとの違い
AccessはMicrosoftOfficeシリーズのデータベースの専用ソフトです。データベースソフトなので顧客管理などを得意とするソフトです。
なぜそんなソフトがあるというのにAccessがマイナーなソフトかというと、、、
- なれないと構造が分かりにくい。レポートやクエリといった機能やフォームなども作らなければいけない。
- そもそも小規模名簿程度ならExcelで足りてしまう。
- Excelのほうが何をやるにしても敷居が低くとっつきやすい。
そう、全部Excelのせいなのです(※これは私の独断と偏見です。)
Excelが便利すぎてAccessの影が薄くなってしまうのです( *´艸`)
そもそもDB(データベース)には専門的な知識が少なからず必要になってきます。そうなると使い慣れているExcelで済ましてしまう用になってきてしまうのです。
ですがExcelで管理ができなくなり、Accessで作らないといけなくなった場合、知識を持った方しかDB(データベース)を使えない・・・となりますよね。
確かに使い慣れていないAccessを操作するには結構ハードルが高いかもしれません(;^ω^)
そこで【AccessとExcelの連携】です!
AccessはDBに特化している分、計算やグラフ作成などが苦手です。
そしてExcelは表でのデータ管理はできますが関連したデータをつなぎ合わせて管理できる【リレーショナル データベース】のような使い方が苦手です。
お互いできない訳じゃありません。ただ苦手なだけなんです(笑)
ExcelとAccessを連携させることでお互いを補完しあう事で【計算が得意でDB管理もできるシステムが作成することができるのです】
Excel上にAccessのデータを取り込みExcelで印刷や統計などを受け持つ。
データ自体はAccessの中にあるのでもし誤操作でExcel上で削除してしまっても、Accessにデータが残っていれば取り込み直せば元通りになります。
Accessを【倉庫】として扱う事で保存できるデータの件数も格段に上がります。
(Excelでは1048576行までですがAccessの場合はデータサイズ2GBまで保存可能です。)
AccessとExcelを連携させる意味とは?
私はもともとはExcelでDBシステムを作成していたのですが、データ量が増えていくにつれExcelの挙動が重くなってきたり、応答なしなどが増えてきて不安定な状態が続くようになり。大切なデータを扱う上で不安になるようになりました・・・
ですが、Excelで使用していた印刷フォームなどそのまま使いたい事や、会社自体も本格的なDB(データベース)システムを用意する予算もない。ならExcelとAccessを連携させてデータをAccessに保存しておいてExcel上で処理ができないか?ということで【AccessとExcelの連携】にいきつきました(^O^)
Excel VBA 初心者の方には若干難易度が高いかもしれません、ですがこの記事を見ていただいて【AccessとExcelを連携する意味】や実際にExcelからAccessに接続してExcelにAccessのデータを抽出してみて動かしてみることで何となくイメージをつかんでいただけたらと思います(^_-)-☆
そもそも【AccessとExcelを連携する意味】は何か?(?_?)
それはExcelの限界にあります(・□・;)
Excelは「表計算ソフト」という特性上、データベースに近い構成になっているため中小企業ではDB(データベース)として扱われることも多々あるのではないでしょうか?
ですがExcelは所詮は【表演算ソフト】です。【データベースソフト】ではありません(;^ω^)
もちろんExcelでデータ保管は可能です。500人、1000人程度の管理でしたらExcelだけでも全く問題ありません。ですが10000人などの規模になってくるとExcelでは少々荷が重い状態になります。
そもそもソフトとしての使い方・・・【役割が違う】わけですからしかたありません、そこで登場するのがExcelと同じOfficeソフトである【Access】です
少しばかりマイナーなソフトなので聞いたことがないような方もいるかもしれません(。-∀-)
AccessとExcelの連携の手順
では実際にExcelからAccessデータベースに接続してみます。下記の手順を沿って行きます。
- Accessデータベースを作成しておく
- DBなのでAccessのデータベースにパスワードをおかけください。Access2013の場合ですがファイル→クライアントの設定→既定の暗号化方法を使用するを選択してください。
- ExcelのVBEを開き【ツール】→【参照設定】→MicrosoftActiveX Data Objects 6.1 Lidraryにチェックを入れてください。
- ThisWorkbookを選択してPrivate Sub Workbook_Open()




※このコードはbookを開いたときにAccessのデータを取り込むコードです。別の用途にも応用が利くのでうまく使ってください。そしてデータベースなので慎重に実践してください。
ExcelからAccessデータベースへ接続させるサンプルコード
実際にExcelからAccessデータベースに接続するコードをご紹介します。今回のコードはExcelBookが開かれたときにAccessに接続することを前提に作成されています。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | Option Explicit Dim con As New ADODB.Connection Dim rs As New ADODB.Recordset Private Sub Workbook_Open() 'データベース(DB)接続 Dim myWBPath As String Dim constr As String, pswd As String, pas As String Dim i As Long myWBPath = ActiveWorkbook.Path '←Excelファイルがあるパスを取得 pswd = "パスワード" '←Accessファイルををパスワード保護している場合は必要 pas = myWBPath & "\Access連携.accdb" '←Excelファイルが接続先のAccessファイルとフォルダの同一階層にあると仮定 '----------データベースに接続------------- 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 '-------------------------------------- End Sub |
Accessにデータを登録するのは下記で紹介しています。
コメントを残す