VBA　ExcelとAccessの連携パート1【Accessと連携する意味とデータベースへの接続】

1 そもそもAccessとは？Excelとの違い
2 AccessとExcelを連携させる意味とは？
3 AccessとExcelの連携の手順
- 3.1 ExcelからAccessデータベースへ接続させるサンプルコード
- 3.2 共有:

この記事を書いた人
最新の記事

taka

あることがキッカケでVBAを独学で勉強しました、今ではブログを通してVBAでできることを解説しつつ、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に接続することを前提に作成されています。

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

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にデータを登録するのは下記で紹介しています。

E-VBA

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

AccessとExcelの連携パート2実際にプログラムを作ってみる前回はAccessのデータベースを読み込むコードを掲載しました、前回のプログラムを利用して実際に使えるAccess...

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

■Twitterフォロー

Follow @takabou63

■YouTubeチャンネル登録

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

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

カテゴリーで絞る

月で探す

VBA　ExcelとAccessの連携パート1【Accessと連携する意味とデータベースへの接続】

taka

最新記事 by taka (全て見る)

そもそもAccessとは？Excelとの違い

AccessとExcelを連携させる意味とは？