MySQLとAccess間の連携とは？リンクテーブル・クエリとパススルークエリ

この記事でわかること

MySQLとAccess間の連携とは

MySQLとAccessを連携にはいくつかの方法があります。この記事ではリンクテーブル、パススルークエリ、Access VBAによるサンプルコードの作成を提示します。

・リンクテーブルを利用することにより、MySQLのデータをAccessのテーブルのフィルタを使用しながら解析できます。
・パススルークエリを使用することにより、MySQLのデータを素早く処理したり、処理結果が取得できます。
・VBAを使用することにより、実運用のアプリや評価用のプロトタイプを手早く構築するとこがでます。

使用した環境について

本記事で使用している環境です。

MySQLは mysql-installer-community-8.0.24.0.msi を Developer Default モードでインストールして使用しています。

デモデータが必要な場合は Other MySQL Documentation から world database をダウンロードしてインストールしてください。

MySQLには以下の記事用のユーザを作成しています。

CREATE USER 'demo' IDENTIFIED BY 'demo'; GRANT ALL ON world.* TO 'demo';

1 2	CREATE USER 'demo' IDENTIFIED BY 'demo';   GRANT ALL ON world.* TO 'demo';

Access2016 x32 を使用しているため、必要に応じて mysql-connector-odbc-8.0.24-win32.msi を追加インストールしてください。

MySQLとAccessを違うPCに稼働させてる場合、WindowsDefenderファイアウォールへの設定の追加が必要です。受信の規則（TCP）の追加より、TCPとUDPにポート3306のアクセスを許可する設定を追加してください。

AccessからMySQLへはODBCのデータソースを作成しての接続しています。
データソース名は[MYWORLDUNI]です。

リンクテーブルについて

以下の手順でリンクテーブルを作成します

外部データタブをクリックしてください。

・新しいデータソースをクリックしてください。
・他のソースから -> ODBCデータソースを選択してください。
・リンクテーブルを作成してソースデータにリンクするを選択してください。
・データソースの選択画面からコンピュータデータソースをクリックするを選択してください。
・作成済のデータソースを(今回は[MYWORLDUNI])選択して　OKボタンを押下するを選択してください。
・テーブルのリンク画面より、リンクしたいテーブルを選択、パスワードの保存をチェック後OKボタンを押下してください

左側のペインにMySQLにリンクしたテーブルが表示されます。

リンクテーブルに対してフィルタを使って操作する

上記で作成したテーブルを開いてください。

ホームタブの[並べ替えとフィルター]より

・昇順、降順をクリックして、表示順の並べ替えを実行できます。
・選択や詳細設定をクリックして、検索条件の指定が可能です。テーブル単体ですが、データの解析を試行錯誤しながら実行できます。
・上記２項目は、テーブルのカラム名を右クリックすることにより、実行することもできます。

クエリとパススルークエリについて

クエリとは、Accessに存在するテーブルや他のクエリのデータを操作して、抽出や集計を実行します。

リンクテーブルもAccessに存在するため操作の対象となります。クエリはあくまでAccessでの処理になる為、リンクテーブルの全データがAccess側で処理されることになります。つまり全データがネットワーク経由で取り込まれ処理されるとこになります。

それに対して、パススルークエリは[SQLビュー]に記述されたSQLがMySQL側で実行され、その結果がパススルークエリの結果として取得できます。

処理の対象となるデータの個数が多くなるほど、Accessに取込んだ後に処理を実行するクエリとMySQLで処理をした結果のみを取得するパススルークエリの速度の違いが出ます。

クエリを作成する

作成タブの[クエリデザイン]をクリックします。

・テーブルの表示画面から、city、country を[追加ボタン]押下で追加後、[閉じる]ボタンを押下してください。
・cityテーブルの[ID]、[Name]、[CountryCode]を、countryテーブルの[Name]を下部のフィールド欄にドラッグ&ドロップで追加します。
・クエリ名が表示されているタブを右クリックして[SQLビュー]を選択してください。以下のSQL文が表示されています。

SELECT city.ID, city.Name, country.Name, city.CountryCode, city.District, city.Population FROM city INNER JOIN country ON city.CountryCode = country.Code;

1	SELECT city.ID, city.Name, country.Name, city.CountryCode, city.District, city.Population FROM city INNER JOIN country ON city.CountryCode = country.Code;

・クエリ名が表示されているタブを右クリックして[データシートビュー]を選択してください。
・SQLの実行結果が表示されます。

パススルークエリを作成する

AccessからMySQLへの接続のためのODBCのデータソースは[MYWORLDUNI]を使用します。

作成タブの[クエリデザイン]をクリックします。

・テーブルの表示画面から[閉じる]ボタンを押下してください。
・[パススルークエリ]を押下してください。
・[SQLビュー]が表示されるので、クエリで確認したSQLを張り付けてください。

SELECT city.ID, city.Name, country.Name, city.CountryCode, city.District, city.Population FROM city INNER JOIN country ON city.CountryCode = country.Code;

1	SELECT city.ID, city.Name, country.Name, city.CountryCode, city.District, city.Population FROM city INNER JOIN country ON city.CountryCode = country.Code;

・実行をクリック後、コンピューターデータソースより[MYWORLDUNI]を選択後OKボタンを押下します。
・データシートにSQLの実行結果が表示されます。

上記の例では、SQLをコピペしていますが、通常はMySQL Workbench等の開発用のツールを使用して開発したSQLを使用します。

VBAでの連携について

Access VBA からODBCのデータソースを使用してMySQLのテーブルに直接アクセスが可能です。

以下にサンプルコードを提示します。データベースタブの[マクロ]から Visual Basic をクリックすると、VBAの編集画面が表示されます。

ツール -> 参照設定より以下を追加します。
・Microsoft ActiveX Data Objects 6.1 Library を追加します。
・Microsoft ActiveX Data Objects Recordset 6.0 Library を追加します。

Sub Access2mysql() Dim adoCon As ADODB.Connection Dim adoRst As New ADODB.Recordset Dim Host As String Dim User As String Dim Pass As String Dim conn As String Dim strSQL As String Host = “MYWORLDUNI” User = “demo” Pass = “demo” conn = “DSN=“ & Host & “;UID=“ & User & “;PWD=“ & Pass strSQL = “SELECT * FROM world.country;” Set adoCon = New ADODB.Connection adoCon.ConnectionString = conn Call adoCon.Open Set adoRst = adoCon.Execute(strSQL, 0) While Not adoRst.EOF Debug.Print Trim(adoRst.Fields(“Code”)) & vbTab & _ Trim(adoRst.Fields(“Name”)) adoRst.MoveNext Wend adoCon.Close Set adoRst = Nothing Set adoCon = Nothing End Sub

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 26 27 28 29 30 31 32 33 34 35 36 37

Sub Access2mysql()       Dim adoCon      As ADODB.Connection     Dim adoRst      As New ADODB.Recordset     Dim Host        As String     Dim User        As String     Dim Pass        As String     Dim conn        As String     Dim strSQL      As String       Host = “MYWORLDUNI”     User = “demo”     Pass = “demo”       conn = “DSN=“ & Host & “;UID=“ & User & “;PWD=“ & Pass     strSQL = “SELECT * FROM world.country;”       Set adoCon = New ADODB.Connection     adoCon.ConnectionString = conn       Call adoCon.Open       Set adoRst = adoCon.Execute(strSQL, 0)       While Not adoRst.EOF         Debug.Print Trim(adoRst.Fields(“Code”)) & vbTab & _                     Trim(adoRst.Fields(“Name”))         adoRst.MoveNext     Wend       adoCon.Close       Set adoRst = Nothing     Set adoCon = Nothing End Sub

MySQLとAccess間で連携してみよう

いかがでしたでしょうか。MySQLとAccess間の連携について説明してきました。

Access単体のシステムから、慣れ親しんだAccessをフロントエンドに、本格的なRDBMSであるMySQLをバックエンドのデータベースとする構成はシステムの発展形として一つの候補にあがると思います。

ぜひご自身で実際に導入して試してみてください。

FEnetを運営しているネプラス株式会社はサービス開始から10年以上
『エンジニアの生涯価値の向上』をミッションに掲げ、
多くのインフラエンジニア・ネットワークエンジニアの就業を支援してきました。

ネプラス株式会社はこんな会社です

秋葉原オフィスにはネプラス株式会社をはじめグループのIT企業が集結！
数多くのエンジニアが集まります。

• インフラ業界に特化

  

  ネットワーク・サーバー・データベース等、ITインフラ業界に特化。Cisco Systemsプレミアパートナーをはじめ各種ベンダーのパートナー企業です。

  業界を知り尽くしているからこそ大手の取引先企業、経験豊富なエンジニアに選ばれています。

• 正社員なのにフリーランスのような働き方

  

  正社員の方でも希望を聞いたうえでプロジェクトをアサインさせていただいており、フリーランスのような働き方が可能。帰社日もありません。

  プロジェクト終了後もすぐに次の案件をご紹介させていただきますのでご安心ください。

• 大手直取引の高額案件

  

  案件のほとんどが大手SIerやエンドユーザーからの直取引のためエンジニアの皆様へに高く還元できています。

  Ciscoをはじめ、Juniper、Azure、Linux、AWS等インフラに特化した常時300件以上の案件があります。

• スキルアップ支援

  

  不要なコストを削減し、その分エンジニアの方へのスキルアップ支援(ネットワーク機器貸出、合格時の受験費用支給など)や給与で還元しています。

  受験費用例）CCNP,CCIE:6-20万円、JNCIS:3-4万円、AWS:1-3万円など

  ※業務に関連する一定の資格のみ。各種条件がありますので詳しくは担当者へにお尋ねください。

• 現給与を保証します！※

  

  前職の給与保証しており、昨年度は100%の方が給与アップを実現。収入面の不安がある方でも安心して入社していただけます。

  ※適用にはインフラエンジニアの業務経験1年以上、等一定の条件がございます。

• インセンティブ制度

  

  ネットワーク機器の販売・レンタル事業等、売上に貢献いただいた方にはインセンティブをお支払いしています。

  取引先企業とエンジニア側、双方にメリットがあり大変好評をいただいています。

• 社会保険・福利厚生

  

  社員の方は、社会保険を完備。健康保険は業界内で最も評価の高い「関東ITソフトウェア健康保険組合」です。

  さらに様々なサービスをお得に利用できるベネフィットステーションにも加入いただきます。

• 東証プライム上場企業グループ

  

  ネプラスは東証プライム上場「株式会社夢真ビーネックスグループ」のグループ企業です。

  安定した経営基盤とグループ間のスムーズな連携でコロナ禍でも安定した雇用を実現させています。

ネプラス株式会社に興味を持った方へ

ネプラス株式会社では、インフラエンジニアを募集しています。

年収をアップしたい！スキルアップしたい！大手の上流案件にチャレンジしたい！
まずは話だけでも聞いてみたい場合もOK。お気軽にご登録ください。

MySQL新着案件New Job

• サーバ構築・運用設計/東京都渋谷区/【WEB面談可/インフラサーバ経験者/20～40代の方活躍中】/在宅勤務

  年収588万～588万円
  東京都渋谷区(渋谷駅)
  • Apache
  • Tomcat
  • MySQL
  • Linux

• 【高額年収】/基盤運用保守/東京都千代田区/【WEB面談可/インフラサーバ経験者/20～40代の方活躍中】/在宅勤務

  年収600万～720万円
  東京都千代田区(溜池山王駅)
  • EMC
  • Linux
  • MySQL
  • Oracle
  • Unix
  • VMware

• 【高額年収】/国内クラウドシステムのサーバ運用保守/東京都新宿区/【WEB面談可/インフラサーバ経験者/20～40代の方活躍中】/在宅勤務

  年収600万～720万円
  東京都新宿区(東新宿駅)
  • MySQL
  • PostgreSQL
  • Oracle
  • Apache

• 国内クラウドシステムのサーバ構築のテスター/東京都新宿区/【WEB面談可/インフラサーバ経験者/20～40代の方活躍中】/在宅勤務

  年収300万～420万円
  東京都新宿区(東新宿駅)
  • MySQL
  • PostgreSQL
  • Oracle
  • Apache

• 【高額年収】/社内情報システムインフラ運用保守/Windows/東京都多摩北部/【WEB面談可】/在宅勤務/20代～30代の方活躍中

  年収600万～720万円
  東京都多摩北部(-駅)
  • A10
  • Alaxala
  • Azure
  • Brocade
  • Cisco
  • F5
  • FortiGate
  • Juniper
  • MySQL
  • NetApp
  • Oracle
  • Paloalto
  • PostgreSQL
  • Windows

• 社内情報システムインフラ構築のテスター/Windows/東京都多摩北部/【WEB面談可】/在宅勤務/20代～30代の方活躍中

  年収300万～420万円
  東京都多摩北部(-駅)
  • A10
  • Alaxala
  • Azure
  • Brocade
  • Cisco
  • F5
  • FortiGate
  • Juniper
  • MySQL
  • NetApp
  • Oracle
  • Paloalto
  • PostgreSQL
  • Windows