【Access VBA】SQL Serverのテンポラリテーブルからデータを取得する

SQL Serverのテンポラリテーブルからデータを取得する

SQL Serverのテンポラリテーブルからデータを取得し、Accessのテーブルに取り込みます。
テンポラリテーブル名の末尾にセッションIDを付与して他のセッションが作成するテンポラリテーブルと重複しないようにします。

テーブルを準備する

SQL Serverで「sample」という名前のデータベースの中に「T売上明細」という名前のテーブルを用意しました。

Accessには「WT売上明細」という名前のテーブルを用意しました。テーブルにはデータが入力されていません。

SQL Serverにストアドプロシージャを準備する

SQL Serverに「export売上明細」という名前のストアドプロシージャを用意しました。
これによりテンポラリテーブルを作成し、入力パラメーターの@slip_noで受け取った伝票番号のレコードをテンポラリテーブルに書き込みます。

CREATE PROCEDURE export売上明細 	
	@slip_no int,@ID int OUTPUT
AS
BEGIN	
	SET NOCOUNT ON;

    BEGIN TRY			
		DECLARE @table_name VARCHAR(50);
		DECLARE @sql VARCHAR(1000);
		SET @ID=@@spid	   
		SET @table_name='';
		SET @table_name=@table_name+'##TEMP売上明細'+CONVERT(VARCHAR,@@spid);
		SET @sql='';
		SET @sql=@sql+'IF EXISTS(SELECT * FROM tempdb.sys.tables WHERE name='''+@table_name+''')';
		SET @sql=@sql+'DROP TABLE '+@table_name+';';   
		SET @sql=@sql+'CREATE TABLE '+@table_name;
		SET @sql=@sql+'(';
		SET @sql=@sql+'明細ID int';
		SET @sql=@sql+',伝票番号 int';
		SET @sql=@sql+',商品コード VARCHAR(4)';
		SET @sql=@sql+',数量 int';
		SET @sql=@sql+');';
		SET @sql=@sql+'INSERT INTO '+@table_name;
		SET @sql=@sql+' SELECT * FROM T売上明細 WHERE 伝票番号=';
		SET @sql=@sql+ CONVERT(VARCHAR,@slip_no)
                EXECUTE(@sql);	 
		RETURN -1 
	END TRY

	BEGIN CATCH				
		RETURN 0
	END CATCH
END

SQL ServerのテンポラリテーブルのデータをAccessのテーブルに取得するコードの記述

Accessの標準モジュールに以下のコードを記述しました。

Public Sub ExecuteStoredProcedure()
    On Error GoTo ErrhA
    Dim strCN_sample As String
    strCN_sample = "DRIVER={ODBC Driver 17 for SQL Server};SERVER=LAPTOP-114315\SQLEXPRESS;DATABASE=sample;UID=admin;PWD=1111"
    Dim cn As New ADODB.Connection
    cn.Open strCN_sample
    Dim sid As Integer 'SQL Serverが割り当てるセッションIDを格納する変数
    Dim slip_no As Integer 'Accessに書き出したい伝票番号を格納する変数
    slip_no = 522
    Dim cmd As New ADODB.Command
    cmd.ActiveConnection = cn
    cmd.CommandType = adCmdStoredProc
    cmd.CommandText = "export売上明細"
    cmd.CommandTimeout = 3
    cmd.Parameters.Append cmd.CreateParameter("@return_value", adInteger, adParamReturnValue, , Null)
    cmd.Parameters.Append cmd.CreateParameter("@slip_no", adInteger, adParamInput, , slip_no)
    cmd.Parameters.Append cmd.CreateParameter("@ID", adInteger, adParamOutput, , Null)
    cmd.Execute
    On Error GoTo 0
    
    If CBool(cmd.Parameters("@return_value").Value) Then
        On Error GoTo ErrhB
        sid = cmd.Parameters("@ID").Value
        Dim w_cn As New ADODB.Connection
        Set w_cn = CurrentProject.AccessConnection
        Dim w_cmd As New ADODB.Command
        w_cmd.ActiveConnection = w_cn
        w_cmd.CommandType = adCmdText
        Dim strCN_temp As String
        strCN_temp = "DRIVER={ODBC Driver 17 for SQL Server};SERVER=LAPTOP-114315\SQLEXPRESS;DATABASE=tempdb;UID=admin;PWD=1111"
        w_cmd.CommandText = "INSERT INTO WT売上明細 SELECT * FROM [##TEMP売上明細" & CStr(sid) & "] IN ''[ODBC;" & strCN_temp & "]"
        w_cmd.Execute
        On Error GoTo 0
        Set w_cmd = Nothing
        w_cn.Close: Set w_cn = Nothing
    Else
        MsgBox "エラーが発生しました。", vbExclamation, "確認"
    End If
    Set cmd = Nothing
    cn.Close: Set cn = Nothing
    Exit Sub
ErrhA:
    MsgBox "エラーが発生しました。", vbExclamation, "確認"
    Set cmd = Nothing
    cn.Close: Set cn = Nothing
    Exit Sub
ErrhB:
    MsgBox "エラーが発生しました。", vbExclamation, "確認"
    Set w_cmd = Nothing
    w_cn.Close: Set w_cn = Nothing
End Sub