We work with Oracle and MS Access and are wanting to bind or fill a form with a recordset from Oracle, displaying a list of records. We want to use the MS Access Form with Oracle OO4O.

What is your question? What part of this process are you having problems with? Creating a recordset in Oracle? Binding a recordset to a form? Creating a form? Etc?


mmcdonal

Look it up at: http://wrox.books24x7.com

We can not get the Oracle data retrieved via OO4O to go into the MS Access continuous form. We have tried Dynasets and RefCursors. Me.recordset or set me.recordset = ds does not work.
Mike

I am not sure how you are trying to retrieve the data. Normally I would use this sort of connection set up:

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sSQL As String

Set cn = New ADODB.Connection
With cn
    .CursorLocation = adUseClient
End With
cn.Open "Provider=msdaora;Data Source=Database.Domain;User ID=UserID;Password=Password;"

sSQL = "SELECT * FROM database.tablename"

Set rs = New ADODB.Recordset
rs.Open sSQL, cn, adOpenDynamic, adLockOptimistic

This will open an editable recordset.

Have you gotten this far?



mmcdonal

Look it up at: http://wrox.books24x7.com

I have the same issue as this user, I believe. In my case, I don't want to use ADO to connect to ORACLE, as there are issues with drivers and such that make using ADO/Access/ORACLE a maintenance nightmare. For example, each version's driver has a different name...

Also, I can force the construction of a parameterized or dynamic SQL statement back to the server with OO4O, without all of the gruesome concatenations required in Access to build the parameterized SQL statement for a pass-through query.

As it stands, the result set returned by OO4O is not compatible with an ADO recordset. They are different objects. So, the data has to be moved from one to the other.

In MS Access, we have created a temporary table and looped the OO4O result set, inserting each row into the temp table. We then set the form's recordsource to that table. When the form closes, we delete the table and when the Access app closes, we compact. My team lead does NOT like this approach, so that is why I am still researching.

We also tried defining a disconnected ADO recordset and inserted the OO4O result set rows into it. Problem there was that while the rows displayed correctly in Access, Access abends when a user right clicks a column in a continuous form and selects a filter or sort option.

So, the question remains, is there a way to easily move the data in an OO4O result set to an form's recordset without using a temporary table?

Thanks,

Wayne