View in Access Database 2007

Question

PARAMETERS [prmAuto] Long;
CREATE VIEW view (IndexItemCaption) AS
SELECT IndexItemCaption FROM IndexItems WHERE Auto = prmAuto;

This create view doesn't work for me.

Does anyone know why?

Thanks!

Answer 1

See Microsoft's SQL Reference for the CREATE VIEW Statement Under the Remarks heading, it says:

The SELECT statement that defines the view cannot contain any parameters.

Use DAO to create your query instead. See Database.CreateQueryDef Method

And, as @InSane suggested, don't use "view" as the name of your query.

Answer 2

You cannot create a view in Access with a parameter (http://msdn.microsoft.com/en-us/library/bb177895(office.12).aspx). Furthermore, the create view statement will not run in the query design window unless you set SQL server compatible Syntax (ANSI 92).

Is VBA to create a query (view) an option form you?

s = "PARAMETERS [prmAuto] Long; " _
& "SELECT IndexItemCaption FROM IndexItems WHERE Auto = prmAuto;"

CurrentDb.CreateQueryDef "IndexItemCaption", s