マイクロソフトアクセスで作成した簡単な画像管理ソフトを、データをクラウドのAzureSQLDatabaseとリンクさせて、クラウドアプリのPowerAppsから利用します。ロカールのMSAccessとクラウドアプリの連携です。
低予算で手軽に業務システムが作成できるMSAccessとクラウドアプリとの連携は、だれもが望んでいることでしょう。
私も、2年前のコロナの始まった年にPowerAppsを知って、簡単にスマフォアプリが作れることを知りました。データベース元は、簡単に接続できる、GoogleSpreadSheetを使っていました。
マイクロソフトのSharePointをデータベースに使えばアクセス、クラウドアプリの連携はできますが、AccessからのSharePointのとの連携は、古いAccess2003などからはその機能はないので連携できません。スマフォアプリも簡単にできるようになり、沢山つくりましたが、満足できません。
やはりこれまでMsAccessでいろいろな自社用業務システムを作ってきため、いつかはAccessと直接連携できないかと思っていました。Access2003で作ったものも多数稼働中です。
私は、最近になって、アクセスのとテーブルだけSQLServerに移行の仕方を知ったばかりです。でもそれをクラウドと接続するのは難しそうです。次にAzureSQLデータベースを知りました。PowerAppsはデータ元をSQLServerが選べるというのは知っていたので、もしかしたら、AzureSQLDatabaseを使えばAccessとPowerAppsが連携できるではと思っていました。
とにかく最新版のAccessを使わずにクラウドのPowerAppsと連携したいと思っていました。
今回の環境
ローカルPC Windows11pro64bit Access2003（未だ現役で使っています。）
結果的にはOSは Windows10pro64bitでもWindows8.1pro32bitでも接続できました。
クラウドデータベース Azure内に SQLDatabaseを 立ち上げています。
仮想マシンを立ち上げてその上にSQLServerをインストールするタイプではありません。SQLDatabaseのみです。OSの管理、SQLServerの管理不要です。
AzureSQLDatabaseの立ち上げ方などは省略します。分かれば結構簡単にできます。
クラウドアプリ マイクロソフトPowerAppsを使います。
AzureのアカウントとPowerAppsのアカウントは同じMicrosoft365アカウントを使います。
手順
●SSMSでの設定
SSMS（SQLServerManagimentStudio）でAzureSQLDatabaseに接続します。
オンプレミスのSQLServerに接続するのと同じ要領で使えます。
SSMSから 新規でテーブルを作成します。
＊手間がないので画像は スクショでなく一部スマフォで撮影しています。
テーブルを新規作成します。
フィールド名、フィールドの種類はご自由に
IDには主キーを設定してください。
IDフィールドのIDEBTITY設定をします。
アクセスでいうオートナンバー型です。
シンプルなテーブルを作成します
ID int オートナンバーで1づつ増えます 主キーを設定します。
画像名 nvarchar(50)
画像URL nvarchar(max) 画像URLが非常に長い場合もあるため アクセスで言うメモ型
説明 nvarchar(max) アクセスで言うメモ型
練習で 始め画像用 image型フィールドを作ってためしましたが、PowerAppsとの連携は良いのですが アクセスとの連携に挫折したため今回はやめます。
テーブルにオブジェクト型は、ファイルが大きくなる理由もあります。
クラウドのAzureSQLデータベース内に画像テストテーブルができました。
テーブル名は、画像テストです。
●アクセス側の設定
ローカルのAccessから空のデータベースを新規に作成して、Azure側のテーブルをリンクします。
ODBCドライバを使ってリンクします。
外部データからODBCデータベースを選びます。
最新のアクセス2016、2019とは画面が古くて申し訳ありませんが、手順は同じです。
dsnファイルがないので新規で作成します。
今回は ODBC Driver18 for SQLServerを使います。
PCの OS や ビット数 アクセスのバージョンで 接続できない場合があるので
いろいろ試行錯誤しました。
PC環境によって 18でだめで 17ならできる その逆もありました。
SQL Server、Native Client11も試しました。
ドライバがPC内にない場合 マイクロソフトからドライバをダウンロード
https://learn.microsoft.com/ja-jp/sql/connect/odbc/windows/release-notes-odbc-sql-server-windows?view=sql-server-ver16
日本語版をダウンロードしてください。
OSによってはいっているドライバが異なります。
設定ファイル dsnを名前を任意で付けて保存します。
ODBC18を選んだので 任意の名前odbc18 を付けました。
サーバーの部分には
AzureSQLDatabaseのサーバー名を入力します。
*********.database.windows.net となります。
*********.database.windows.net,1433 とポート番号もつけました。
*********部分はSQLサーバ立ち上げたときにユーザが付けた一意のサーバー名です。
ポート番号は付けなくてもアクセスできるようです。
SQLServer認証を選びます。他はやったことがありません。
認証がうまくいくと Azure上の自分が作ったデータベースが選べるようになります。
データベースを選ぶところ以外は デフォルト設定ですすみます。
認証がうまくいかないとエラーがでます。
その場合は、パスワードなどの入力を確認したり、初めからやり直しでODBCドライバをいろいろかえて試行錯誤します。
Azure側で許可設定した、接続PCの ipアドレスがにかわる場合があるため
Azureのファイヤーウォール設定画面で、接続PCの ipアドレスを追加して許可する必要がある場合あります。同じ環境で急につながらなくなったらこの場合が多いです。
masterは初めからあるデータベースなので選びません。ご自身が作ったものを選んでください。
デフォルト設定で 進みます
このポップアップがでれば接続成功です。
これが表示されると嬉しいです。
ログイン画面がでて 再度 ログイン情報を入力します。
AzureDatabase内にある全テーブルが表示されます。
今回は dbo画像テストを選択します。
SQLServerではテーブル名の前に強制的に【dbo_】が付きます。
右下のパスワード保存はチェックを入れておいてください。
でないと アクセスで接続するたびに パスワードを求められます。
アクセスから クラウドのAzure SQL Databaseサーバの中の データベースの中の dbo_画像テストテーブルとリンクがされました。
ここも嬉しい画面ところです。
アクセス側のテーブル名はご自由に変更可能です。
リンクできれば、あとは いつものアクセスを使うように、クエリでもフォームでもレポートでも使えます。
★注意
複数のPCでアクセスを使う場合、ODBCの設定は、各ローカルPCごとに必要となることと
不定で、ローカルPCの グローバルipアドレスが変更されますので
その都度、Azureの管理画面のファイヤーウォール設定で新しいく割り振られたipアドレスの設定が必要です。ファイヤウォールの設定は2クリックくらいで簡単ですので大変でありません。
自分はアクセスのフォームにAzureファイヤーウォールへのリンクボタンを作ってあります。
テーブルにサンプルデータを3件入力しました。
画像URLは WEB上にある画像の https://から始まるフルパスです。
例えば https://myserver.com/images/sample.jpg
今回は、自分のホームページ上の画像ファイル .jpgでなく .png を指定しました。
●PowerApssでの作業
Microsoft365サービスへログインして PowerPlatformサービスの PowerAppsの画面です。
データ元を SQLを選びます。
初めての場合 接続予定のSQLServerの接続情報の初期設定が必要です。
SQLServerAuthntication
直接接続（クラウドサービス）
SQL server name
SQL database name
username
password
を入力します。
SQLServer接続は プレミアムなので 基本的に有料プランサービスでないと接続できません。
私は 容量内ならいくつでもアプリが作成できる per user プランを利用しています。月々2800円
今回作成した 画像テスト テーブルを選択して 接続します。
アニメーションが流れて、自動的に初期設定アプリを作成してくれます。
慣れてれば、自動作成でなく１から作ることもできます。
自動でアプリが完成しました。
初期状態でも、データの閲覧、修正、追加、削除の基本機能はついています。
初期の状態で閲覧のみで、修正、追加、削除できないバージョンができる場合があります。
ここで、さんざんどうしてなのか悩みましたが
条件がそろっていないと、つまりリードオンリーになります。
分かったことは、Azureへのログインアカウントと PowerAppsへのログインアカウントが同じでなければなりません。AzureADで別アカウントを招待許可すればよいのかもしれません。
あと、はじめSSMSで作成したテーブルに主キーが設定していないとできないようです。
今回は違いますが、アクセスのテーブルをSQLServerにインポートする場合、アクセス側で主キーが設定してあっても、SQLServer側では主キーが解除されています。
ご自身で主キーを設定してください。
また、別のSSMAというマイクロソフトのAccessデータベースをSQLServer用にインポートできるツールでは、主キーは設定されました。
マイクロソフト SSMA ダウンロード 今回は特に使いません。参考リンクです。
https://learn.microsoft.com/ja-jp/sql/ssma/access/sql-server-migration-assistant-for-access-accesstosql?view=sql-server-ver16
自動作成された初期状態では、画像URLフィールは、ただのテキストでしか表示されません。
画像は表示されません。
挿入メニューから メディアの画像 コントロールを挿入します。
このとき 挿入する位置が枠内以外の場所に設置すると参照方法がかわってきます。
サンプル画像が各行に複数表示される必要があります。
挿入された画像コントロールの imageプロパティの値（関数）に
ThisItem.画像URLに書き換えます。
書き換えると サンプル画像（グレーの山と太陽）が 見事 画像URLの画像が表示されます。
アクセス側で追加、修正したものが PowerApps側に反映されます。
アクセスデータが スマフォでも 相互に 閲覧、修正、追加、削除できるようになります。
アクセス側のフォームで 画像URLから 画像を表示することもできます。
ここは、アクセスの情報から調べてください。
●応用
在庫管理などの場合、アクセスで管理しているものを、離れた倉庫で在庫数をスマフォから入力、修正すれば、アクセス側に反映されるなどのクラウド対応機能も簡単にできます。
●残念
初めに SQLServerに作成したテーブルに image型 または、varbinary(max)型を選ぶと
PowerAppsでスマフォで画像を撮影してテーブルに登録でき、表示も簡単にできます。
しかし、アクセス側では連携ができません。
テーブルに画像などの大きなファイルを付けるのは良くないため、別に保存する仕組みが必要です。
最新のAccessとDataverseを使えばPowerAppsと添付ファイル形式画像も連携できそうです。
●今後の予定
マイクロソフトのDataverseを使って MSAccessとPowerAppsを連携
マイクロソフトのAzureSQLServerを使って MSAccessとGoogleのAppSheetを連携
コメント