在Access中尝试CREATE VIEW会显示"CREATE TABLE语句中的语法错误".

本文介绍了在Access中尝试CREATE VIEW会显示"CREATE TABLE语句中的语法错误".的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我键入以下代码以在预创建的数据库中创建视图:

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支持CREATE VIEW.此代码段有效，因为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