Vba Access kết nối với Store Procedure

https://stackoverflow.com/questions/12407389/how-to-return-values-from-a-sql-server-stored-procedure-and-utilise-them-in-acce
https://youtu.be/a-uylSs8-CM

Excel vba https://youtu.be/XPQJTn1gnlU

B1: viết 1 PROCEDURE
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        <Author,,Name>
-- Create date: <Create Date,,>
-- Description:    <Description,,>
-- =============================================
 [dbo].[test] 0
ALTER PROCEDURE [dbo].[test]
    @ID_CongTy int
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    SELECT * From tbBangGia Where ID_CongTy=@ID_CongTy
END

B2: viết code VBA

    Dim Conn As New ADODB.Connection
    Dim rcs As New ADODB.Recordset

    Dim strCn As String
    strCn = "PROVIDER=SQLOLEDB;SERVER=HONGNHAT\SQLEXPRESS;DATABASE=tai;UID=sa;PWD=123"

    Dim cn As New ADODB.Connection
    cn.ConnectionString = strCn
    cn.CommandTimeout = 15
    cn.Mode = adModeReadWrite
    cn.Open
    
    Dim cmd As New ADODB.Command
    With cmd
        .ActiveConnection = cn
        .CommandType = adCmdStoredProc
        .CommandTimeout = 60
        .CommandText = "dbo.test"
        .Parameters.Append .CreateParameter("ID_CongTy", adInteger, adParamInput, , 0)
    End With
    
 
    rcs.CursorType = adOpenKeyset
    rcs.LockType = adLockBatchOptimistic
    rcs.Open cmd
    
    
    Set rcs = cmd.Execute
    
    Do While (Not rcs Is Nothing)
        If rcs.State = adStateClosed Then Exit Do
        While Not rcs.EOF
            'For i = 0 To rcs.Fields.Count - 1
            '    rStr = rStr & " : " & rcs(i)
           ' Next i
            MsgBox rcs!TenBangGia
            
            rcs.MoveNext
          
        Wend
        Debug.Print "----------------------"
        Set ADORs = rcs.NextRecordset
    Loop
    
    
   
    
    Set cmd = Nothing
    
   cn.Close

** Gọi hàm có tham số là nvarchar

Option Compare Database
Option Explicit

Private Sub Command0_Click()
    Dim Conn As New ADODB.Connection
    Dim rcs As New ADODB.Recordset

    Dim strCn As String
    strCn = "PROVIDER=SQLOLEDB;SERVER=LAPTOP-1UAIHGHP\SQLEXPRESS;DATABASE=hoso;UID=sa;PWD=123"

    Dim cn As New ADODB.Connection
    cn.ConnectionString = strCn
    cn.CommandTimeout = 30
    cn.Mode = adModeReadWrite
    cn.Open
    
    Dim cmd As New ADODB.Command
    With cmd
        .ActiveConnection = cn
        .CommandType = adCmdStoredProc
        .CommandTimeout = 60
        .CommandText = "dbo.getHoSo"
     End With
     Dim prm As Object
    
     Set prm = cmd.CreateParameter("Lop", adVarChar, adParamInput)
     Dim giatri As String
     giatri = "1"
         
    prm.Size = Len(giatri)

    cmd.Parameters.Append prm
    cmd.Parameters("Lop") = giatri
     
    rcs.CursorType = adOpenKeyset
    rcs.LockType = adLockBatchOptimistic
    rcs.Open cmd
    
    
    Set rcs = cmd.Execute
    
    Do While (Not rcs Is Nothing)
        If rcs.State = adStateClosed Then Exit Do
        While Not rcs.EOF
            'For i = 0 To rcs.Fields.Count - 1
            '    rStr = rStr & " : " & rcs(i)
           ' Next i
            MsgBox rcs!ten
            
            rcs.MoveNext
          
        Wend
        Debug.Print "----------------------"
        Set rcs = rcs.NextRecordset
    Loop
    
    
   
    
    Set cmd = Nothing
    
   cn.Close
End Sub
 

** với produce là

USE [HoSo]
GO
/****** Object:  StoredProcedure [dbo].[getHoSo]    Script Date: 08/04/2023 20:15:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        <Author,,Name>
-- Create date: <Create Date,,>
-- Description:    <Description,,>
-- =============================================
-- [getHoSo] '1'
ALTER PROCEDURE [dbo].[getHoSo]
    @Lop nvarchar(50)   
AS
BEGIN
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    
    SELECT * From tbHoSo Where Lop=@Lop

    

END
 

*** Viết SQL

Dim Conn As New ADODB.Connection
    Dim rcs As New ADODB.Recordset

    Dim strCn As String
    strCn = "PROVIDER=SQLOLEDB;SERVER=LAPTOP-1UAIHGHP\SQLEXPRESS;DATABASE=hoso;UID=sa;PWD=123"

    Dim cn As New ADODB.Connection
    cn.ConnectionString = strCn
    cn.CommandTimeout = 30
    cn.Mode = adModeReadWrite
    cn.Open
    
    Dim cmd As New ADODB.Command
    With cmd
        .ActiveConnection = cn
        .CommandType = adCmdText
        .CommandTimeout = 60
        .CommandText = "Select * From dbo.tbHoSo"
     End With
     Dim prm As Object
     
    rcs.CursorType = adOpenKeyset
    rcs.LockType = adLockBatchOptimistic
    rcs.Open cmd
    
    
    Set rcs = cmd.Execute
    
    Do While (Not rcs Is Nothing)
        If rcs.State = adStateClosed Then Exit Do
        While Not rcs.EOF
            'For i = 0 To rcs.Fields.Count - 1
            '    rStr = rStr & " : " & rcs(i)
           ' Next i
            MsgBox rcs!ten
            
            rcs.MoveNext

*** Xóa bảng ghi

 Dim Conn As New ADODB.Connection
    Dim rcs As New ADODB.Recordset

    Dim strCn As String
    strCn = "PROVIDER=SQLOLEDB;SERVER=LAPTOP-1UAIHGHP\SQLEXPRESS;DATABASE=hoso;UID=sa;PWD=123"

    Dim cn As New ADODB.Connection
    cn.ConnectionString = strCn
    cn.CommandTimeout = 30
    cn.Mode = adModeReadWrite
    cn.Open
    
    Dim cmd As New ADODB.Command
    With cmd
        .ActiveConnection = cn
        .CommandType = adCmdText
        .CommandTimeout = 60
        .CommandText = "Delete from tbHoSo"
     End With
     
    rcs.CursorType = adOpenKeyset
    rcs.LockType = adLockBatchOptimistic
    rcs.Open cmd
    
    
   Set rcs = cmd.Execute
    
    
    
    
   
    
    Set cmd = Nothing
    
   cn.Close

          
        Wend
        Debug.Print "----------------------"
        Set rcs = rcs.NextRecordset
    Loop
    
    
   
    
    Set cmd = Nothing
    
   cn.Close