クラウドに構築したデータベースの不具合
クラウドにWindowsServerを立てて、その中にSqlServerを起動させて、VPNでサイト間接続をさせて社内のAccessからデータベースを更新するシステムを構築しました。
もう、稼働して５年以上は経過しているかと思いますが、データベースの繰越処理ができないという不具合が出てくるようになりました。
その不具合の中で、ODBC接続が切れてしまうというようなケースも出てきましたので、
ココがポイント
検証用に自分のPCにSqlServerをインストールしてAccessから接続
できるようにしてみたいと思います。
最新版のSqlServerExpressをインストール
今年は、２０２５年ですが、今のところ最新版のSqlServerExpressのバージョンは、２０２２のようです。
ちなみに、Express版は、無料で使えるデータベースサーバーになります。
Microsoft SQL Server 2022 Express は、機能が豊富な SQL Server の無償エディションです。デスクトップ、Web、小規模サーバーで使用するアプリケーションに関する学習、開発、強化、および ISV によるソフトウェア再配布に最適です。（マイクロソフト：SQL Server 2022 Express）
早速ダウンロードしてインストールして見る事にしました。
ネット検索すると、「カスタム」でインストールしている人が多いので、カスタムを選択しました。
「SQL Serverの新規スタンドアロンインストールを実行するか、既存のインストールに機能を追加」を選択
ライセンス条項がでたので、チェックを入れて次へ進みました。
「Microsoft Updateを使用して更新プログラムを確認する（推奨）」でしたが、検証だけなので、チェック入れずに次へ進みました。
Windowsファイアウォールが警告になっていましたが、自分のパソコン内での接続なので何もしないで次へ進みました。
SQLServer用Azure拡張機能にデフォルトでチェックが入っているので消して次へ進みました。
必要の無さそうなチェックは外して次へ進みました。
次に「インスタンスの構成」が出てきましたが、構築済のインスタンスの構成もデフォルトのままだったので、このまま次へ進みました。
「サーバーの構成」となりますが、何もしないで、次に進みました。
「データベースエンジンの構成」となりますが、自分のパソコンだけでの作業になりますので、「Windows認証モード」のデフォルトのまま次へ進みました。
以上で、インストールが開始されました。
MSSMSを最新バージョンインストール
クラウドサーバーの方のSqlServerを遠隔で操作する為に、MSSMS（Microsoft SQL Server Management Studio）というソフトをインストールしてありましたが、バージョンが１８だったので、最新版にしました。
２０２５年現在は、２０．２が最新バージョンのようです。
何だか、以前のバージョンを消さずに置いてあるようだったので、アンインストールしてからインストールしました。
インストールはすんなりといきましたが、簡単にSQL Serverに接続させてくれませんでした。
一旦閉じて、再接続させると下の様な画面がでてきたので、チェックをいれて「はい」をすると無事に接続できました。
データベースの復元で失敗
なんどやってもデータベースの復元で失敗します。
何かわすれているのでしょうか？
ココに注意
既存のデータベースを上書きする
を忘れていました。
これで無事にデータベースの復元もすることができました。
次は、端末のAccessから接続になります。
Access接続でパススルークエリだけODBC接続失敗
不思議な現象に困りました。
ココに注意
パススルークエリだけODBC接続失敗します！
SQLserverにODBC接続したテーブルは普通に開きます。
訳わかりません！
そして、最初にテーブルを開いた後に、パススルークエリを開くと普通に開きます。
じゃ、作用最初だけテーブルを開けばOKかと言えば、VBAなどが動いているどこかのタイミングで元に戻ってしまうようです。
訳わかりません！
そこで、仕方なく、パソコンにODBCデータソースの設定をして、ODBCドライバーから接続をテストして見る事にしました。
（テーブル） ODBC;DRIVER=SQL Server;SERVER=192.168.×××.×××\SQLEXPRESS;UID=username;APP=Microsoft Office 2013;DATABASE=databasename;TABLE=tabename （パススルークエリ） ODBC;DRIVER=SQL Server;SERVER=192.168.×××.×××\SQLEXPRESS;DATABASE=databasename;UID=username;PWD=password
テーブルとパススルークエリのODBCドライバを直接接続から、ODBCドライバー接続に変更しました。
（テーブル） ODBC;DSN=drivername;UID=username;Trusted_Connection=Yes;APP=Microsoft Office 2013;DATABASE=databasename;TABLE=teblename （パススルークエリ） ODBC;DSN=drivername;Trusted_Connection=Yes;DATABASE=databasename
当たり前のことですが、ODBCドライバーから接続する時には、パソコンのODBCデータソースにユーザー名もパスワードも設定しているので、必要無くなりますね。
ちなみに、パススルークエリに設定した内容でも、テーブルもちゃんとアクセスできるのは確認しました。短い方で統一しても良いと思います。
ココがポイント
パソコンのODBCデータソースから接続したら問題解決
今回は、パソコンのスタンドアロンでSqlserverにAccessで接続して処理を実験してみましたが、ODBC接続でパススルークエリの挙動がおかしいという原因不明の現象に悩まされましたが、どうせ、スタンドアロンで使うので、ODBCデータソースを設定して接続すれば解決することが分かりました。納得はしていませんが、今回はこれで良しとします。
自分だけの備忘録
この内容は、今回の事とあまり関係ないので、自分だけの備忘録とさせて頂きます。
AccessのVBAにて、SqlServerのストアドを動かそうとした時に、
さらに詳しく
オブジェクトが閉じている場合は、操作は許可されません。
というエラーが出て悩みました。
調べてみると、VBA内で、SqlServerに接続する時のコードでエラーが出ていることがわかりました。
With Cnn .Provider = "SQLOLEDB" .ConnectionString = "Data Source=サーバー名\SQLEXPRESS;" & _ .ConnectionString = "Data Source=サーバー名\SQLEXPRESS;" & _ "Initial Catalog=データベース名;" & _ "User ID=ユーザー名;" & _ "Password=パスワード" .Open End With
このコードを下記のようにしたら問題が解決した。
（これでもエラーが出た）
With Cnn
.Provider = "MSDASQL"
.ConnectionString = "ODBC;DSN=ODBCデータソース名;Trusted_Connection=Yes;DATABASE=データベース名"
.Open
End With
（最終的にエラーが無くなった接続コード）
With Cnn
.Provider = "MSDASQL"
.ConnectionString = "DSN=ODBCデータソース名;Trusted_Connection=Yes;DATABASE=データベース名"
.Open
End With最近では、CopilotなどのAIを使ってコード修正をしてもらっていますが、これに辿り着くまでに時間がかかりましたので、備忘録として記録に取っておきたいと思います。