Call store procedure : Stored Procedure « Access « VBA / Excel / Access / Word

1. VBA / Excel / Access / Word
2. Access
3. Stored Procedure

Call store procedure

 

Public Sub CallStoredProcedure()
  Const ConnectionString As String = "Provider=SQLOLEDB.1;Integrated Security=SSPI;" + _
    "Persist Security Info=False;Initial Catalog=NorthwindCS;Data Source=LAP800;Workstation ID=LAP800;"
  
  Dim Command As Command
  Set Command = New Command
  
  Command.ActiveConnection = ConnectionString
  Command.CommandText = "[Sales by Year]"
  Command.CommandType = CommandTypeEnum.adCmdStoredProc
  
  Dim BeginningDate As ADODB.Parameter
  Dim EndingDate As ADODB.Parameter
  
  Dim StartDate As Date
  StartDate = #1/1/1995#
  
  Dim EndDate As Date
  EndDate = #1/1/2004#
  
  Set BeginningDate = Command.CreateParameter("@Beginning_Date",DataTypeEnum.adDate, ParameterDirectionEnum.adParamInput, , StartDate)
  Set EndingDate = Command.CreateParameter("@Ending_Date",DataTypeEnum.adDate, ParameterDirectionEnum.adParamInput, , EndDate)
  Call Command.Parameters.Append(BeginningDate)
  Call Command.Parameters.Append(EndingDate)
  Dim Recordset As ADODB.Recordset
  Set Recordset = Command.Execute
  Call Sheet1.Range("A1").CopyFromRecordset(Recordset)
End Sub

 

Related examples in the same category