2021年5月12日水曜日

SQL Server リンクサーバーを使って ACCESS accdb にデータをコピー

  • アクセス側からデータを引っ張る
  • 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」のような形で追加します。

以上メモまで。