How do I use CREATE VIEW in Access 2003?

[Scott Ayliffe]

The HELP documentation gives me the EXACT syntax for how to do this. CREATE
and VIEW are even reserved words in the Jet Database. The DROP command even
lists tables, indexes, procedures and VIEWS as legal objects to drop.

There are no examples of code in the help menu and the only message I get is
a syntax error will trying to "Create Table".

Anyone having success with VIEW's in Access ?

 

[Allen Browne]

CREATE VIEW can be executed only through the ADO library.

Access itself uses DAO, so any attempt to CREATE VIEW from the query design
window will fail.

Try something like this:

Function CreateViewAdo()
Dim cn As ADODB.Connection
Dim strSql As String

strSql = "CREATE VIEW MyTableView AS SELECT MyTable.* FROM MyTable;"
Set cn = CurrentProject.Connection
cn.Execute strSql

Debug.Print "MyTableView created"
Set cn = Nothing
End Function

 

[Duane Hookom]

Keep in mind the view created by ADO code is not visible in the database
window. You can however create a query or other object based on the view.

 

[Allen Browne]

But the view does show up on the Queries tab of the Database window if you
refresh the window (e.g. by clicking on the Forms tab and back to the
Queries tab again).

It's interesting how Access parses the SQL statement when you read
CurrentDb().QueryDefs("MyTableView").SQL. It actually displays the CrLf
after each clause and the trailing CrLf at the end, just like CreateQuery().

 

[Duane Hookom]

I recently used ado from Excel 2000 to create some views in an Access 2000
mdb. The views never appeared in the database window.

 

[Scott Ayliffe]

Thanks for the tips, all...

I was very confused as to why they gave the syntax at ALL for Creating and
Dropping views if you can't do it in the SQL Window of Access (??).

Thanks again.

Scott in Texas

 

[Duane Hookom]

I just used ADO code this past Thursday to create a view from Excel in an
Access MDB. The Excel "solution" which automates some graphing will be sent
to many factories. This seemed like the easiest implementation that I could
think of.