sql - ERROR in CREATE VIEW

Question

I tried to create a new view in my MS Access database so I can select better from it but I wonder what's happening here.

CREATE VIEW new 
AS 
  SELECT msthread.id, 
         msthread.threadname, 
         Count(msthread.threadname) AS TotalPost, 
         threadcategory 
  FROM   msthread 
         LEFT OUTER JOIN msposts 
                      ON msthread.threadname = msposts.threadname 
  GROUP  BY msthread.id, 
            msthread.threadname, 
            msthread.threadcategory 

Access gives me this error message when I try to execute that statement.

Syntax error in create table statement

Is there specific problems in creating view with JOINs? I'm trying to access 2 tables.

Answer 1

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;