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
INSTEAD OF UPDATE ON MyView as
begin
update MyUnderlyingTable ut
set ut.Field = new.ViewField
where ut.keyfield new.keyfield ;
end;

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
fin:
Set dft = Nothing
Set bds = Nothing
End Sub

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

• strNomTable is the name of the Access linked table object.
• strNomtablesource is the Oracle view 's name, it may include a schema prefix.
• strODBC is the ODBC connection string matching you oracle database.
  it should be like the following : "ODBC;DSN=DSN_name;DBQ=oracle_schema_name;PWD=Password;DBA=W;APA=T;EXC=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;GDE=F;FRL=F;BAM=IfAllSuccessful;MTS=F;MDI=F;CSR=F;FWC=F;PFC=10;TLO=0;DATABASE=" notice that here you have to provide a password, the one matching the user name provided in the DSN.
• strKey is the coma separated list of fields defining the unique identifier for the view.
  

That's all folks! Comments are welcome!

  ¶ 7:58 AM