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!

 

[Pat Hartman]

Ramost, be careful of your SQL syntax. Access uses the newer style join syntax. The syntax in your example will create a cartesian product before applying the criteria and winnowing it down. If you are unfamiliar with Jet SQL syntax, the simplest solution is to use the QBE to build your queries - especially those with joins. Then you can copy the SQL if you want to.

Select a.*, b.* from a inner join b on a.key = b.key
Where a.somefield = "somevalue";

 

[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

 

[Pat Hartman]

You can create views with pass-through queries from Access or I believe directly from an .adp. Be careful though. Views are not necessarily updatable.