Қалай қоңырау шалу керек SQL Server Excel VBA-дан сақталған процедура

Қазір бөлісу:

Сервердегі деректерді Excel VBA-дағы «клиенттік» жазбаларды зерттеп, оларды қажетіне қарай өзгерту және оларды серверге сақтау арқылы өзгертуге болады.
Мұны жасаудың неғұрлым тиімді тәсілі, егер мәліметтер базасы қашықта болса және трафик көп болса, «сервер жағында» жұмыс жасау керек. Бұл жаттығу Excel-де сақталған процедураны шақырады, қызметкерлерді олардың туған күндеріне қарай жас шамаларына бөлу (яғни 18-25 жас, 26-35 жас және т.б.), сервер мен Excel арасында мол деректер алмасусыз.

Бұл мақала оқырманға Әзірлеуші ​​лентасы көрсетілген және VBA редакторымен таныс деп болжайды. Олай болмаса, Google-дің «Excel Developer Tab» немесе «Excel Code Window» сұраймыз.

Жаттығудың үш элементі бар:

  • Мәліметтер кестесі tblStaff деректер базасында TestDB;
  • Сақталған процедура spAgeRange;
  • Біз шақыратын Excel xlsm xlsm. Excel файлының үлгісін табуға болады Мұнда

Мәліметтер кестесі

Деректер базасын жасаңыз SQL Server деп аталатын DBTest.

Кесте үшін келесі бағандарды орнатыңыз tblStaff.

TblStaff кестесіне бағандарды орнатыңыз

Келесілерді кестеге көшіріңіз:

2017/05/25 1 қоңыр J 1946/12/02 M
2017/05/25 2 Smart A 1976/03/26 F
2017/05/25 3 круиз T 1962/07/03 M
2017/05/25 4 Лохан L 1986/07/02 F
2017/05/25 5 Фредриксен F 1964/03/15 M
2017/05/25 6 Снайдер L 1968/07/05 F
2017/05/25 7 Липникки J 1983/11/25 M
2017/05/25 8 Гувер S 2002/12/08 F
2017/05/25 9 Уотсон E 1990/04/15 F

Сақталған процедура.

Сақталған процедураны жасау үшін осы сценарийді TestDB-ге қарсы іске қосыңыз:

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

Сақталған процедура мәліметтер базасында «Бағдарламаланатындық» астында сақталады.

Excel VBA

Тек Excel бағдарламасынан сақталған процедураны шақыру қалады Жалақы күні «2017/05/25» параметрі. Мен жай ғана мәліметтер тергенімді ескересіз  Жалақы күні әртүрлі күн форматтарымен күресуден гөрі жіп ретінде. Көмегімен жолды күнге айналдыру өте қарапайым Түрлендіру функция, егер Жалақы күні арифметикалық мақсаттарда қолданылуы керек.

Жаңа жұмыс кітабын жасаңыз. VBA кодтық терезесін ашып, модуль салыңыз.

Код терезесінің Құралдар мәзірінен сәйкесінше сілтеме жасаңыз Active X 2.nn кітапханасы деректер объектілерін пайдалануды жеңілдету.Деректер нысандарын пайдалануды жеңілдету үшін тиісті Active X 2.nn кітапханасына сілтеме жасаңыз

Код терезесіне келесі кодты қойыңыз. Бұл белсендірілгеннен кейін қосылады SQL Server, 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

Параққа1 батырмасын қосып, оны “ішкі процедураға тағайындаңыз.WriteStoredProcedure«

Нәтижелері

Түймені басыңыз, содан кейін tblStaff-ті тексеріңіз, оны жас және жас аралықтарына қарай жаңарту керек. Өңдеу сервер жағында өтті.

Бүлінген жұмыс кітаптарын қалпына келтіру

Егер Excel істен шықса, жұмыс кітабыңыздың жалғыз данасын алып кетуі мүмкін. Excel көбінесе зақымдалған жұмыс кітаптарын қалпына келтіре алмайды; мұндай жағдайда, егер сізде құрал болмаса, жұмыс кітабы жасалғаннан бері жасалған барлық жұмыс қайтарымсыз жоғалуы мүмкін. Excel бағдарламасын жөндеу xlsx немесе xlsm файлдары.

Автордың кіріспесі:

Феликс Хукер - деректерді қалпына келтіру бойынша сарапшы DataNumen, Соның ішінде деректерді қалпына келтіру технологиялары бойынша әлемдік көшбасшы болып табылатын Inc. rar нұсқасын жөндеу және SQL қалпына келтіру бағдарламалық жасақтама өнімдері. Қосымша ақпарат алу үшін кіріңіз WWW.datanumen.com

Қазір бөлісу:

Пікірлер жабылды.