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

Mục liên quan:

0935999617