Creating a VIEW OR Updating a VIEW

  • Thread starter Thread starter jmarr02s
  • Start date Start date
J

jmarr02s

Is Creating a VIEW OR Updating a VIEW possible using SQL code in MS
Access?

Here is the code for Creating a VIEW that I tried (unsuccessfully) to
use with MS Access:

CREATE VIEW sup_orders AS
SELECT suppliers.supplier_id, orders.quantity, orders.price
FROM suppliers, orders
WHERE suppliers.supplier_id = orders.supplier_id
and suppliers.supplier_name = 'IBM';

Here is the code for Updating a VIEW that I tried (unsuccessfully) to
use with MS Access:

CREATE or REPLACE VIEW sup_orders AS
SELECT suppliers.supplier_id, orders.quantity, orders.price
FROM suppliers, orders
WHERE suppliers.supplier_id = orders.supplier_id
and suppliers.supplier_name = 'Microsoft';

Any knowledge you can pass my way would be appreciated!

Thanks!

jm
 
Yes. You can create a view like this:
http://allenbrowne.com/func-DDL.html#CreateViewDDL

I don't recognise REPLACE VIEW for JET SQL.

Within Access, views and procedures appear as queries. The QueryDef has a
SQL property you can assign a new SQL statement to, e.g.:
CurrentDb.QueryDefs("Query1").SQL = "SELECT ...
Creating a query using DAO:
http://allenbrowne.com/func-DAO.html#CreateQueryDAO

If you prefer to work with them as views and procedures:
http://allenbrowne.com/func-ADOX.html#CreateViewAdox
 
CREATE, DROP & ALTER are not supported by Jet db engine. What are you
trying to accomplish? There may be a workaround someone can suggest.

HTH
Damon
 
My bad. Knowing a little can be a dangerous thing. I was quoting Access
help:
Note The Microsoft Jet database engine does not support the use of CREATE
VIEW, or any of the DDL statements, with non-Microsoft Jet database engine
databases.

Left out the last line.

Damon
 
Back
Top