How to create View in Access 2000 using Jet SQL?

[lu_yanfeng]

Can any body tell me how to create a View in Access using Jet SQL? And where
to get the detailed specific reference for Jet SQL?

 

[Albert D. Kallal]

Any query you save in the query tab is essentially the same as a view. You
can also create queries on queries.

Since we can also save update queries, delete querys etc in the queris tab,
then it would be wrong to have that tab named as "views".

However, for all intensive purposes, a saved query is the same as a view.

As for a sql reference. There is one in the help...

 

[lu_yanfeng]

Maybe I didn't express my problem clearly, and I'm sorry for my poor
English.

In fact, I'd known that Query is the equivalent to View in MS-Access. And I
just wanna create a View/Query by writting SQL DDL statements, not by normal
operation.

However, I found that Jet SQL does not support the 'Create View' statement
after I had a try. And I was puzzled by whether it doest not support this
statement which was demanded in SQL Standard, or there is another
Jet-specific statement to create a View. Can anybody help me? Thanks a lot!

 

[david epsom dot com dot au]

However, I found that Jet SQL does not support the 'Create View'

'JET SQL' has more features than the old SQL standard,
but fewer features than the new SQL standard. If you
want to use CREATE VIEW, you can't use 'JET SQL'.
Fortunately, JET also supports a dialect called 'ANSI SQL'.

To use the ANSI SQL dialect, you must use an ADO connection.
If you use DAO or the Access IDE, you get 'JET SQL'
rather than 'ANSI SQL'

(david)

 

[Albert D. Kallal]

Sure, the sql does. Try the following:

dim strMySql as string

strMySql = "create view myview2 as select id from tblFruits"

CurrentProject.Connection.Execute strMySql

If you look in the query window, you will indeed see the query myview2 has
been added.

You can drop the view as:

CurrentProject.Connection.Execute "drop view myview2"

You have to use the ado object to do this...but most ddl statements will
work...

 

[Albert D. Kallal]

'JET SQL' has more features than the old SQL standard,
but fewer features than the new SQL standard. If you
want to use CREATE VIEW, you can't use 'JET SQL'.
Fortunately, JET also supports a dialect called 'ANSI SQL'.

To use the ANSI SQL dialect, you must use an ADO connection.
If you use DAO or the Access IDE, you get 'JET SQL'
rather than 'ANSI SQL'

Yes, you are 100% correct!

You could type into the debug window those commands.

In the debug window you can go:

CurrentProject.Connection.Execute "create view myview2 as select id from
tblFruits"

Not quite a interactive prompt window...but it does work!

The above also works well in code....