VBA hỗ trợ câu lệnh truy vấn SQL tối đa bao nhiêu dòng (1 người xem)

LEHOC · 19/7/20

Em chào mọi người.
Em có vấn đề với VBA khi truy vấn SQL bị lỗi "Operation is not allowed when the object is closed."
Câu truy vấn của em trên SQL như sau:

PHP:

DECLARE @AccountObjectTaxCode NVARCHAR(MAX)
DECLARE @AccountObjectGroupList NVARCHAR(MAX)
SELECT
    RefID,
    AccountObjectID,
    @AccountObjectTaxCode AS AccObjCode,
    @AccountObjectGroupList AS AccObjGrLst,
    InvSeries,
    InvNo,
    InvDate,
    TotalAmount
INTO #tbSAReturn
FROM SAReturn A
UPDATE #tbSAReturn
    SET #tbSAReturn.AccObjCode = C.AccountObjectCode FROM AccountObject C WHERE #tbSAReturn.AccountObjectID = C.AccountObjectID
UPDATE #tbSAReturn
    SET #tbSAReturn.AccObjGrLst = C.AccountObjectGroupList FROM AccountObject C WHERE #tbSAReturn.AccountObjectID = C.AccountObjectID
SELECT
    AccObjCode,
    InvSeries,
    InvNo,
    InvDate,
    TotalAmount
FROM #tbSAReturn
WHERE AccObjGrLst LIKE '%;/3/7/%'

Em đã chạy câu truy vấn trên SQL Server thì chạy được, nhưng em truy vấn trong VBA thì bị lỗi tại dòng:

PHP:

 ShResultDataReturn.Cells(1, "A").CopyFromRecordset rs

em gửi a/c mã VBA em đã chạy.

PHP:

Sub ADOExcelSQLServer3()

    Dim Cn As ADODB.Connection
    Dim Server_Name As String
    Dim Database_Name As String
    Dim User_ID As String
    Dim Password As String
    Dim SQLStr1 As String
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset

    Set ShGetServer = Sheets("Conn_SQL_Data")
    Set ShResultDataReturn = Sheet1

    Server_Name = ShGetServer.Cells(3, "B")
    Database_Name = ShGetServer.Cells(4, "B")
    User_ID = ShGetServer.Cells(5, "B")
    Password = ShGetServer.Cells(6, "B")

    SQLStr1 = "DECLARE @AccountObjectTaxCode NVARCHAR(MAX)" & _
             " DECLARE @AccountObjectGroupList NVARCHAR(MAX)" & _
             " SELECT" & _
                " RefID," & _
                " AccountObjectID," & _
                " @AccountObjectTaxCode AS AccObjCode," & _
                " @AccountObjectGroupList AS AccObjGrLst," & _
                " InvSeries," & _
                " InvNo," & _
                 " InvDate," & _
                " TotalAmount" & _
             " INTO #tbSAReturn" & _
             " FROM SAReturn A" & _
             " UPDATE #tbSAReturn" & _
                " SET #tbSAReturn.AccObjCode = C.AccountObjectCode FROM AccountObject C" & _
                " WHERE #tbSAReturn.AccountObjectID = C.AccountObjectID" & _
            " UPDATE #tbSAReturn" & _
                " SET #tbSAReturn.AccObjGrLst = C.AccountObjectGroupList FROM AccountObject C" & _
                " WHERE #tbSAReturn.AccountObjectID = C.AccountObjectID" & _
            " SELECT" & _
                   " AccObjCode," & _
                   " InvSeries," & _
                   " InvNo," & _
                   " InvDate," & _
                   " TotalAmount" & _
                " FROM #tbSAReturn" & _
                " WHERE AccObjGrLst LIKE '%;/3/7/%'"
    Set Cn = New ADODB.Connection
    Cn.Open "Driver={SQL Server};Server=" & Server_Name & ";Database=" & Database_Name & _
    ";Uid=" & User_ID & ";Pwd=" & Password & ";"

    rs.Open SQLStr1, Cn, adOpenStatic
    ShResultDataReturn.Cells(1, "A").CopyFromRecordset rs

    rs.Close
    Set rs = Nothing
    Cn.Close
    Set Cn = Nothing
End Sub

Cũng với phương pháp Truy vấn này em truy vấn bằng câu lệnh ngắn gọn và đơn giản hơn thì không sao cả.
Em đang suy đoán lỗi liên quan đến vấn đề VBA hỗ trợ độ dài câu truy vấn SQL, a/c cho em ý kiến với ạ.
Có phải trường hợp này thì cần chạy thủ tục trên SQL trước rồi trên VBA chỉ gọi lại thủ tục mới được.
Em cảm ơn!

ongke0711 · 19/7/20

LEHOC đã viết:
Em chào mọi người.
Em có vấn đề với VBA khi truy vấn SQL bị lỗi "Operation is not allowed when the object is closed."
...
Cũng với phương pháp Truy vấn này em truy vấn bằng câu lệnh ngắn gọn và đơn giản hơn thì không sao cả.
Em đang suy đoán lỗi liên quan đến vấn đề VBA hỗ trợ độ dài câu truy vấn SQL, a/c cho em ý kiến với ạ.
Có phải trường hợp này thì cần chạy thủ tục trên SQL trước rồi trên VBA chỉ gọi lại thủ tục mới được.

Câu lệnh SQL dài thì không bị ảnh hưởng gì nhưng theo tôi là do bạn thực thi mấy action query trong cùng câu lệnh nên ADO nó không thực hiện.
Bạn thử tách ra chạy từng action query (Insert, update) xong rồi lấy recordset sau.

thuyyeu99 · 19/7/20

LEHOC đã viết:

Em chào mọi người.
Em có vấn đề với VBA khi truy vấn SQL bị lỗi "Operation is not allowed when the object is closed."
Câu truy vấn của em trên SQL như sau:

PHP:

DECLARE @AccountObjectTaxCode NVARCHAR(MAX)
DECLARE @AccountObjectGroupList NVARCHAR(MAX)
SELECT
    RefID,
    AccountObjectID,
    @AccountObjectTaxCode AS AccObjCode,
    @AccountObjectGroupList AS AccObjGrLst,
    InvSeries,
    InvNo,
    InvDate,
    TotalAmount
INTO #tbSAReturn
FROM SAReturn A
UPDATE #tbSAReturn
    SET #tbSAReturn.AccObjCode = C.AccountObjectCode FROM AccountObject C WHERE #tbSAReturn.AccountObjectID = C.AccountObjectID
UPDATE #tbSAReturn
    SET #tbSAReturn.AccObjGrLst = C.AccountObjectGroupList FROM AccountObject C WHERE #tbSAReturn.AccountObjectID = C.AccountObjectID
SELECT
    AccObjCode,
    InvSeries,
    InvNo,
    InvDate,
    TotalAmount
FROM #tbSAReturn
WHERE AccObjGrLst LIKE '%;/3/7/%'

Em đã chạy câu truy vấn trên SQL Server thì chạy được, nhưng em truy vấn trong VBA thì bị lỗi tại dòng:

PHP:

 ShResultDataReturn.Cells(1, "A").CopyFromRecordset rs

em gửi a/c mã VBA em đã chạy.

PHP:

Sub ADOExcelSQLServer3()

    Dim Cn As ADODB.Connection
    Dim Server_Name As String
    Dim Database_Name As String
    Dim User_ID As String
    Dim Password As String
    Dim SQLStr1 As String
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset

    Set ShGetServer = Sheets("Conn_SQL_Data")
    Set ShResultDataReturn = Sheet1

    Server_Name = ShGetServer.Cells(3, "B")
    Database_Name = ShGetServer.Cells(4, "B")
    User_ID = ShGetServer.Cells(5, "B")
    Password = ShGetServer.Cells(6, "B")

    SQLStr1 = "DECLARE @AccountObjectTaxCode NVARCHAR(MAX)" & _
             " DECLARE @AccountObjectGroupList NVARCHAR(MAX)" & _
             " SELECT" & _
                " RefID," & _
                " AccountObjectID," & _
                " @AccountObjectTaxCode AS AccObjCode," & _
                " @AccountObjectGroupList AS AccObjGrLst," & _
                " InvSeries," & _
                " InvNo," & _
                 " InvDate," & _
                " TotalAmount" & _
             " INTO #tbSAReturn" & _
             " FROM SAReturn A" & _
             " UPDATE #tbSAReturn" & _
                " SET #tbSAReturn.AccObjCode = C.AccountObjectCode FROM AccountObject C" & _
                " WHERE #tbSAReturn.AccountObjectID = C.AccountObjectID" & _
            " UPDATE #tbSAReturn" & _
                " SET #tbSAReturn.AccObjGrLst = C.AccountObjectGroupList FROM AccountObject C" & _
                " WHERE #tbSAReturn.AccountObjectID = C.AccountObjectID" & _
            " SELECT" & _
                   " AccObjCode," & _
                   " InvSeries," & _
                   " InvNo," & _
                   " InvDate," & _
                   " TotalAmount" & _
                " FROM #tbSAReturn" & _
                " WHERE AccObjGrLst LIKE '%;/3/7/%'"
    Set Cn = New ADODB.Connection
    Cn.Open "Driver={SQL Server};Server=" & Server_Name & ";Database=" & Database_Name & _
    ";Uid=" & User_ID & ";Pwd=" & Password & ";"

    rs.Open SQLStr1, Cn, adOpenStatic
    ShResultDataReturn.Cells(1, "A").CopyFromRecordset rs

    rs.Close
    Set rs = Nothing
    Cn.Close
    Set Cn = Nothing
End Sub

Cũng với phương pháp Truy vấn này em truy vấn bằng câu lệnh ngắn gọn và đơn giản hơn thì không sao cả.
Em đang suy đoán lỗi liên quan đến vấn đề VBA hỗ trợ độ dài câu truy vấn SQL, a/c cho em ý kiến với ạ.
Có phải trường hợp này thì cần chạy thủ tục trên SQL trước rồi trên VBA chỉ gọi lại thủ tục mới được.
Em cảm ơn!

mình thấy bạn viết code nhìn vào chống mặt luôn đó, còn nếu theo tiêu đề ban 5 test thêm giơi hạn thì biết mà. mà làm gì thì làm đừng vượt quá giới hạn của excel tuỳ theo vesion

Vô danh Tiểu tốt · 19/7/20

Tôi từng làm mấy cái query dài lằng nhằng hơn đó nhiều nhưng vẫn chạy phà phà nên nhiêu đó không có gì khó hiểu với driver của ADO đâu. Sớc trên gu gồ thì có một cái đề nghị rất đáng chú ý đó là thêm dòng " SET NOCOUNT ON;" ở trên cùng. Nếu là bạn tôi sẽ thử liền.

Giải pháp nữa đơn giản là tách cái "SELECT..." cuối ra thành một query riêng còn những lệnh trên cứ gom thành bó cũng không sao. Tôi lúc trước làm trên Access toàn mơ có ngày chạy được nhiều lệnh khác nhau trong cùng một query như SQL-Server mà có được đâu.

Vì chưa bao giờ làm việc với SQLServer, nên những ý này của tôi toàn là đoán mò mong các bạn cảm thông nếu thấy tôi phán ngu --=0

VBA hỗ trợ câu lệnh truy vấn SQL tối đa bao nhiêu dòng (1 người xem)

Người dùng đang xem chủ đề này

LEHOC

Thành viên chính thức

ongke0711

Thành viên gắn bó

thuyyeu99

Trùm Nhiều Chuyện

Vô danh Tiểu tốt

Thành viên tiêu biểu

Bài viết mới nhất

Thành viên có số lượng bài viết cao nhất tháng

Thành viên có điểm tương tác cao nhất tháng

Đếm ngược thời gian