Kuidas helistada a SQL Server Salvestatud protseduur Excel VBA-st

Serveris olevaid andmeid saab muuta, uurides Excel VBA-s "kliendipoolseid" kirjeid, muutes neid vastavalt vajadusele ja salvestades need tagasi serverisse.
Tõhusam viis seda teha, eriti kui andmebaas asub kauges kohas ja sellega on seotud palju liiklust, on teha tööd serveripoolne. See harjutus kutsub Excelist välja salvestatud protseduuri, et liigitada töötajad vanusevahemikku vastavalt nende sünnikuupäevadele (st 18–25 aastat, 26–35 aastat jne), ilma serveri ja Exceli vahelise rikkaliku andmevahetuseta.

See artikkel eeldab, et lugejal on kuvatud arendaja lint ja ta tunneb VBA redaktorit. Kui ei, siis kasutage Google'i „Exceli arendaja vahekaarti” või „Exceli koodi akent”.

Treeningul on kolm elementi:

• Andmetabel tblPersonal andmebaasis TestDB;
• Salvestatud protseduur spAgeRange;
• Exceli xlsm, mida me kutsume xlsm. Exceli näidisfaili leiate siin

andmed tabelis

Looge sisse andmebaas SQL Server kutsutud DBTest.

Seadistage tabeli jaoks järgmised veerud tblPersonal.

Kopeerige tabelisse järgmine:

Salvestatud protseduur.

Salvestatud protseduuri loomiseks käivitage see skript TestDB vastu:

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

Salvestatud protseduur salvestatakse andmebaasi "Programmeeritavus" alla.

Exceli VBA

Jääb vaid helistada salvestatud protseduur Excelist, pakkudes Palgakuupäev parameeter "2017/05/25". Pange tähele, et olen lihtsalt andmed sisestanud  Palgakuupäev pigem stringina kui erineva kuupäevavorminguga maadlema. Stringi teisendamine kuupäevaks on piisavalt lihtne, kasutades Muutma funktsioon kui Palgakuupäev kasutatakse aritmeetika eesmärkidel.

Looge uus töövihik. Avage VBA koodi aken ja sisestage moodul.

Koodiakna menüüst Tööriistad valige sobiv Aktiivne X 2.nn library andmeobjektide kasutamise hõlbustamiseks.

Kleepige järgmine kood koodiaknasse. Pärast aktiveerimist loob see ühenduse SQL Server, vastavalt ConnectDatabase alamprotseduurile

'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

Lisage nupp lehele 1 ja määrake see alamprotseduurile "WriteStoredProcedure"

Tulemused

Vajutage nuppu, seejärel uurige tblStaff, mida tuleks värskendada vanuse ja vanusevahemikuga. Töötlemine on toimunud serveri poolel.

Rikutud töövihikute taastamine

Kui Excel peaks kokku jooksma, võib see teie töövihiku ainsa eksemplari kaasa võtta. Suur osa ajast ei suuda Excel sageli kahjustatud töövihikuid taastada; sellisel juhul võib kogu töövihiku loomisest saadik tehtud töö olla irrevocably lost, kui teil pole selleks tööriista Exceli parandamine xlsx või xlsm failid.

Autori sissejuhatus:

Felix Hooker on andmete taastamise ekspert DataNumen, Inc., mis on maailmas juhtiv andmete taastamise tehnoloogiate, sealhulgas rar remont ja SQL-i taastamise tarkvaratooted. Lisateabe saamiseks külastage www.datanumenCom