Hur man ringer till en SQL Server Lagrad procedur från Excel VBA

Data på en server kan modifieras genom att undersöka posternas "klientsida" i Excel VBA, ändra dem efter behov och spara dem på servern.
Ett mer effektivt sätt att göra detta, särskilt om databasen befinner sig på en avlägsen plats och det är mycket trafik inblandad, är att göra arbetet "på serversidan". Denna övning kallar ett lagrat förfarande från Excel för att kategorisera anställda i åldersintervall efter deras födelsedatum (dvs. 18-25 år, 26-35 år, etc.), utan ett omfattande utbyte av data mellan servern och Excel.

Den här artikeln förutsätter att läsaren har utvecklarbandet och är bekant med VBA Editor. Om inte, vänligen Google "Excel-fliken för utvecklare" eller "Excel-kodfönstret".

Det finns tre element i övningen:

  • En datatabell tblPersonal inom en databas TestDB;
  • En lagrad procedur spAgeRange;
  • En Excel xlsm, som vi kallar xlsm. Ett exempel på en Excel-fil kan hittas här.

data~~POS=TRUNC

Skapa en databas i SQL Server kallas DBTest.

Ställ in följande kolumner för en tabell tblPersonal.

Ställ in kolumnerna för en tabell tblStaff

Kopiera följande till tabellen:

2017/05/25 1 Brun J 1946/12/02 M
2017/05/25 2 Smart A 1976/03/26 F
2017/05/25 3 Kryssning T 1962/07/03 M
2017/05/25 4 Lohan L 1986/07/02 F
2017/05/25 5 Fredricksen F 1964/03/15 M
2017/05/25 6 Snyder L 1968/07/05 F
2017/05/25 7 Lipnicki J 1983/11/25 M
2017/05/25 8 Hoover S 2002/12/08 F
2017/05/25 9 Watson E 1990/04/15 F

Lagrad procedur.

Kör det här skriptet mot TestDB för att skapa den lagrade proceduren:

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

Den lagrade proceduren sparas under "Programmerbarhet" i databasen.

Excel VBA

Allt som återstår är att ringa till den lagrade proceduren från Excel och tillhandahålla Lönedatum parameter "2017/05/25". Du kommer att notera att jag helt enkelt har datatypat  Lönedatum som en sträng snarare än att brottas med olika datumformat. Det är tillräckligt enkelt att konvertera en sträng till ett datum med hjälp av Konvertera funktion om Lönedatum ska användas för aritmetiska ändamål.

Skapa en ny arbetsbok. Öppna VBA-kodfönstret och sätt in en modul.

Från kodfönstrets verktygsmeny hänvisar du till lämpligt Active X 2.nn-bibliotek för att underlätta användningen av dataobjekt.Referera till lämpligt Active X 2.nn-bibliotek för att underlätta användningen av dataobjekt

Klistra in följande kod i kodfönstret. Detta, när det är aktiverat, kommer att ansluta till SQL Serverenligt ConnectDatabase-underproceduren

'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

Lägg till en knapp i Sheet1 och tilldela den till underproceduren “WriteStoredProcedure"

Resultatet

Tryck på knappen och undersök sedan tblStaff, som ska uppdateras med åldrar och åldersintervall. Bearbetningen har ägt rum på serversidan.

Återställa skadade arbetsböcker

Om Excel kraschar kan det mycket väl ta bort din enda kopia av arbetsboken. En stor andel av gångerna kan Excel ofta inte återställa skadade arbetsböcker; i ett sådant fall kan allt arbete som gjorts sedan arbetsboken skapades gå oåterkalleligt förlorat, såvida du inte har ett verktyg för att... reparera Excel xlsx- eller xlsm-filer.

Författarintroduktion:

Felix Hooker är en dataåterställningsexpert i DataNumen, Inc., som är världsledande inom teknik för återställning av data, inklusive rar-reparation och mjukvaruprodukter för SQL-återställning. För mer information besök www.datanumen.com

Kommentarer är stängda.