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

Hello Jim,

That is great, thank you.

The issue in here is adding the ADO reference to 300 instances of
Access 97.  It is currently with management to see if they will allow
it !

Thanks again for your attention, I will let the list know if they
decide to roll out ADO.

Mark



On Fri, 21 Jan 2005 22:00:39 -0800, Jim Lawrence <accessd at shaw.ca> wrote:
> 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
> 
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>