- アクセス側からデータを引っ張る
- SQL Server 側からデータを送る
- アプリケーションでSQL Server からデータを引っ張りアクセスに送る
など色々と方法は考えられるかと思いますが、そのうちの一例「SQL Server から accdb にリンクサーバーで接続し、データを出力する方法」をメモしておきます。
構成
- Windows Server 2019 64bit
- SQL Server Web 2019 64bit
- IIS 10
- Windows 10 Pro 64bit
- SQL Server 2019 Express 64bit
- Visual Studio 2019
- Office 2019 32bit
初めに空のaccdbファイルを作成して必要な場所に配置しておくと良いです。ACCESSのインストールされていない環境でもaccdbファイルを作成することが出来るようですが、詳細は検索してください。
64bit SQL Server からACCESS accdbファイルに接続するには 64bit 版の Microsoft.ACE.OLEDB が必要です。未インストールの場合は以下から入手できます。（ただし、64bit版と32bit版の共存にはトラブルがあるようで、既に32bit版Officeの入っていた私の開発環境ではACCESSが起動しなくなってしまいました。）
Microsoft Access データベース エンジン 2016 再頒布可能コンポーネント
https://www.microsoft.com/ja-JP/download/details.aspx?id=54920
インストールに成功するとSSMSのサーバーオブジェクト→リンクサーバー→プロバイダーから確認できます。
「リンクサーバー」右クリック→「新しいリンクサーバー」でウィザードが表示され設定できます。
以降は以下のような形でSSMSからリンクサーバーにクエリを投げることができます。
EXEC ('CREATE TABLE Sample (
Id INTEGER NOT NULL,
Name TEXT(50) NOT NULL,
Email TEXT(50)
)') AT [LINKSERVERNAME];
表題のSQLサーバからACCESS accdbにデータを出力するためのクエリを作成しますが、ラクをするために事前に出力先のテーブルをSSMSのエクスポートウィザードで作成しておきます。もちろんCREATE文を書いて対応するテーブルを作成してもかまいません。
以下のストアドプロシージャを作成します。
USE [SampleDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE spExportFromSQLtoACCDB
AS
BEGIN
SET NOCOUNT ON;
DELETE FROM [LINKSERVERNAME]...[Sample];
INSERT INTO [LINKSERVERNAME]...[Sample] SELECT * FROM SampleDB.dbo.Sample
END
GOこのストアドプロシージャを実行すれば SQL Server から accdb へのデータのエクスポートが実行できますが、アプリケーションから実行するにはパーミッションの設定が必要です。 今回の場合は ASP.NET から実行する形でしたので、IISの当該アプリケーションプールIDにaccdb ファイルへの変更権限を付与します。 アプリケーションプールIDはファイルの権限設定ウィンドウに出てこないので「IIS AppPool\DefaultAppPool」のような形で追加します。
以上メモまで。
