Creating Views in Access 2000

  • Thread starter Thread starter Brian K. Sheperd
  • Start date Start date
B

Brian K. Sheperd

Is it possible to create views in Access 2000? I have seen several places
that mention it, but I keep getting an error. Then I checked the Access
help file, and it mentions it, but it also says that Jet doesn't support it.
If this can be done in Access, how do you go about doing this?

Thanks,
Brian
 
Hi

Use Queries. A query is a logical *view* of one or more tables.


HTH,
Immanuel Sibero
 
Dear Brian:

Microsoft Access 2000 gives you a choice of 2 database engines, Jet or
MSDE. MSDE has specific separate "Views" which, if by View you mean
just what that means in SQL Server, this is just what you want.

If you choose to use the Jet database engine, then the Queries there
will do pretty much the same thing. But you don't have Stored
Procedures or User Defined Functions if that's what you're accustomed
to. You can still perform such things, however, using dynamically
generated SQL or other techniques.

Jet does give you the advantages of being able to use Public Functions
you write in VBA and to reference controls on forms directly.

If you already know SQL Server, then the MSDE route is transparent.
MSDE is a full implementation of SQL Server, but with some size and
performance limitations.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Yes. I was looking for something similar to SQL Server. How do you specify
which engine to use? Does it set it for the application, or is only
database specific? Is this the database upsizing or something different?

Thanks,
Brian
 
Dear Brian:

The Jet database engine is "native" to applications built as MDBs, but
MDBs can "Link" to tables in SQL Server. However, this does not mean
it will use the SQL Server database engine to run the queries.
However, you can also use "pass through" to run the queries that are
in the SQL Server database using the SQL Server engine.

MSDE (a fully compatible version of SQL Server) is the native engine
for ADPs. If you have Jet tables in an MDB somewhere, SQL Server can
link to those.

So, there is a lot of flexibility in using this. We have done some of
these combinations, but really prefer just to avoid Jet altogether and
use ADPs and MSDE.

There is an "upsizing wizard" that can be used to move and MDB to and
ADP, at least to a large extent. I don't use that because it has some
weaknesses, and a review of the database is usually well worth the
time when converting. We hand craft all the queries for SQL Server,
sometimes choosing Stored Procedures or User Defined Functions rather
than just Views. I haven't found this to be too bad, but I've been
doing this for 3 years (I guess I think I'm getting good at it!)

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
HI,


You can create a VIEW and a sproc with Jet 4.0, but they must be (for
the stored proc) made of a single statement, like for a parameter query,
really no advantage, unless that a stored proc can be use as if it was a
function, instead of having to pass by a command object... which can be
useful in some cases. You have to do it using ADO (not DAO, not the query
designer):


CurrentProject.Connection.Execute "CREATE VIEW viewName AS SELECT
.... "

CurrentProject.Connection.Execute "CREATE PROCEDURE alpha(beta) AS
SELECT ... WHERE fieldName = beta "

... = CurrentProject.Connection.Execute( "EXEC alpha 444" )

which is, in many aspect, much easier than using an ADO command object: you
can build the string at run time and beneficiate of a pre-compiled query at
the same time.

The stored proc can be an action query, not necessary a data retreival
query, as shown (like an INSERT INTO query, often use in introduction book
as use of stored procedure... but I won't comment that kind of use of a
stored proc).


A feature in Access 2000 hide the VIEW you would have created this way from
the database main window. You see then in Access 2002 and later.


Hoping it may help,
Vanderghast, Access MVP
 
Back
Top