[AccessD] Calling stored procedures from Access 97 to Oracle 8i

Hi Mark:

Here is one example from Microsoft that will give you all the information
you need to connect to Oracle, using ADO and passing parameters. Remember to
have your Oracle TSNames file, on each station, setup first.

1.

Sample code for connecting, passing parameters and an Oracle sample
procedure that would accept the passed parameters and then return data.
 
http://support.microsoft.com/kb/176936/en-us?ln=en-us&sd=gn&fr=0

2.

Remember to add a reference to the MDAC ADO libraries in your Access.
Reference entry will read something like: 'Microsoft ActiveX Data Objects
X.X Library'.

3.

Do not use the ODBC connection like MS are recommending use direct ADO-OLE,
with Microsoft's latest MDAC Oracle driver like:

    Dim mobjConn As ADODB.Connection
    Dim gstrConnection As String

    ' Microsoft connection string...standard security
    gstrConnection = "Provider=msdaora;" & _
                        "Data Source=MyOracle.website.com;" & _
                        "User Id=" & myUsername & ";" & _
                        "Password=" & myPassword
        
'   Test connection string
    Set mobjConn = New ADODB.Connection
    mobjConn.ConnectionString = gstrConnection
    mobjConn.Open


4.

Contents of a TSNames.ora file related to the local station Oracle client.
This is just a sample.

'------------ Oracle connectuions --------------------------------------
            
'from the tnsnames.ora
'
'MyOracle.website.com =
'   (DESCRIPTION =
'       (ADDRESS_LIST =
'           (ADDRESS =
'               (COMMUNITY = 'MyOracle.website.com)
'               (PROTOCOL = TCP)
'               (Host = Hostsubnet.MyOracle.website.com)
'               (Port = 1521)
'           )
'       )
'       (CONNECT_DATA =
'           (SID = PCAG)
'               (GLOBAL_NAME = MyOracle.website.com)
'           )
'       )


5.
You can download a free Oracle client for your own use at:
http://www.oracle.com/technology/software/products/oracle9i/index.html


HTH
Jim

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Mark Breen
Sent: Friday, January 21, 2005 12:19 AM
To: Access Developers discussion and problem solving
Subject: [AccessD] Calling stored procedures from Access 97 to Oracle 8i

Hello All,

This is more a piece of information than a question.

As you may have seen, I have recently starting work on a project
involving Access 97 and Oracle 8i.  Some of you may also have noticed
that I asked a question about ADO.  To be honest, I wondered why my
colleague wanted to use ADO, why not use DAO I thought.  Of course his
answer was that he wanted to use output parameters in ADO.  I have
never really used output parameters ( in the MS SQL Server
environment), if I want another value, I usually just stick it on the
end of the columns that I am selecting.  Not perfect I know, but it is
fine unless I am returning millions of records.

So, last night I started to write my first Oracle procedure, it was a
simple task I wished to do, select one field from a table and group by
that field.

It turns out that Oracle cannot select (and return) records from
within stored procedures !!!  Sure you can open a cursor and loop
through the data and return them as out parameters and then, when you
get back you command object you can assign that to a recordset.

So two points to make from this
1) sproc's in Oracle cannot just select records
2) DAO cannot deal with output parameters

It comes as no surprise that DAO cannot deal with output params but I
am gobsmacked that Oracle 8i cannot just select a few bloody records
in a sproc and just return them as a recordset.

One option is to insert my work in to temp tables and then select from
them, but that is not really an option with such a big front end as
this.

So, it seems that Oracle likes to return cursors !

I think that I have a mindset change coming on.

Mark
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com