All geek questions in one place

Attempting to 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' ;

When I try to start Access (2007), I get the error message: "Syntax error in the CREATE TABLE statement".

Why?

+3
source share
2 answers

Access supports CREATE VIEW when executed from ADO / OleDb. This piece of code 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

However, an attempt to execute the same instruction from triggers DAO # 3290 "Syntax error in the CREATE TABLE statement" ....

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

This means that you will get the same error if you try to execute this statement from the query designer, since it uses the DAO.

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

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

There is not much information about which database you are trying to create the view in. If you are trying to create a view in access, then you should think about all the requests that are in the access navigation bar like VIEWS.

They can be created using VBA by manipulating the QueryDefs object, see here and here.

0
source

Source: https://habr.com/ru/post/985749/

More articles:


All Articles