.comment-link {margin-left:.6em;}
Marc Boizeau's blog
Friday, October 29, 2004
  From the datawarehouse to the chart (part 1)
After Access lets use Excel as a client for Oracle.

I work for a real estate company where assets managers always want to make charts or calculus based on data stored in the Oracle dataWarehouse. Most od the time they want to do this with Excel because well known this tool. Sometimes they want to use Excel just because its easy to format data and then to put it in a Powerpoint document.

The problem is that we do not want to use VB macro in those Excel files . Those files may be sent by e-mail to clients. Any good firewall will destroy such files.

One solution is to generate files on a server. For instance you install Oracle's client and office on a Windows 2003/IIS5 server and implement complex ASP/ASPX pages which generate Excel files .
You may also use a Excel intepreted ASP page. Both of this solutions are hard to set up and need a lot of code.

There is another solution: lets use MS Query!

First of all your users need:
- Oracle connectivity install and MS Excel on there computer.
- A Data Source Name configure on there computer .
- A login and password which they can use to access the database.

I assume you work with Windows NT/XP and got an Oracle Server.
Here is how to get data from Oracle:
- create a new Excel document.
- in the "data" menu choose "External data" then "new query"
-Choose the appropriate Data Source Name
- Logon
- A wizard will guide you to make your select statement.
- choose return to Excel and validate.

surprise you've got your datas into Excel !

That's all folks! Any comments are welcome!

Next time I will try to expose how to fine tune this feature.

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!

I'm a french developper. I spend spend most of my time at work searching solutions to problems like :
" How can we get the data( in Oracle database) into the document (Microsoft Office file)"
".Net studio is cool use it! You won't use SQL Server as as Database use Oracle for your applications"

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

Go to knol
When does the next bus pass?
two lines in the datagrid header
Context saving with persistent datasets
.net webservice session
Winform, Web Services & credential
back to work
just a link
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