472,977 Members | 2,084 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,977 software developers and data experts.

Q:Access Parameterized Query to Oracle?


I want to know how to use Oracle views with session variables in
The parameterised views in access, are migrated to views with per

The open questions:
How to display a resultset
How to handle Oracle exceptions

Closed questions:
How to call a stored procedure from DAO.
How to call a stored procedure from ado and then display a view in a
Using a cache to get the same session.

[Sample database setup]

(I have an Oracle backgroud)

The open questions:
How to display a resultset
I am not sure how to display a recordset that is created in VBA.

How to handle Oracle exceptions
Oracle handles errors by 'exceptions',
I am not sure how to handle them in Access.

Closed questions:

What I have been able to do is:

[My session variables have an API, but basically one needs to call a
stored procedure/function to set/get them]

How to call a stored procedure from DAO

Set session variables over DAO/ODBC:
where 'QUERYEXAMPLE:input_name' references a variable and 'smith' is
the value
to set.

call to set string via dao
Function SetVal() As Boolean
Dim db As Database
Set db = DBEngine.Workspaces(0).Databases(0)
Set qitem = db.CreateQueryDef("")
qitem.Connect = "ODBC;DSN=o92;UID=scott;PWD=tiger"
qitem.ReturnsRecords = False
qitem.ODBCTimeout = 15
qitem.SQL = "CALL
omwb_emulation.utilities.setvarchar2('QUERYEXAMPLE :input_name','smith')"
Set qitem = Nothing
SetVal = True
End Function
How to call a stored procedure from ado and then display a view in a
using ado/oledb
************************************************** *********************

How to bind Microsoft Access forms to ADO recordsets:

looks like adodb.recordsets bound to forms are read only.
The following is based on the Microsoft information with nformation
fille in
about using odbc to reach the oracle database with the views. The same
is used to set the parameter and then select from the view to populate

Option Compare Database

Private Sub Form_Open(Cancel As Integer)
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strConnection As String
Dim setstring As ADODB.Command
Dim param1 As ADODB.Parameter
Dim param2 As ADODB.Parameter

strConnection = "ODBC;DSN=o92;UID=northwindalt;PWD=oracle"
'Create a new ADO Connection object
Set cn = New ADODB.Connection

With cn
.Provider = "MSDASQL"
.Properties("Data Source").Value = strConnection
End With

'Create an instance of the ADO Recordset class, and
'set its properties

Set setstring = New ADODB.Command
With setstring
.ActiveConnection = cn
.CommandText = "omwb_emulation.utilities.setvarchar2"
.CommandType = adCmdStoredProc
End With

Set param1 = setstring.CreateParameter(, adVarChar, adParamInput,
100, "QUERYEXAMPLE:input_name")
setstring.Parameters.Append param1

Set param2 = setstring.CreateParameter(, adVarChar, adParamInput,
100, "smith")
setstring.Parameters.Append param2


Set setstring = Nothing

Set rs = New ADODB.Recordset
With rs
Set .ActiveConnection = cn
.Source = "SELECT * FROM queryexample"
.LockType = adLockOptimistic
.CursorType = adOpenKeyset
.CursorLocation = adUseClient
End With

'Set the form's Recordset property to the ADO recordset
Set Me.Recordset = rs
Set rs = Nothing
Set cn = Nothing
End Sub

Private Sub Form_Unload(Cancel As Integer)
'Close the ADO connection we opened
Dim cn As ADODB.Connection
Set cn = Me.Recordset.ActiveConnection
Set cn = Nothing
End Sub

Using a cache to get the same session.
The thing is there is a timeout

The Microsoft Jet database engine uses the ODBC entries as follows.

Initialization Settings for Microsoft Jet-connected ODBC Databases
The \HKEY_LOCAL_MACHINE\Software\Microsoft\Jet\4.0\Eng ines\ODBC folder
contains initialization settings for the Microsoft Jet database engine.

ConnectionTimeout The number of seconds a cached connection can remain
before timing out. The default is 600 (values are of type REG_DWORD).

Sample database setup

table example1.
ID, name, telephone

query queryexample:
Parameters [input name] text;
select telephone from example1 where name=[input name];

becomes in oracle
SELECT telephone
FROM example1
omwb_emulation.utilities.getvarchar2('QUERYEXAMPLE :input_name') FROM

which can be called from Oracle sqlplus:

SQL> begin
omwb_emulation.utilities.setvarchar2('QUERYEXAMPLE :input_name','smith');
3 end;
4 /

SQL> select * from queryexample;


Nov 13 '05 #1
0 2960

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

by: gary b | last post by:
Hello When I use a PreparedStatement (in jdbc) with the following query: SELECT store_groups_id FROM store_groups WHERE store_groups_id IS NOT NULL AND type = ? ORDER BY group_name
by: Jon Ole Hedne | last post by:
My Access 2002-application need to work with tables from both Oracle and Access. To solve this, I want to run some querys on three views in Oracle and import the results into temporary...
by: bhbgroup | last post by:
I have a query on one large table. I only add one condition, i.e. a date (the SQL reads like 'where date > parameterdate'. This query is rather quick if 'parameterdate' is either explicitly...
by: Alfonso Esteban Gonzalez Sencion | last post by:
I am trying to use Access as a front end for extracting information from an Oracle database. I started using linked tables but I am getting a very curious behaviour. When I consult the linked...
by: premmehrotra | last post by:
I am using Microsoft Access 2000 and Oracle on Windows 2000. When I export a table from Access to Oracle using ODBC I get error: ORA 972 identifier too long I think the error is because...
by: Greg Strong | last post by:
Hello All, The short questions are 1 Do you know how to make DSN connection close in Access to Oracle 10g Express Edition? &/or 2 Do you know how to make a DSN-less pass-through query...
by: zwasdl | last post by:
Hi, I'm using MS Access to query against Oracle DB via ODBC. Is it possible to use HINT in Access? Thanks, Wei
by: Tom | last post by:
Looking for some help with stored procedure call issues. Conceptually, I need to pass a data structure as the sole parameter to the Oracle stored procedure. Sounds simple enough....but how? ...
by: jonceramic | last post by:
Hi All, I started developing in Access, and people took notice and so we're starting to migrate into our corporate's bigger Oracle system. I'll still be using my developed Access front ends,...
by: Aliciasmith | last post by:
In an age dominated by smartphones, having a mobile app for your business is no longer an option; it's a necessity. Whether you're a startup or an established enterprise, finding the right mobile app...
by: tracyyun | last post by:
Hello everyone, I have a question and would like some advice on network connectivity. I have one computer connected to my router via WiFi, but I have two other computers that I want to be able to...
by: giovanniandrean | last post by:
The energy model is structured as follows and uses excel sheets to give input data: 1-Utility.py contains all the functions needed to calculate the variables and other minor things (mentions...
by: Teri B | last post by:
Hi, I have created a sub-form Roles. In my course form the user selects the roles assigned to the course. 0ne-to-many. One course many roles. Then I created a report based on the Course form and...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 1 Nov 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM) Please note that the UK and Europe revert to winter time on...
by: nia12 | last post by:
Hi there, I am very new to Access so apologies if any of this is obvious/not clear. I am creating a data collection tool for health care employees to complete. It consists of a number of...
by: NeoPa | last post by:
Introduction For this article I'll be focusing on the Report (clsReport) class. This simply handles making the calling Form invisible until all of the Reports opened by it have been closed, when it...
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
by: GKJR | last post by:
Does anyone have a recommendation to build a standalone application to replace an Access database? I have my bookkeeping software I developed in Access that I would like to make available to other...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.