電話をかける方法 SQL Server ExcelVBAからのストアドプロシージャ

サーバー上のデータは、Excel VBAでレコード「クライアント側」を調べ、必要に応じて変更し、サーバーに保存することで変更できます。
これを行うためのより効率的な方法は、特にデータベースが離れた場所にあり、大量のトラフィックが関係している場合、「サーバー側」の作業を行うことです。 この演習では、Excelからストアドプロシージャを呼び出して、サーバーとExcelの間で大量のデータを交換することなく、従業員を生年月日（18〜25歳、26〜35歳など）に従って年齢範囲に分類します。

この記事は、読者が開発者リボンを表示していて、VBAエディターに精通していることを前提としています。 そうでない場合は、Googleの「Excel開発者タブ」または「Excelコードウィンドウ」をご覧ください。

演習にはXNUMXつの要素があります。

• データテーブル tblスタッフ データベース内 テストDB;
• ストアドプロシージャ spAgeRange;
• これをExcelxlsmと呼びます。 xlsm。 サンプルのExcelファイルがあります こちら

データ表

でデータベースを作成する SQL Server 呼ばれます DBテスト.

テーブルに次の列を設定します tblスタッフ.

以下を表にコピーします。

ストアドプロシージャ。

TestDBに対してこのスクリプトを実行して、ストアドプロシージャを作成します。

USE [TestDB]
GO
/****** Object: StoredProcedure [dbo].[spAgeRange] Script Date: 2017/05/10 12:16:28 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[spAgeRange]

    @PayrollDate varchar(50)
 
AS
BEGIN

    SET NOCOUNT ON;
    UPDATE tblStaff SET Age = CONVERT(int, DATEDIFF(day, DateOfBirth, GETDATE()) / 365.25, 0) 
    WHERE tblStaff.PayrollDate = @PayrollDate
 
    Update tblStaff set AgeRange = '>56' where Age >= 56 and PayrollDate = @PayrollDate

    Update tblStaff set AgeRange = '46 to 55' where Age >= 46 and Age < 56 and PayrollDate = PayrollDate 
 
    Update tblStaff set AgeRange = '39 to 45' where Age >= 39 and Age < 46 and PayrollDate = @PayrollDate 
 
    Update tblStaff set AgeRange = '31 to 38' where Age >= 30 and Age < 39 and PayrollDate = @PayrollDate 
 
    Update tblStaff set AgeRange = '25 to 30' where Age >= 25 and Age < 30 and PayrollDate = @PayrollDate 
 
    Update tblStaff set AgeRange = '18 to 24' where Age >= 18 and Age < 25 and PayrollDate = @PayrollDate 
 
    Update tblStaff set AgeRange = '<18' where Age < 18 and PayrollDate = @PayrollDate

END

ストアドプロシージャは、データベースの「Programmability」の下に保存されます。

Excel VBA

残っているのは、Excelからストアドプロシージャを呼び出して、 給与日 「2017/05/25」のパラメータ。 あなたは私が単にデータ型をしたことに気付くでしょう  給与日 さまざまな日付形式と格闘するのではなく、文字列として。 を使用して文字列を日付に変換するのは簡単です 変換 機能する場合 給与日 算術目的で使用されます。

新しいブックを作成します。 VBAコードウィンドウを開き、モジュールを挿入します。

コードウィンドウの[ツール]メニューから、適切なを参照します Active X 2.nnライブラリ データオブジェクトの使用を容易にするため。

次のコードをコードウィンドウに貼り付けます。 これは、アクティブ化されると、に接続します SQL Server、ConnectDatabaseサブプロシージャに従って

'All "public" in case the code is spread over several modules.
Public connDB As New ADODB.Connection
Public rs As New ADODB.Recordset
Public strSQL As String
Public strConnectionstring As String
Public strServer As String
Public strDBase As String
Public strUser As String
Public strPwd As String
Public PayrollDate As String

Sub WriteStoredProcedure()
     PayrollDate = "2017/05/25"
     Call ConnectDatabase
     On Error GoTo errSP
     strSQL = "EXEC spAgeRange '" & PayrollDate & "'"
     connDB.Execute (strSQL)
     Exit Sub
errSP:
MsgBox Err.Description
End Sub

Sub ConnectDatabase()
     If connDB.State = 1 Then connDB.Close
     On Error GoTo ErrConnect
     strServer = "SERVERNAME" ‘The name or IP Address of the SQL Server
     strDBase = "TestDB"
     strUser = "" 'leave this blank for Windows authentication
     strPwd = ""

     If strPwd > "" Then
         strConnectionstring = "DRIVER={SQL Server};Server=" & strServer & ";Database=" & strDBase & ";Uid=" & strUser & ";Pwd=" & strPwd & ";Connection Timeout=30;"
     Else
         strConnectionstring = "DRIVER={SQL Server};SERVER=" & strServer & ";Trusted_Connection=yes;DATABASE=" &    strDBase 'Windows authentication
     End If
     connDB.ConnectionTimeout = 30
     connDB.Open strConnectionstring
Exit Sub
ErrConnect:
     MsgBox Err.Description
End Sub

Sheet1にボタンを追加し、それをサブプロシージャ「ストアドプロシージャの書き込み　

結果

ボタンを押してから、tblStaffを調べます。これは、年齢と年齢範囲で更新する必要があります。 処理はサーバー側で行われました。

破損したブックの回復

Excelがクラッシュすると、ワークブックの唯一のコピーも一緒に失われてしまう可能性があります。Excelは破損したワークブックを復元できないことがかなりの割合で発生し、そのような場合、ワークブックの作成以降に行ったすべての作業が取り返しのつかないほど失われる可能性があります。 Excelを修復する xlsxまたはxlsmファイル。

著者紹介：

フェリックスフッカーは、のデータ復旧の専門家です DataNumen、Inc。は、以下を含むデータ復旧技術の世界的リーダーです。 RAR修復 およびSQL回復ソフトウェア製品。 詳細については、次のWebサイトをご覧ください。 WWW。datanumen.com