Create View statement

[tblair23221]

Does any version on MS Access support the use of the "CREATE VIEW" SQL statement. From what I have read on various MS websites it is supported via MS Jet 4.0 but whenever I try to run a statement such as:

CREATE VIEW vPeopleTN AS SELECT * FROM People WHERE STATE = "TN";

I get an error stating "Syntax error in CREATE TABLE statement. Does Access only allow you to create a VIEW if you are using an Access Project (connecting to a SQL Server DB). Thanks for any help.

 

[cmmrfrds]

The Views are stored in SQL Server, so, it seems that Microsoft would not want views also stored in an MDB, but I could be wrong.

There is a syntax error in the statement - not a valid sql server select statement.
CREATE VIEW vPeopleTN AS SELECT * FROM People WHERE STATE = "TN";

single quotes in sql server for a literal
'TN'

 

[chillay]

Microdoft Access Jet does not support views.

chillay

 

[r937]

microsoft access does not support views, but it does support something which is pretty much a complete equivalent -- saved queries

run your query to make sure it's working the way you want:

[tt]SELECT * FROM People WHERE STATE = 'TN'[/tt]

save this query as [tt]vPeopleTN[/tt]

now run the following:

[tt]SELECT * FROM vPeopleTN[/tt]

tada!

i have used saved queries successfully for much more complicated situations, e.g. ones which get around the fact that access does not support COUNT DISTINCT

note: the saved query does not contain data, it just contains the query's sql statement -- when you write a query against a saved query, the access engine "merges" the sql and runs the combined result


rudy