CREATE VIEW?

[dwa]

How does one create a VIEW in Access/Jet?

The Access Jet referrence implies that you can do this using the CREATE VIEW
statement:

=====

Syntax
CREATE VIEW view [(field1[, field2[, ...]])] AS selectstatement

The CREATE VIEW statement has these parts:

Part Description
view The name of the view to be created.
field1, field2 The name of field or fields for the corresponding
fields specified in selectstatement.
selectstatement A SQL SELECT statement. For more information, see
SELECT Statement.


===

But when I try "CREATE VIEW TestView AS select * from sometable" from an
Access Query Window, I get this error:

"Syntax error in CREATE TABLE statement"

Can anyone shed any light on this mystery?

Thanks,

-- dwa

 

[Michel Walsh]

HI,


Definitively. You have to use ADO and the query designer is using DAO.
If you try:

CurrentProject.Connection.Execute "CREATE VIEW viewName AS SELECT ... "

in the Immediate-Debug window, in Access 2002 and later, you should see the
view among the queries. In Access 2000, a feature make it invisible, but you
can still use it. Note that a view, in Jet, is nothing much more than a
query without parameter.



Hoping it may help,
Vanderghast, Access MVP