Funkcja VBA i SQL

Function Wylicz(addressCell As String, cityCell As String, startDate As String, endDate As String)
Dim address As String
Dim city As String

address = Replace(addressCell, " ", "%")
city = Replace(cityCell, " ", "%")

Dim objRec As ADODB.Recordset
Dim objConn As ADODB.Connection
Dim cmdString As String

'Set objRec = CreateObject("ADODB.Recordset")
'Set objConn = CreateObject("ADODB.Connection")

objConn.ConnectionString = "Driver={SQL Server};Server=serwer;Database=baza;User ID=user;Password=haslo"
objConn.Open

cmdString = "DECLARE @table AS TGroups" + Chr(13)
cmdString = cmdString + "GO" + Chr(13)
cmdString = cmdString + "INSERT INTO @table (groupName) VALUES ('Grupa1')" + Chr(13)
cmdString = cmdString + "GO" + Chr(13)
cmdString = cmdString + "INSERT INTO @table (groupName) VALUES ('Grupa2')" + Chr(13)
cmdString = cmdString + "GO" + Chr(13)
cmdString = cmdString + "INSERT INTO @table (groupName) VALUES ('Grupa3')" + Chr(13)
cmdString = cmdString + "GO" + Chr(13)

Set objRec = objConn.Execute(cmdString)


cmdString = "EXEC dbo.SprzedazPoAdresieDostawy @groups = @table, @address = address, @city = city, @startDate = startDate, @endDate = endDate"

Set objRec = objConn.Execute(cmdString)

If objRec.EOF = True Then
    Wylicz = 0
Else
    Wylicz = objRec!ACTINDX
End If
objConn.Close
End Function

=Wylicz(C1;D1;"2024-04-01";"2024-04-30")

Public Function Wylicz(addressCell As String, cityCell As String, startDate As String, endDate As String)

Dim address As String
Dim city As String

address = Replace(addressCell, " ", "%")
city = Replace(cityCell, " ", "%")

Dim objRec As ADODB.Recordset
Dim objConn As ADODB.Connection
Dim objCmd As ADODB.Command
Dim ConnectionString As String
Dim cmdString As String

Set objRec = New ADODB.Recordset
Set objConn = New ADODB.Connection
Set objCmd = New ADODB.Command

ConnectionString = "Provider=SQLOLEDB;Data source=Serwer;Initial catalog=Baza;User ID=sa;Password=haslo"
objConn.Open ConnectionString

objCmd.ActiveConnection = objConn
objCmd.CommandType = adCmdText

cmdString = "DECLARE @table AS TGroups"

objCmd.CommandText = cmdString
objCmd.Execute , , adExecuteNoRecords
MsgBox ("Declare")

cmdString = "INSERT INTO @table (groupName) VALUES ('Grupa 1')"
objCmd.CommandText = cmdString
objCmd.Execute , , adExecuteNoRecords
MsgBox ("1 INSERT")

cmdString = "INSERT INTO @table (groupName) VALUES ('Grupa 2')"
objCmd.CommandText = cmdString
objCmd.Execute , , adExecuteNoRecords
MsgBox ("2 INSERT")

cmdString = "INSERT INTO @table (groupName) VALUES ('Grupa 3')"
objCmd.CommandText = cmdString
objCmd.Execute , , adExecuteNoRecords
MsgBox ("3 INSERT")

cmdString = "EXEC dbo.SprzedazPoAdresieDostawy @groups = @table, @address = address, @city = city, @startDate = startDate, @endDate = endDate"
MsgBox (cmdString)
Set objRec = objConn.Execute(cmdString)

If objRec.EOF = True Then
    Wylicz = 0
    'MsgBox (0)
Else
    Wylicz = objRec!ACTINDX
    'MsgBox (objRec!ACTINDX)
End If
objConn.Close
End Function