在 Access 中尝试 CREATE VIEW 会出现“CREATE TABLE 语句中的语法错误"； [英] Attempting CREATE VIEW in Access gives "Syntax error in CREATE TABLE statement"

问题描述

我输入此代码以在预先创建的数据库中创建视图:

I typed this code to create a view in a pre created database:

CREATE VIEW NHTrips AS
SELECT TripID, TripName, StartLocation, State, Distance, MaxGrpSize, Type, Season
FROM Trip
WHERE State = 'NH' 
;

当我尝试运行 Access(2007) 时，返回一条错误消息:CREATE TABLE 语句中的语法错误."

When I try to run Access(2007) responds with a an error message: "Syntax error in CREATE TABLE statement."

为什么?

推荐答案

当您从 ADO/OleDb 执行 CREATE VIEW 时，Access 支持它.此代码段有效，因为 CurrentProject.Connection 是一个 ADO 对象...

Access supports CREATE VIEW when you execute it from ADO/OleDb. This code snippet works because CurrentProject.Connection is an ADO object ...

Dim strSql As String
strSql = "CREATE VIEW NHTrips AS" & vbCrLf & _
    "SELECT TripID, TripName, StartLocation, State, Distance, MaxGrpSize, Type, Season" & vbCrLf & _
    "FROM Trip" & vbCrLf & _
    "WHERE State = 'NH';"
CurrentProject.Connection.Execute strSql

然而，尝试从 DAO 执行相同的语句会触发错误 #3290 CREATE TABLE 语句中的语法错误." ...

However attempting to execute the same statement from DAO triggers error #3290 "Syntax error in CREATE TABLE statement." ...

CurrentDb.Execute strSql ' CurrentDb refers to a DAO Database object

这意味着如果您尝试从查询设计器执​​行该语句，您将得到相同的错误，因为它使用了 DAO.

That means you will get the same error if you attempt to execute that statement from the query designer because it uses DAO.

如果您可以使用 CREATE VIEW 以外的其他东西，请考虑使用 CreateQueryDef 方法来创建带有 SQL SELECT 语句的查询...

If you can use something other than CREATE VIEW, consider using the CreateQueryDef method to create your query with the SQL SELECT statement ...

strSql = "SELECT TripID, TripName, StartLocation, State, Distance, MaxGrpSize, Type, Season" & vbCrLf & _
    "FROM Trip" & vbCrLf & _
    "WHERE State = 'NH';"
CurrentDb.CreateQueryDef "NHTrips", strSql

这篇关于在 Access 中尝试 CREATE VIEW 会出现“CREATE TABLE 语句中的语法错误"；的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！