Як зателефонувати a SQL Server Збережена процедура з Excel VBA

Поділитися зараз:

Дані на сервері можна змінити, перевіривши записи на стороні клієнта в Excel VBA, змінивши їх за необхідності та збереживши назад на сервері.
Більш ефективний спосіб зробити це, особливо якщо база даних знаходиться у віддаленому місці і залучено багато трафіку, - це виконувати роботу на стороні сервера. Ця вправа вимагає збереженої процедури з Excel для класифікації працівників за віковими діапазонами відповідно до їх дат народження (тобто 18-25 років, 26-35 років тощо), без рясного обміну даними між сервером та Excel.

У цій статті передбачається, що на пристрої для читання відображається стрічка розробника та він знайомий з редактором VBA. Якщо ні, будь ласка, перегляньте Google “Вкладка розробника Excel” або “Вікно коду Excel”.

У вправі є три елементи:

  • Таблиця даних tblСтаф в базі даних TestDB;
  • Збережена процедура spAgeRange;
  • Excel xlsm, який ми будемо називати xlsm. Зразок файлу Excel можна знайти тут

Таблиця даних

Створити базу даних у SQL Server званий DBTest.

Налаштуйте наступні стовпці для таблиці tblСтаф.

Налаштування стовпців для таблиці 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 Hoover 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”. Ви зауважите, я просто набрав дані  Дата виплати заробітної плати як струна, а не боротьба з різними форматами дат. Досить просто перетворити рядок на дату за допомогою Конвертувати функція if Дата виплати заробітної плати має використовуватися для арифметичних цілей.

Створіть нову книгу. Відкрийте вікно коду 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

Поділитися зараз:

Коментарі закриті.