Microsoft文档指出:
CREATE VIEW view [(field1[, field2[, ...]])] AS selectstatement
并列出了一些规定。我正在尝试使用ADODB
创建带有CREATE VIEW
语句的联合查询。联合本身我可以在 Access (2013( 中运行并保存(它显示它被保存为带有两个互锁小圆圈图标的联合查询(。但是 Microsoft.ACE.OLEDB.12.0 引擎抱怨子查询中不允许Unions
或者VIEWS
中只允许简单的SELECT
查询。我需要使用DAO
还是有不同的语法?我的创建视图语句是:
uString = "CREATE VIEW vwSpendingTotals (Spending, Name) as SELECT Spending, Name FROM " & _
" (SELECT Sum( tblA.Amount) as Spending, 'Auto' as Name from tblGeneral_Ledger gl " & _
" inner join tblAccount_AUTO as tblA on gl.Trans_ID = tblA.Trans_ID " & _
" where gl.Debit_Credit < 0 AND Trans_Date > DateSerial(Year(Now()),1,1) Union " & _
" SELECT Sum( tblH.Amount) as Spending, 'Household' as Name from tblGeneral_Ledger gl " & _
" inner join tblAccount_Household as tblH on gl.Trans_ID = tblH.Trans_ID " & _
" where gl.Debit_Credit < 0 AND Trans_Date > DateSerial(Year(Now()),1,1) union " & _
" SELECT Sum( tblL.Amount) as Spending, 'Living' as Name from tblGeneral_Ledger gl " & _
" inner join tblAccount_Living as tblL on gl.Trans_ID = tblL.Trans_ID " & _
" where gl.Debit_Credit < 0 AND Trans_Date > DateSerial(Year(Now()),1,1) union " & _
" SELECT Sum( tblM.Amount) as Spending, 'Medical' as Name from tblGeneral_Ledger gl " & _
" inner join tblAccount_Medical as tblM on gl.Trans_ID = tblM.Trans_ID " & _
" where gl.Debit_Credit < 0 AND Trans_Date > DateSerial(Year(Now()),1,1) union " & _
" SELECT Sum( tblP.Amount) as Spending, 'Personal' as Name from tblGeneral_Ledger gl " & _
" inner join tblAccount_Personal as tblP on gl.Trans_ID = tblP.Trans_ID " & _
" where gl.Debit_Credit < 0 AND Trans_Date > DateSerial(Year(Now()),1,1) union " & _
" SELECT Sum( tblU.Amount) as Spending, 'Utilities' as Name from tblGeneral_Ledger gl " & _
" inner join tblAccount_Utilities as tblU on gl.Trans_ID = tblU.Trans_ID " & _
" where gl.Debit_Credit < 0 AND Trans_Date > DateSerial(Year(Now()),1,1)) order by Name"
objConnection.Execute uString
objConnection
已在脚本的前面实例化。 是否应该更新Microsoft文档以反映 Jet 12 发动机返回的错误,还是我吠错了树?
提前感谢, 哈罗德
您是否在子查询后使用别名运行它:
uString = "CREATE VIEW vwSpendingTotals (Spending, Name) as SELECT Spending, Name FROM " & _
" (SELECT Sum( tblA.Amount) as Spending, 'Auto' as Name from tblGeneral_Ledger gl " & _
" inner join tblAccount_AUTO as tblA on gl.Trans_ID = tblA.Trans_ID " & _
" where gl.Debit_Credit < 0 AND Trans_Date > DateSerial(Year(Now()),1,1) Union " & _
" SELECT Sum( tblH.Amount) as Spending, 'Household' as Name from tblGeneral_Ledger gl " & _
" inner join tblAccount_Household as tblH on gl.Trans_ID = tblH.Trans_ID " & _
" where gl.Debit_Credit < 0 AND Trans_Date > DateSerial(Year(Now()),1,1) union " & _
" SELECT Sum( tblL.Amount) as Spending, 'Living' as Name from tblGeneral_Ledger gl " & _
" inner join tblAccount_Living as tblL on gl.Trans_ID = tblL.Trans_ID " & _
" where gl.Debit_Credit < 0 AND Trans_Date > DateSerial(Year(Now()),1,1) union " & _
" SELECT Sum( tblM.Amount) as Spending, 'Medical' as Name from tblGeneral_Ledger gl " & _
" inner join tblAccount_Medical as tblM on gl.Trans_ID = tblM.Trans_ID " & _
" where gl.Debit_Credit < 0 AND Trans_Date > DateSerial(Year(Now()),1,1) union " & _
" SELECT Sum( tblP.Amount) as Spending, 'Personal' as Name from tblGeneral_Ledger gl " & _
" inner join tblAccount_Personal as tblP on gl.Trans_ID = tblP.Trans_ID " & _
" where gl.Debit_Credit < 0 AND Trans_Date > DateSerial(Year(Now()),1,1) union " & _
" SELECT Sum( tblU.Amount) as Spending, 'Utilities' as Name from tblGeneral_Ledger gl " & _
" inner join tblAccount_Utilities as tblU on gl.Trans_ID = tblU.Trans_ID " & _
" where gl.Debit_Credit < 0 AND Trans_Date > DateSerial(Year(Now()),1,1))a order by Name"
objConnection.Execute uString