Create VIEW

[Man Utd]

Can I create a view by using the 'Queries' ?

I got an error 'Syntax error in CREATE TABLE statement' in the following
statement:

create view viewCompanyName([Company Name]) as
select [Company Name] from Preferences

 

[Van T. Dinh]

If you are talking about MDB (not ADP), no.

CREATE VIEW is only supported in JET 4 and you need to use the JET OLE DB
Provider and ADO. In Queries / Access interface, CREATE VIEW will error
out.

 

[Man Utd]

So I cannot create a view using the 'Queries' inside Access ?

If you are talking about MDB (not ADP), no.

CREATE VIEW is only supported in JET 4 and you need to use the JET OLE DB
Provider and ADO. In Queries / Access interface, CREATE VIEW will error
out.

--
HTH
Van T. Dinh
MVP (Access)

Can I create a view by using the 'Queries' ?

I got an error 'Syntax error in CREATE TABLE statement' in the following
statement:

create view viewCompanyName([Company Name]) as
select [Company Name] from Preferences

 

[Van T. Dinh]

Not through the Access Query interface. I haven't tested this but you may
be able to use ADO code to execute a Query that has "CREATE VIEW ..."

OTOH, Views are not that useful in MDBs. For example, Views are not exposed
as saved Queries in the "Queries" Container window. Most developers still
use Queries and QueryDef to create Queries by code in MDBs.

 

[Brendan Reynolds]

As Van suggests, you can do it with ADO ...

Public Sub TestSub3()

Dim strSQL As String

strSQL = "CREATE VIEW viewCategoryName(CategoryName) AS SELECT
CategoryName FROM Categories"
CurrentProject.Connection.Execute strSQL

End Sub

Tested in Access 2003, haven't tested in any earlier version.

 

[Van T. Dinh]

It sounds to me that Man United actually wants to create a saved Query with
the SQL String "CREATE VIEW ..." in the saved Query and then use ADO to
execute the saved Query (to create the View).

I just did a quick test in A2002. It is not possible to save the Query with
the SQL String "CREATE VIEW ...". Access will parse the SQL String which
errors out and therefore the Query cannot be saved.

Using the SQL String in ADO code is fine in A2002.