Kuinka soittaa a SQL Server Tallennettu menettely Excel VBA: lta

Palvelimen tietoja voidaan muokata tutkimalla tietueiden 'asiakaspuoli' Excel VBA: ssa, muuttamalla niitä tarvittaessa ja tallentamalla ne takaisin palvelimelle.
Tehokkaampi tapa tehdä tämä, etenkin jos tietokanta on syrjäisessä paikassa ja liikennettä on paljon, on tehdä työ palvelinpuolella. Tämä harjoitus kutsuu Excelistä tallennetun menettelyn työntekijöiden luokittelemiseksi ikäryhmiin heidän syntymäaikojensa mukaan (esim. 18-25 vuotta, 26-35 vuotta jne.) Ilman palvelimen ja Excelin välistä runsasta tietojenvaihtoa.

Tässä artikkelissa oletetaan, että lukijalla on kehittäjänauha ja että hän tuntee VBA-editorin. Jos ei, ota Google "Excel Developer -välilehti" tai "Excel Code -ikkuna".

Harjoituksessa on kolme elementtiä:

• Tietotaulukko tblTyöntekijät tietokannassa TestDB;
• Tallennettu menettely SPAgeRange;
• Excel-xlsm, jota kutsumme xlsm. Näyte Excel-tiedostosta löytyy täältä

data Table

Luo tietokanta SQL Server nimeltään DBTest.

Määritä seuraavat sarakkeet taulukolle tblTyöntekijät.

Kopioi seuraava taulukko:

Tallennettu menettely.

Suorita tämä komentosarja TestDB: tä vastaan ​​luodaksesi tallennetun menettelyn:

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

Tallennettu toiminto tallennetaan tietokannan ”Ohjelmoitavuus” -kohtaan.

Excel VBA

Ainoa on kutsua tallennettu menettely Excelistä, tarjoamalla Palkkapäivä “2017/05/25” -parametri. Huomaa, että olen yksinkertaisesti kirjoittanut tiedot  Palkkapäivä pikemminkin merkkijonona kuin paini vaihtelevilla päivämäärämuotoilla. Merkkijono voidaan muuntaa päivämääräksi käyttämällä Muuntaa toiminto jos Palkkapäivä on käytettävä aritmeettisiin tarkoituksiin.

Luo uusi työkirja. Avaa VBA-koodi-ikkuna ja aseta moduuli.

Viittaa sopivaan koodiikkunan Työkalut-valikossa Active X 2.nn -kirjasto tietojen objektien käytön helpottamiseksi.

Liitä seuraava koodi Koodi-ikkunaan. Kun tämä on aktivoitu, se muodostaa yhteyden SQL Server, kuten ConnectDatabase-alimenettely

'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

Lisää painike Sheet1: een ja määritä se alimenettelyyn “WriteStoredProcedure"

Tulokset

Paina painiketta ja tarkista sitten tblStaff, joka tulisi päivittää iän ja ikäryhmän mukaan. Käsittely on tapahtunut palvelinpuolella.

Vioittuneiden työkirjojen palauttaminen

Jos Excel kaatuu, se saattaa hyvinkin viedä mukanaan ainoan kopiosi työkirjasta. Useimmiten Excel ei pysty palauttamaan vaurioituneita työkirjoja. Tällaisessa tapauksessa kaikki työkirjan luomisen jälkeen tehty työ saattaa kadota peruuttamattomasti, ellet käytä työkalua. korjaa Excel xlsx- tai xlsm-tiedostot.

Tekijän esittely:

Felix Hooker on tietojen palauttamisen asiantuntija DataNumen, Inc., joka on maailman johtava tietojen palautustekniikoissa, mukaan lukien rar-korjaus ja sql-palautusohjelmistotuotteet. Lisätietoja osoitteessa www.datanumen.com