Necə Zəng etməli a SQL Server Excel VBA-dan Saxlanan Prosedur

Serverdəki məlumatlar Excel VBA-da "müştəri tərəfi" qeydlərini yoxlamaq, tələb olunduqda onları dəyişdirmək və onları yenidən serverə saxlamaqla dəyişdirilə bilər.
Bunu etmənin daha səmərəli yolu, xüsusən də verilənlər bazası uzaq bir yerdədirsə və orada çoxlu trafik varsa, işi “server tərəfində” etməkdir. Bu məşq, server və Excel arasında çoxlu məlumat mübadiləsi olmadan işçiləri doğum tarixlərinə (yəni 18-25 yaş, 26-35 yaş və s.) görə yaş aralığına bölmək üçün Excel-dən saxlanılan proseduru çağırır.

Bu məqalə oxucunun Tərtibatçı lentinin göstərildiyini və VBA Redaktoru ilə tanış olduğunu güman edir. Əgər belə deyilsə, lütfən Google “Excel Developer Tab” və ya “Excel Code Window”.

Məşq üçün üç element var:

• Məlumat cədvəli tblStaff verilənlər bazası daxilində TestDB;
• Saxlanılan prosedur spAgeRange;
• Zəng edəcəyimiz Excel xlsm xlsm. Nümunə Excel faylı tapa bilərsiniz burada

Məlumat Cədvəli

-də verilənlər bazası yaradın SQL Server adlanan DBTest.

Cədvəl üçün aşağıdakı sütunları qurun tblStaff.

Aşağıdakıları cədvələ köçürün:

Saxlanılan Prosedur.

Saxlanılan proseduru yaratmaq üçün bu skripti TestDB-yə qarşı işlədin:

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

Saxlanılan prosedur verilənlər bazasında "Proqramlaşma" altında saxlanılacaq.

Excel VBA

Qalır ki, saxlanılan prosedura Excel-dən zəng etmək lazımdır Əmək haqqı tarixi “2017/05/25” parametri. Siz qeyd edəcəksiniz ki, mən sadəcə məlumat tipinə malikəm  Əmək haqqı tarixi müxtəlif tarix formatları ilə güləşdən çox simli kimi. Istifadə edərək sətri tarixə çevirmək kifayət qədər sadədir Çevirmək funksiyası varsa Əmək haqqı tarixi arifmetik məqsədlər üçün istifadə edilməlidir.

Yeni iş kitabı yaradın. VBA kodu pəncərəsini açın və modul daxil edin.

Kod pəncərəsinin Alətlər menyusundan uyğun olana istinad edin Active X 2.nn library məlumat obyektlərinin istifadəsini asanlaşdırmaq.

Aşağıdakı kodu Kod pəncərəsinə yapışdırın. Bu, aktivləşdirildikdən sonra qoşulacaq SQL Server, ConnectDatabase alt proseduruna uyğun olaraq

'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-ə düymə əlavə edin və onu alt prosedura təyin edin "WriteStoredProcedure"

Nəticələr

Düyməni basın, sonra yaş və yaş aralığı ilə yenilənməli olan tblStaff-ı yoxlayın. Emal server tərəfində baş verdi.

Zədələnmiş iş dəftərlərinin bərpası

Excel qəzaya uğrayarsa, iş kitabının yeganə nüsxəsini onunla birlikdə götürə bilər. Vaxtın yaxşı faizi Excel tez-tez zədələnmiş iş kitablarını bərpa edə bilmir; belə halda, iş kitabının yaradılmasından bəri görülən bütün işlər geri qaytarıla bilərcably lost, alətiniz olmadığı təqdirdə Excel təmiri xlsx və ya xlsm faylları.

Müəllif Giriş:

Feliks Hooker məlumatların bərpası üzrə mütəxəssisdir DataNumendaxil olmaqla məlumatların bərpası texnologiyaları üzrə dünya lideri olan , Inc rar təmir və sql bərpa proqram məhsulları. Ətraflı məlumat üçün ziyarət edin www.datanumen.com