How to create a View in Access 2000 using Jet SQL?

  • Thread starter Thread starter lu_yanfeng
  • Start date Start date
L

lu_yanfeng

Can any body tell me how to create a View in Access using Jet SQL? And where
to get the detailed specific reference for Jet SQL?
 
From your description, it isn't clear where you want the "view" to reside.
If you are building something that lives in an Access .mdb file, what in
SQL-Server would be termed a "view", is called a "query" in Access.

If you are trying to build a view in SQL-Server, from Access, I'm not
familiar with how SQL-Server would allow you to do that. As far as I know,
you have to be logged into and recognized by SQL-Server itself to
create/modify views stored there.

Perhaps one of the other 'group readers can offer a more direct approach...

Good luck

Jeff Boyce
<Access MVP>
 
You have to connect to SQL Server and create a .adp file
in order to have "View", but remember, all .adp use ADO
not DAO, one more thing is you are not allowed to have any
paramater in View, like [Enter year], you have to create
all SQL in Store Procedure if there is any paramater

May
MCP in Access and SQL Server
 
Actually, you can use views.

Remember, any stored query in the ms-access "query" tab can be used in place
of a table name, and, you can do a query on a query. So, the standard
queries in ms-access really for all purposes is a view.

So often, I seen sql people come back and RANT about why did not anyone tell
them that saved quires in ms-access is the same thing as a view? Further,
many people as actually asked why does MS not call them views? The only
reason we don't call that tab "views" is that we can also place any other
type of queries in there (action queries, etc). However, any select query
really can be considered a view.


In addition, JET 4 (a2000 and later) actually supports the ddl commands to
create views.

So, the following is actually legal:

CREATE VIEW ViewName AS SELECT FieldName1, FieldName2 FROM TableName

ACC2000: New Features in Microsoft Jet 4.0
http://support.microsoft.com/default.aspx?scid=kb;en-us;275561
 
Dear Kallal,
Tank you very much for your kind help! I've retried it in VB using ADO,
and successfully created a View using the DDL command. However, the created
View is invisible and can not been seen in the Query tab in Access window.
 
Yes, I may be corrected on this one, but if you don't use the ms-access
interface, then the queries don't show.

I certainly prefer that they "show". Perhaps it depends on how you setup the
ado. If you use the built-in connection, then I bet it shows.

Try using the ms-access interface, and see what happens:

You can use the debug window, or one line of code as follows:

CurrentProject.Connection.Execute "create view myview2 as select id from
tblFruits"

In a2002, the above does show up in the query window. My pc with a2000 on it
is in use right now, so I can't test. So, either it is using currentproject
that makes it show. So, at least when using ms-access, the queries do show.
I would assume there is a flag, or some such means to "show" all the
queries.
 
Back
Top