How to create a view via sql

[ramosit]

I would like to create a view using sql command and this may
seem very symply:

create view view1 (a,b) as select a,b from tbl1
or
create view view1 as select a,b from tbl1

and this works but the query does not apper in the list of the user views (the queries)...

how to do to let the query appear in this list???
I thank you all in advance

bye

 

[FoFa]

Access does not do views (in the Oracle or Sql Server sense).
Just create a query.
Views imply a running Db Engine, which Access is not.
Access is basically a flat file DB with an application portion that can run (ok that is an over simplification, but it gives you the picture).

 

[KenHigg]

I think you may need to do the Select/Into sql commands. Do a simple make table query in Access in the query builder and then examine the sql it generates...

(1 Post. Something tells me that he may not be back )

 

[ramosit]

I tried to do this :
select a, b into mynewobj from tbl1 where conditions..

and a new table is created and not a view.

I called before "view" perhaps improperly but the sql command

create view test as select * from tbl1

works and if i execute following command it works

select * from test
The problem is that i know that "test" exists but it is not listed in the queries list or in another place...

 

[KenHigg]

My bad - I mis-read your problem...

 

[Bat17]

if you are trying to create a pemanant query look at CreateQueryDef:-
Set querydef = object.CreateQueryDef (name, sqltext)

For a table, from Help
To create a table that is ready for new records in a database
Use the CreateTableDef method to create a TableDef object.


Set its properties.


For each field in the table, use the CreateField method to create a Field object variable and set its properties.


Use the Append method to add the fields to the Fields collection of the TableDef object.


Use the Append method to add the new TableDef object to the TableDefs collection of the Database object.

 

[KenHigg]

Thanks Bat17 - I new something wasn't clicking. He was trying to create and Access object with a sql statement!

 

[fuzzygeek]

I remember creating something in 1999-2000 that was called a view. It did not appear in the query area because it wasn't a query though it did appear as an object. Even the access help site lists CREATE VIEW, and in the following site listing what is available in Jet 4.0. Having said all that, I can't make it work now. I wonder if I were dreaming.

http://support.microsoft.com/default.aspx?scid=kb;en-us;275561#XSLTH3263121123120121120120