Jak zadzwonić do SQL Server Procedura składowana z Excel VBA

Dane na serwerze można modyfikować, sprawdzając rekordy „po stronie klienta” w programie Excel VBA, zmieniając je w razie potrzeby i zapisując z powrotem na serwerze.
Bardziej wydajnym sposobem na zrobienie tego, szczególnie jeśli baza danych znajduje się w zdalnej lokalizacji i wiąże się to z dużym ruchem, jest wykonanie pracy po stronie serwera. To ćwiczenie wywołuje procedurę składowaną z programu Excel w celu kategoryzacji pracowników według przedziałów wiekowych według ich dat urodzenia (tj. 18–25 lat, 26–35 lat itd.), Bez nadmiernej wymiany danych między serwerem a programem Excel.

W tym artykule założono, że czytelnik ma wyświetloną wstążkę programisty i zna edytor VBA. Jeśli nie, wybierz Google „Excel Developer Tab” lub „Excel Code Window”.

Ćwiczenie składa się z trzech elementów:

• Tabela danych tblPersonel w bazie danych Testowa baza danych;
• Procedura składowana spAgeRange;
• Excel xlsm, który nazwiemy xlsm. Można znaleźć przykładowy plik Excel w tym miejscu

Tabela danych

Utwórz bazę danych w SQL Server o nazwie Test DB.

Skonfiguruj następujące kolumny dla tabeli tblPersonel.

Skopiuj do tabeli:

Procedura składowana.

Uruchom ten skrypt na TestDB, aby utworzyć procedurę składowaną:

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

Procedura składowana zostanie zapisana w bazie danych w obszarze „Programowalność”.

Excel VBA

Pozostaje tylko wywołać procedurę składowaną z programu Excel, zapewniając rozszerzenie PayrollData parametr „2017/05/25”. Zauważysz, że po prostu wpisałem dane  PayrollData jako ciąg znaków zamiast zmagać się z różnymi formatami daty. Przekonwertowanie ciągu na datę przy użyciu rozszerzenia konwertować funkcja, jeśli PayrollData ma być używany do celów arytmetycznych.

Utwórz nowy skoroszyt. Otwórz okno kodu VBA i wstaw moduł.

W menu Narzędzia okna kodu odwołaj się do odpowiedniego pliku Biblioteka Active X 2.nn ułatwienie korzystania z obiektów danych.

Wklej następujący kod do okna Code. To, po aktywacji, połączy się z SQL Server, zgodnie z procedurą podrzędną 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

Dodaj przycisk do Arkusza1 i przypisz go do procedury podrzędnej „ZapiszProceduraPrzechowywana"

Efekty

Naciśnij przycisk, a następnie sprawdź tabelę tblStaff, która powinna zostać zaktualizowana o grupy wiekowe i przedziały wiekowe. Przetwarzanie odbyło się po stronie serwera.

Odzyskiwanie uszkodzonych skoroszytów

Jeśli Excel ulegnie awarii, może to spowodować utratę jedynej kopii skoroszytu. W dużej mierze Excel nie jest w stanie odzyskać uszkodzonych skoroszytów; w takim przypadku cała praca wykonana od momentu utworzenia skoroszytu może zostać bezpowrotnie utracona, chyba że dysponujesz odpowiednim narzędziem. napraw Excel xlsx lub xlsm.

Wprowadzenie autora:

Felix Hooker jest ekspertem w dziedzinie odzyskiwania danych w DataNumen, Inc., która jest światowym liderem w technologiach odzyskiwania danych, w tym naprawa rar i oprogramowanie do odzyskiwania sql. po więcej informacji odwiedź www.datanumen.com