Nasıl Aranır SQL Server Excel VBA'dan Saklı Prosedür

Bir sunucudaki veriler, Excel VBA'da 'istemci tarafında' kayıtlar incelenerek, gerektiğinde değiştirilerek ve sunucuya geri kaydedilerek değiştirilebilir.
Bunu yapmanın daha verimli bir yolu, özellikle veritabanı uzak bir konumdaysa ve çok fazla trafik söz konusuysa, işi 'sunucu tarafında' yapmaktır. Bu alıştırma, sunucu ile Excel arasında çok fazla veri alışverişi olmadan, çalışanları doğum tarihlerine göre (yani 18-25 yaş, 26-35 yaş, vb.) yaş aralıklarına göre sınıflandırmak için Excel'den bir saklı yordam çağırır.

Bu makalede, okuyucunun Geliştirici şeridinin görüntülendiği ve VBA Düzenleyicisine aşina olduğu varsayılmaktadır. Değilse, lütfen Google "Excel Geliştirici Sekmesi" veya "Excel Kod Penceresi".

Egzersizin üç unsuru vardır:

• bir veri tablosu tblStaff bir veritabanı içinde TestDB'si;
• saklı yordam Yay Aralığı;
• Arayacağımız bir Excel xlsm xlsm. Örnek bir Excel dosyası bulunabilir okuyun

veri Tablosu

içinde bir veritabanı oluşturun SQL Server denilen DBTest.

Bir tablo için aşağıdaki sütunları ayarlayın tblStaff.

Aşağıdakileri tabloya kopyalayın:

Saklı yordam.

Saklı yordamı oluşturmak için bu betiği TestDB'ye karşı çalıştırın:

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

Saklı yordam, veritabanında "Programlanabilirlik" altında kaydedilecektir.

Excel VBA

Geriye kalan tek şey, saklanan yordamı Excel'den çağırmaktır. Bordro Tarihi “2017/05/25” parametresi. Basitçe veri yazdığımı fark edeceksiniz  Bordro Tarihi değişen tarih biçimleriyle güreşmek yerine bir dize olarak. kullanarak bir diziyi tarihe dönüştürmek yeterince basittir. dönüştürmek işlev eğer Bordro Tarihi aritmetik amaçlar için kullanılacaktır.

Yeni bir çalışma kitabı oluşturun. VBA kodu penceresini açın ve bir modül ekleyin.

Kod penceresinin Araçlar menüsünden, uygun Aktif X 2.nn kitaplığırary veri nesnelerinin kullanımını kolaylaştırmak için.

Aşağıdaki kodu Kod penceresine yapıştırın. Bu, etkinleştirildiğinde şuna bağlanacaktır: SQL Server, ConnectDatabase alt prosedürüne göre

'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

Sayfa1'e bir düğme ekleyin ve onu " alt prosedüre atayınWriteStoredProsedürbaşlıklı bir kılavuz yayınladı

Sonuçlar

Düğmeye basın, ardından yaş ve yaş aralıkları ile güncellenmesi gereken tblStaff'ı inceleyin. İşlem sunucu tarafında gerçekleşti.

Bozuk çalışma kitaplarını kurtarma

Excel çökerse, çalışma kitabının tek kopyasını da beraberinde götürebilir. Çoğu zaman Excel, zarar görmüş çalışma kitaplarını kurtaramaz; böyle bir durumda, çalışma kitabının oluşturulmasından bu yana yapılan tüm çalışmalar geri alınabilir.cabevet benost, bir aracınız olmadığı sürece Excel'i onar xlsx veya xlsm dosyaları.

Yazar Tanıtımı:

Felix Hooker, veri kurtarma uzmanıdır. DataNumendahil olmak üzere veri kurtarma teknolojilerinde dünya lideri olan , Inc. rar onarım ve sql kurtarma yazılımı ürünleri. Daha fazla bilgi için ziyaret edin www.datanumen.com