.comment-link {margin-left:.6em;}
Marc Boizeau's blog
Thursday, October 28, 2004
  Using updatable views in Access
These "updatable views" feature in Oracle is interesting. You can specify triggers on a view to make it behaves like a table. Its interesting when different processes or applications use the same data but not the same way.
How to make this working with Microsoft Access?

First define your view with a statement like the following:
create view MyView as select .......

then add your trigger(s) :
create or replace trigger MyUpdateTrigger

update MyUnderlyingTable ut
set ut.Field = new.ViewField
where ut.keyfield new.keyfield ;

notice the "instead of " statement . You may also add "delete" or "insert" triggers the same way.

Now, how do you make Access 2003, your favorite client side application , works with this?
Open your Access database and link the view as you would for a table.
To do this:

- In the file menu choose "external data" and "link table" . Then choose or create the Data Source Name which map your Oracle Database.

- You will then be prompt to log with your Oracle Profile. You need to be familiar with ODBC and DSN (that is an idea for a new post).

-Then Access display a list of all views and tables available. Depending of your profile you may see system tables and views. Choose "MyView".

- You are then prompt to define the unique identifier of this "table". Choose it regarding the trigger's code you've implement. It should be the logical equivalent of a primary key.

Now your view is available in Access. And it works!

No it dosen't. In fact it works but it is not a practical way to build an application and test it.

If you make a change to your view's definition the linked table in Access is no more available.
So debugging a complex application with this is like a turtle race: you need to pe patient ;-).

Here is a sample VB Access code which may help you :

Sub ConnectTable(strNomTable, strNomtablesource, strODBC, strKEy)
Dim bds As Database, dft As TableDef
Dim strSql
Dim oFi As Field

Set bds = CurrentDb

'delete the existing table
If TableExists(strNomTable) Then
CurrentDb.TableDefs.Delete (strNomTable)
End If

Set dft = bds.CreateTableDef(strNomTable)

dft.Connect = strODBC
dft.SourceTableName = strNomtablesource

bds.TableDefs.Append dft
If Strings.Trim(strKEy) <> "" Then
strSql = "CREATE INDEX " & strNomTable & "_PK" & " ON " & strNomTable & "(" & strKEy & ") WITH PRIMARY"
bds.Execute strSql
End If

Set dft = Nothing
Set bds = Nothing
End Sub

Put this in a module and call it with the following parameters:

That's all folks! Comments are welcome!

Comments: Post a Comment

Links to this post:

Create a Link

<< Home
You are a developer and work with Oracle and Microsoft technologies? Have a look!
How to:
Use updatable views in Access
Get data in Excel from Oracle 1
Get data in Excel from Oracle 2
Draw the Mandelbrot set using C#
Use the "Grouping Sets" SQl Syntax
Use the "Rollup" SQl Syntax
Use the "Rank over" SQl Syntax

October 2004 / November 2004 / December 2004 / January 2005 / February 2005 / March 2005 / April 2005 / June 2005 / July 2005 / August 2005 / September 2005 / December 2005 / February 2006 / December 2006 / March 2009 /

Powered by Blogger