AccessのODBC接続についてお探しですね。

広告

Accessが重い・壊れやすい問題を解決！SQL Serverと連携してパワーアップする方法

業務で使っているMicrosoft Accessが、データが増えてくるとだんだん重くなってきたり、ファイルが壊れて困ったり…そんな経験はありませんか? 実は、Accessの使いやすい画面はそのまま残して、データの保存先だけをSQL ServerやOracleといった強力なデータベースに移すことで、これらの問題を一気に解決できるんです。

今回は、Accessの便利さを活かしながら、システムを安定させてスピードアップする方法をわかりやすく紹介します。

Accessを「画面」として使うってどういうこと?

Accessを「データを表示したり入力したりする画面」として使って、実際のデータ管理はSQL ServerやOracleに任せる――これが「クライアント・サーバー型」という仕組みです。

この方法の一番のメリットは、処理の負担を分散できることと、データが壊れにくくなることです。

従来のAccessだけの使い方(ファイル共有型)では、データを処理するたびにネットワーク経由で大量のデータをパソコンに取り込んでいたので、回線に負担がかかって動作が遅くなっていました。

でも、サーバー側のデータベースと連携すれば、重たい集計処理などはサーバーが引き受けてくれて、必要な結果だけをもらえるようになります。

数万件のデータを扱っていても、サクサク快適に動くようになるんです。

それに、セキュリティと信頼性もグンと上がります。

Accessファイル(.accdbや.mdb)は、ネットワークが一瞬途切れたりパソコンが固まったりすると、ファイル自体が壊れてしまうリスクがあります。

でも、SQL ServerやOracleは、データを守る仕組みがしっかりしているので、壊れる心配がほとんどありません。

さらに、ユーザーごとに細かくアクセス権限を設定できるので、会社の大事なデータを守る面でも安心です。

Accessの使いやすいフォームやレポート機能はそのまま使えて、裏側のエンジンだけを強力なものに交換するイメージですね。

コストを抑えながらシステムを長く使い続けたい、という場合にぴったりの方法です。

接続の準備：ドライバーとDSNの設定をしよう

SQL ServerやOracleにAccessから接続するには、「ODBC(オーディービーシー)」という仕組みを使います。

まず確認したいのが、接続先に合った「ODBCドライバー」がパソコンに入っているかどうかです。

Windowsには古い「SQL Server」ドライバーが最初から入っていますが、最新の機能や速度を活かすなら、Microsoftが提供している「ODBC Driver 17(または18) for SQL Server」などの新しいドライバーを入れるのがおすすめです。

Oracleの場合も、Oracle ClientというソフトをインストールすればODBCドライバーが使えるようになります。

適切なドライバーがないと接続エラーになってしまうので、まずはここからスタートです。

次に、接続情報をパソコンに登録する「DSN(データソースネーム)」という設定をします。

Windowsの管理ツールにある「ODBCデータソースアドミニストレーター」を開いて、接続先のサーバーアドレス、ログイン方法、使いたいデータベース名などを入力します。

このとき、「ユーザーDSN」ではなく「システムDSN」を選ぶのがポイントです。

ユーザーDSNだと設定した人しか使えませんが、システムDSNならパソコンを使う全員が使えるので、管理が楽になります。

設定が終わったら接続テストをして、「テストは無事に完了しました」と出ればOKです。

これでAccessとサーバーをつなぐ準備が整いました。

リンクテーブルとパススルークエリの使い分けがカギ

Accessからサーバーのデータを使う方法には、主に「リンクテーブル」と「パススルークエリ」の2種類があります。

この2つを上手に使い分けることが、快適に動かすコツです。

「リンクテーブル」は、Accessの普通のテーブルと同じ感覚でサーバー上のテーブルを操作できる機能です。

フォームやレポートに直接つなげられて、データの追加・更新・削除もAccess上から簡単にできるので、基本的にはこれを使います。

設定は簡単で、Accessの「外部データ」タブから「新しいデータソース」を選んで、さっき作ったODBCデータソースを指定するだけ。

あとは使いたいテーブルを選べば、まるでローカルにあるテーブルのようにサーバーのデータを扱えるようになります。

一方、複雑な集計や大量データの処理をするときは「パススルークエリ」の出番です。

リンクテーブルを使った普通のクエリだと、Access側(パソコン側)でデータを処理しようとするので、ネットワークに負担がかかることがあります。

でも、パススルークエリはSQL文をそのままサーバーに送って、サーバー側で計算してもらった結果だけを受け取ります。

SQL Serverの強力な処理能力を使えるので、スピードが段違いに速くなることが多いです。

ただし、パススルークエリの結果は基本的に「読み取り専用」なので、データを見るだけの画面や集計レポートに使って、データを編集する画面にはリンクテーブルを使う、という組み合わせがおすすめです。

よくあるトラブルと解決方法

ODBC接続を設定したとき、よくあるトラブルが「テーブルの中身は見えるのに、データの書き込みや更新ができない」というものです。

Accessで「このレコードセットは更新できません」と表示されてしまうパターンですね。

これは多くの場合、SQL ServerやOracle側のテーブルに「主キー(Primary Key)」が設定されていないことが原因です。

Accessが特定の行を更新するには、その行を確実に見分けるための目印(主キー)が必要なんです。

解決するには、データベース側でテーブルに主キーを設定してから、Access側で「リンクテーブルマネージャー」を使ってリンクを更新してください。

これで更新できるようになるはずです。

また、「接続に失敗しました」というエラーが出る場合は、ネットワークやログイン設定を見直しましょう。

特にSQL Server Expressを使っている場合、初期設定では外部からの接続が無効になっていることがよくあります。

「SQL Server構成マネージャー」を開いて、TCP/IPプロトコルを有効にして、サービスを再起動してみてください。

さらに、サーバー側のWindowsファイアウォールがSQL Serverのポート(標準では1433番)を止めていないかも確認が必要です。

これらに問題がなくても、AccessのODBC接続タイムアウト設定が短すぎてエラーになることもあります。

クエリのプロパティで「ODBCタイムアウト」の秒数を長くする(例：60秒から0秒=無制限にする)と、重い処理が途中で止まるのを防げます。

広告