Xin chào mọi người,
Hiện tại em đang sử dụng access để làm database, em dùng ADO để truy vấn dữ liệu từ access về. Em đang gặp lỗi Error Method Open Recordset khi sử dụng câu truy vấn có chứa UNION.
Liệu ADO có cho phép sử dụng câu query đó không ạ? Nhờ mọi người xem thử giúp em với ạ.
Em cám ơn mọi người!
Hiện tại em đang sử dụng access để làm database, em dùng ADO để truy vấn dữ liệu từ access về. Em đang gặp lỗi Error Method Open Recordset khi sử dụng câu truy vấn có chứa UNION.
Liệu ADO có cho phép sử dụng câu query đó không ạ? Nhờ mọi người xem thử giúp em với ạ.
Em cám ơn mọi người!
Mã:
Sub R_FOLIO_SUMMARY()
On Error Resume Next
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Sheet4.Range("A7:L1000000").ClearContents
Dim cnn As ADODB.Connection
Set cnn = New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim strcnn As String
Dim sqlstr As String
Dim lr As Long
FrP = Sheet10.Range("E12").Value
ToP = Sheet10.Range("E13").Value
strcnn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "\REV_DATABASE.accdb;" & "Jet OLEDB:Database Password = "
cnn.Open strcnn
sqlstr = "SELECT Sum(A.RevAmt), Sum(A.SvcAmt), Sum(A.ScTAmt), Sum(A.TaxAmt), Sum(A.TipAmt), Sum(A.PaidAmt), A.Conf_No, A.Room_No, A.Arrival_Date, A.Departure_Date" & _
" FROM " & _
" (SELECT Trans_Amount as RevAmt, 0 as SvcAmt, 0 as ScTAmt, 0 as TaxAmt, 0 as TipAmt, 0 as PaidAmt, Conf_No, Room_No, Arrival_Date, Departure_Date" & _
" FROM FOLIO_DETAIL WHERE LEFT(Acc_Code, 4) = '5113'" & _
" UNION ALL SELECT 0 as RevAmt, Trans_Amount as SvcAmt, 0 as ScTAmt, 0 as TaxAmt, 0 as TipAmt, 0 as PaidAmt, Conf_No, Room_No, Arrival_Date, Departure_Date" & _
" FROM FOLIO_DETAIL WHERE LEFT(Acc_Code, 4) = '5114'" & _
" UNION ALL SELECT 0 as RevAmt, 0 as SvcAmt, Trans_Amount as ScTAmt, 0 as TaxAmt, 0 as TipAmt, 0 as PaidAmt, Conf_No, Room_No, Arrival_Date, Departure_Date" & _
" FROM FOLIO_DETAIL WHERE LEFT(Acc_Code, 4) = '3332'" & _
" UNION ALL SELECT 0 as RevAmt, 0 as SvcAmt, 0 as ScTAmt, Trans_Amount as ScTAmt, 0 as TipAmt, 0 as PaidAmt, Conf_No, Room_No, Arrival_Date, Departure_Date" & _
" FROM FOLIO_DETAIL WHERE LEFT(Acc_Code, 4) = '3331'" & _
" UNION ALL SELECT 0 as RevAmt, 0 as SvcAmt, 0 as ScTAmt, 0 as ScTAmt, Trans_Amount as TipAmt, 0 as PaidAmt, Conf_No, Room_No, Arrival_Date, Departure_Date" & _
" FROM FOLIO_DETAIL WHERE LEFT(Acc_Code, 3) = '338'" & _
" UNION ALL SELECT 0 as RevAmt, 0 as SvcAmt, 0 as ScTAmt, 0 as ScTAmt, 0 as TipAmt, Trans_Amount as PaidAmt, Conf_No, Room_No, Arrival_Date, Departure_Date" & _
" FROM FOLIO_DETAIL WHERE CAST(LEFT(Folio_Trans, 1) as INT) >= 8) as A" & _
" GROUP BY A.Conf_No, A.Room_No, A.Arrival_Date, A.Departure_Date"
rs.Open sqlstr, strcnn
Sheet4.Range("A7").CopyFromRecordset rs
rs.Close
cnn.Close
Set rs = Nothing
Set cnn = Nothing
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
End Sub
Lần chỉnh sửa cuối:
