创建视图时出错 [英] ERROR in CREATE VIEW

问题描述

我试图在MS Access数据库中创建一个新视图，以便可以从中选择更好的视图，但是我不知道这里发生了什么.

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会给我此错误消息.

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

创建表语句中的语法错误

Syntax error in create table statement

使用JOIN创建视图时是否存在特定问题?我正在尝试访问2个表.

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

推荐答案

CREATE VIEW在Access 2000中随Jet 4引入.但是您必须从ADO/OleDb执行该语句.如果从DAO执行，则会触发错误3290，"CREATE TABLE语句中的语法错误" ，这比有用的方法更令人困惑.

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.

也CREATE VIEW只能创建简单的SELECT查询.对于CREATE VIEW无法处理的任何内容，请使用CREATE PROCEDURE.

Also CREATE VIEW can only create simple SELECT queries. Use CREATE PROCEDURE for any which CREATE VIEW can't handle.

但是CREATE VIEW应该处理您的问题.我使用了一个字符串变量来保存下面的DDL语句，然后在Access会话中从CurrentProject.Connection执行它:

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

之所以有效，是因为CurrentProject.Connection是ADO对象.如果要从Access外部进行此操作，请使用OleDb连接.

That worked because CurrentProject.Connection is an ADO object. If you will be doing this from outside Access, use an OleDb connection.

注意，我对您的查询进行了一些更改.大多数是未成年人.但是我认为更改查询名称可能很重要. New是保留字，所以我选择了qryNew.在从ADO/OleDb运行的查询中，保留字作为对象名显得特别麻烦.

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;

这篇关于创建视图时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！