CREATE VIEW
was introduced with Jet 4 in Access 2000. But you must execute the statement from ADO/OleDb. If executed from DAO, it triggers error 3290, "Syntax error in CREATE TABLE statement", which is more confusing than helpful.
Also CREATE VIEW
can only create simple SELECT
queries. Use CREATE PROCEDURE
for any which CREATE VIEW
can't handle.
But CREATE VIEW
should handle yours. I used a string variable to hold the DDL statement below, and then executed it from CurrentProject.Connection
in an Access session:
CurrentProject.Connection.Execute strSql
That worked because CurrentProject.Connection
is an ADO object. If you will be doing this from outside Access, use an OleDb connection.
Notice I made a few changes to your query. Most were minor. But I think the query name change may be important. New
is a reserved word so I chose qryNew
instead. Reserved words as object names seem especially troublesome in queries run from ADO/OleDb.
CREATE VIEW qryNew
AS
SELECT
mst.id,
mst.threadname,
mst.threadcategory,
Count(mst.threadname) AS TotalPost
FROM
msthread AS mst
LEFT JOIN msposts AS msp
ON mst.threadname = msp.threadname
GROUP BY
mst.id,
mst.threadname,
mst.threadcategory;
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…